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?

[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/...unction-table/

[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"

[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