Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query across several fields

    I am using Access 2007. Within my database I have a lookup dataset of most of the legislative committees linked to the legislators' file. I have three distinct committee fields per each legislator. The Agricultural committee for Senator Streetsmart may be in field committee1, whereas the same committee for Senator Gotcha might be in the committee 2 or 3 field. How can a query be written to search across the three committee fields for "Agricultural?" Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    G.F.,

    To do this in Access you need to create a line in the query grid for each of the committee fields.
    Each line would have Streetsmart in the Senator Name Filed.
    Each line would contain Agricultural in the appropriate committee field, e.g.,
    Line one would have Agricultural in Committee1 field
    Line two would have Agricultural in Committee2 field
    Line three would have Agricultural in Committee3 field.
    AccessOrQry.JPG
    This is how you construct an OR query in Access. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    This is a perfect illustration of what happens when you have a denormalized database! You have to jump through hoops to get the information you need. The committees should not be fields in the the legislator's record, rather you should have a separate child table under the legislator table that has 1 record for each committee a legislator in on.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query across several fields

    Sorry for not being as clear as I should have Mr. Geek.
    First of all thank you for your prompt reply.
    My search would be for "Agricultural" across the three committee fields producing the committee members for that particular committee.
    Thanks,

    Quote Originally Posted by RetiredGeek View Post
    G.F.,

    To do this in Access you need to create a line in the query grid for each of the committee fields.
    Each line would have Streetsmart in the Senator Name Filed.
    Each line would contain Agricultural in the appropriate committee field, e.g.,
    Line one would have Agricultural in Committee1 field
    Line two would have Agricultural in Committee2 field
    Line three would have Agricultural in Committee3 field.
    AccessOrQry.JPG
    This is how you construct an OR query in Access. HTH

  5. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query across several fields

    Got it Mr. Geek,
    as your screen shot provided, stair cased the committee record number, one line for each committee field, w/o the legislator's name. Knew Access could handle it but needed someone like yourself to show me how. Thanks.

    Quote Originally Posted by RetiredGeek View Post
    G.F.,

    To do this in Access you need to create a line in the query grid for each of the committee fields.
    Each line would have Streetsmart in the Senator Name Filed.
    Each line would contain Agricultural in the appropriate committee field, e.g.,
    Line one would have Agricultural in Committee1 field
    Line two would have Agricultural in Committee2 field
    Line three would have Agricultural in Committee3 field.
    AccessOrQry.JPG
    This is how you construct an OR query in Access. HTH

  6. #6
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Not sure how to go about that Mark but something I should look into. I bookmarked your tips and downloads site - will have to look it over later on to see how to do as you are suggesting. Thanks.


    Quote Originally Posted by MarkLiquorman View Post
    This is a perfect illustration of what happens when you have a denormalized database! You have to jump through hoops to get the information you need. The committees should not be fields in the the legislator's record, rather you should have a separate child table under the legislator table that has 1 record for each committee a legislator in on.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    G.F.,

    Here's a brief summary of what Mark was talking about.

    It's fairly easy to normalize your tables.

    First Table say Senators or maybe Legislators.
    Fields
    ID {Key Field Generated or maybe SSN}
    LastName
    FirstName
    DOB
    Senatorial District No
    ...Any thing else that has a ONE to ONE relationship to the senator.

    Second Table: Committees
    Fields:
    RecNo {Key Field Generated}
    Committee Name
    ID (From Senators table ... this is a Foreign Key)

    Now when a senator is assigned to a committee you just create an entry in the Committee's table with his/her ID and the Committee Name...wash, rinse, repeat.

    When you create a query to get members of a committee you just create a join between the Senators Table ID and the Committee's Table ID fields and you can easily create your query by placing the Desired Committee name in the Committee Name field of the grid and drag the name fields into the grid along with any other pertinent information.

    Query:
    NormalizedQry.JPG

    Results:
    NormalizedQryResults.JPG

    Test DB File: TestDBGF.zip

    HTH
    Last edited by RetiredGeek; 2015-04-15 at 15:54.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mr. Geek,
    Boy, you make it sound so easy and efficient. Most importantly, I appreciate your advice and time including the graphics.
    Within that same database I have records for non-legislative people as well; therefore the committee fields for them would be empty.
    First off, is it easy and safe enough for this goat farmer to use the existing database to normalize without re-entering the data?
    I have concerns but having difficulty in putting them to text. Guess I will have to first make a back up (or two) and go ahead and play.

    Quote Originally Posted by RetiredGeek View Post
    G.F.,

    Here's a brief summary of what Mark was talking about.

    It's fairly easy to normalize you tables.

    First Table say Senators or maybe Legislators.
    Fields
    ID {Key Field Generated or maybe SSN}
    LastName
    FirstName
    DOB
    Senatorial District No
    ...Any thing else that has a ONE to ONE relationship to the senator.

    Second Table: Committees
    Fields:
    RecNo {Key Field Generated}
    Committee Name
    ID (From Senators table ... this is a Foreign Key)

    Now when a senator is assigned to a committee you just create an entry in the Committee's table with his/her ID and the Committee Name...wash, rinse, repeat.

    When you create a query to get members of a committee you just create a join between the Senators Table ID and the Committee's Table ID fields and you can easily create your query by placing the Desired Committee name in the Committee Name field of the grid and drag the name fields into the grid along with any other pertinent information.

    Query:
    NormalizedQry.JPG

    Results:
    NormalizedQryResults.JPG

    Test DB File: TestDBGF.zip

    HTH

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    G.F.,

    Yes do make a backup and save it to some external media so you don't accidentally open it when you're messing around!

    Here's roughly what I'd do:
    1. Open the database.
    2. Open the Legislators (or what ever you call your table) in DESIGN mode.
    3. Insert a row at the top of the list. Name the field ID or SSN or EmplNo and set it to Auto number (don't worry if you are using an existing number like SSN we will fix that later)
    4. Set the new field to Primary Key
    5. Save the table definition (Ctrl+S) or just hit the View button and you will be prompted to save.
    6. If you are using a preexisting number you now need to go into the table and enter the correct number for each person listed. If not go to the next step.
    7. Create a make table query using the ID field and Committee1 (however you need to rename the Committee1 field to Committee (Committee: Committee1 in the design grid, you'll repeat this syntax below) to the Committee Table and run it.
    8. An append query like the previous one for Committee2 then for Committee3
    9. Open the Committees Table and add a row at the top call it RecNo and make it AutoNumber and save.
    10. Open the Legislators table in design mode and delete the committee rows.
    11. I believe we're done.


    I've tested this in pieces so it "SHOULD" work.
    All you Access Gurus can chime in with steps I missed or easier ways to do it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query across several fields

    I wanted to personally thank you Mr. Geek for your time and advice. Having an assortment of categories of people within the same one table I think, at least temporarily, while the legislative session is still fig'ring out how to take more of my money, I must wait to normalize that same table or begin a new database for the legislature. But I did want to thank you very much. I have saved your posts so when I am ready to improve my database I will immediately be able to re-read them.
    Have a great day, sir.

    Quote Originally Posted by RetiredGeek View Post
    G.F.,

    Yes do make a backup and save it to some external media so you don't accidentally open it when you're messing around!

    Here's roughly what I'd do:
    1. Open the database.
    2. Open the Legislators (or what ever you call your table) in DESIGN mode.
    3. Insert a row at the top of the list. Name the field ID or SSN or EmplNo and set it to Auto number (don't worry if you are using an existing number like SSN we will fix that later)
    4. Set the new field to Primary Key
    5. Save the table definition (Ctrl+S) or just hit the View button and you will be prompted to save.
    6. If you are using a preexisting number you now need to go into the table and enter the correct number for each person listed. If not go to the next step.
    7. Create a make table query using the ID field and Committee1 (however you need to rename the Committee1 field to Committee (Committee: Committee1 in the design grid, you'll repeat this syntax below) to the Committee Table and run it.
    8. An append query like the previous one for Committee2 then for Committee3
    9. Open the Committees Table and add a row at the top call it RecNo and make it AutoNumber and save.
    10. Open the Legislators table in design mode and delete the committee rows.
    11. I believe we're done.


    I've tested this in pieces so it "SHOULD" work.
    All you Access Gurus can chime in with steps I missed or easier ways to do it.

    HTH

Tags for this Thread

Posting Permissions

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