SQL help

[CHR1S]

In an access database I need to run a SELECT but need some pointers –

 

It needs a IF *building = *Main or *New or *Six

 

THEN list *roomnames that are relevant to *building (RoomName is Identified by M01 (Main) N01 (New) and S01 (Six))

 

 

I have created the building table and the room table but fail at the hard stuff! I assume you can narrow it down by the prefix?

 

It needs to remove any rooms not in the building chosen in the previous dropdown field

 

Can you help?

Edited June 22, 2012 by CHR1S

[CHR1S]

Right, Ive narrowed it down to this -

 

"SELECT * FROM room WHERE buildingid =" & cbobuildingselect

 

I seem to be unable to get the output from the combobox into the SQL argument.

 

can you see where im failing?

[jinnantonnixx]

I'm not too clued up on Access.... but.....

 

I often use junction tables to do similar things to this. It might seem a horrible technique, but it's quite a logically efficient way of storing an indeterminate number of associated items (many to many).

 

A junction table might look like this:

 

Building | Room

Main | A12

Main | B24

New | C12

Main | A8

New | B2

 

You can make a view selecting all the rooms where the Building = Main, etc.

 

Bind your drop-down value to the result of the query against the junction table. e.g. select room from room_table where building = 'main' would give you a set of rooms for the 'main' building. Bind these to your drop-down.

 

http://megocode3.wordpress.com/2008/01/04/understanding-a-sql-junction-table/

Edited June 25, 2012 by jinnantonnixx

[jinnantonnixx]

As it happens, here's a snippet from an application I wrote a while ago.

"perms" is the junction table

It also binds a drop-down to a data source.

 

 

I should add that I used the now deprecated LINQ to do this, so the code might look a little odd (LINQ looks like backwards SQL).

 

 

  .......
     Else
           'the user should see just their set of sites
           'derive the allowed sites by looking up the site-codes/site-names for those allowed
           'by the user in the junction table
           Dim mySites = From allsites In context.lookups
                         Join perms In context.Permissions
                         On perms.SIMSDatabaseCode Equals allsites.AdminSiteCode
                         Where (perms.userID = userID And allsites.SIMSFileServerPath.ToString.Length > 0)
                        Select Code = allsites.AdminSiteCode.ToUpper.Trim,
                               Full = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(allsites.SiteFullName.Trim) 'use Title Case to properly capitalise the words
           'bind the drop-down list to the result (2D array)
           SIMSDropDown.DataSource = mySites.ToArray()
       End If

'define the display and value members for the combo box

       SIMSDropDown.DisplayMember = "Full"

       SIMSDropDown.ValueMember = "Code"

Edited June 25, 2012 by jinnantonnixx

[CHR1S]

Thanks all, I managed to get this working in the end! I think there was an issue with an enquiry set up for the combobox that was failing and not the SQL