Coding Thread, SQL help in Coding and Web Development; In an access database I need to run a SELECT but need some pointers –
It needs a IF *building ...
22nd June 2012, 04:25 PM #1
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?
Last edited by CHR1S; 22nd June 2012 at 04:30 PM.
IDG Tech News
25th June 2012, 01:03 PM #2
Right, Ive narrowed it down to this -
I seem to be unable to get the output from the combobox into the SQL argument.
"SELECT * FROM room WHERE buildingid =" & cbobuildingselect
can you see where im failing?
25th June 2012, 01:50 PM #3
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.
Last edited by jinnantonnixx; 25th June 2012 at 02:20 PM.
25th June 2012, 01:56 PM #4
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).
'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()
'define the display and value members for the combo box
SIMSDropDown.DisplayMember = "Full"
SIMSDropDown.ValueMember = "Code"
Last edited by jinnantonnixx; 25th June 2012 at 02:04 PM.
26th June 2012, 10:59 AM #5
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
By FragglePete in forum Coding
Last Post: 5th July 2011, 09:13 AM
Last Post: 15th February 2011, 08:19 PM
By kennysarmy in forum Windows
Last Post: 3rd November 2010, 11:52 AM
By LosOjos in forum Coding
Last Post: 29th March 2010, 09:27 AM
By Mr_M_Cox in forum MIS Systems
Last Post: 16th August 2007, 03:24 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)