+ 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,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195

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

  2. #2

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    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
    1,826
    Thank Post
    104
    Thanked 449 Times in 308 Posts
    Blog Entries
    2
    Rep Power
    262
    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 02:20 PM.

  4. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,826
    Thank Post
    104
    Thanked 449 Times in 308 Posts
    Blog Entries
    2
    Rep Power
    262
    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 02:04 PM.

  5. #5

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    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, 09:13 AM
  2. AutoIT and SQL help
    By MK-2 in forum Coding
    Replies: 5
    Last Post: 15th February 2011, 08:19 PM
  3. Replies: 5
    Last Post: 3rd November 2010, 11:52 AM
  4. SQL - Help please
    By LosOjos in forum Coding
    Replies: 6
    Last Post: 29th March 2010, 09:27 AM
  5. SQL Database Migration-HELP!!
    By Mr_M_Cox in forum MIS Systems
    Replies: 3
    Last Post: 16th August 2007, 03: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
  •