+ Post New Thread
Results 1 to 5 of 5
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 ...
  1. #1

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,573
    Thank Post
    1,652
    Thanked 506 Times in 309 Posts
    Rep Power
    221

    SQL help

    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 05:30 PM.

  2. #2

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,573
    Thank Post
    1,652
    Thanked 506 Times in 309 Posts
    Rep Power
    221
    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?

  3. #3

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,086
    Thank Post
    123
    Thanked 534 Times in 357 Posts
    Blog Entries
    2
    Rep Power
    336
    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/
    Last edited by jinnantonnixx; 25th June 2012 at 03:20 PM.

  4. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,086
    Thank Post
    123
    Thanked 534 Times in 357 Posts
    Blog Entries
    2
    Rep Power
    336
    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).


    Code:
      .......
          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"
    Last edited by jinnantonnixx; 25th June 2012 at 03:04 PM.

  5. #5

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,573
    Thank Post
    1,652
    Thanked 506 Times in 309 Posts
    Rep Power
    221
    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



SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 3
    Last Post: 5th July 2011, 10:13 AM
  2. AutoIT and SQL help
    By MK-2 in forum Coding
    Replies: 5
    Last Post: 15th February 2011, 09:19 PM
  3. Replies: 5
    Last Post: 3rd November 2010, 12:52 PM
  4. SQL - Help please
    By LosOjos in forum Coding
    Replies: 6
    Last Post: 29th March 2010, 10:27 AM
  5. SQL Database Migration-HELP!!
    By Mr_M_Cox in forum MIS Systems
    Replies: 3
    Last Post: 16th August 2007, 04:24 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •