Results 1 to 10 of 10
  1. #1
    Little is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5

    Query entire table at once


    I have a table with each record representing a team. The teams can be made up of several individuals so an individual may show up in record three and record ten. Their position on the team also rotates so they may be in position A in record three and position C in record ten. I need to be able to create a query that will allow the user to enter a name in a form and have the query search each record and positon and retrieve any record where the individuals name appears. I made a query and listed all the fields in the table with a like criteria in each; however, I never got a match. I am assuming the query was looking for a record where the entered name was presented in every field of a record. I really hope I explained that correctly. Thanks for your help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What usually happens in Access is that you will have several tables. For example, you might have a table that contains a list of teams, one that lists players, another that lists positions that are available.

    How these various tables relate to one another is the basis of a relational database. This is where queries start to demonstrate their true power.

    If you want to display a specific player and a specific position, you might try creating a query based on your table and then adding criteria to the criteria field(s) within the design grid located towards the bottom of the designer window.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What does your table look like? - I suspect you may have unnormalised data and your table looks something like

    team..positionA...postionB..etc
    1.......George......Harry
    2.......Fred..........Charlie
    1.......Sid...........George

    Whereas it should look like

    team..Position..Person
    1.......A...........George
    1.......B...........Harry
    2.......A...........Fred
    2.......B...........Charlie
    1.......A...........Sid
    1.......B...........George

  4. #4
    Little is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    Quote Originally Posted by Ajax View Post
    What does your table look like? - I suspect you may have unnormalised data and your table looks something like

    team..positionA...postionB..etc
    1.......George......Harry
    2.......Fred..........Charlie
    1.......Sid...........George

    Whereas it should look like

    team..Position..Person
    1.......A...........George
    1.......B...........Harry
    2.......A...........Fred
    2.......B...........Charlie
    1.......A...........Sid
    1.......B...........George
    Hi! My Table is set up like the first one you described.

    TAV ID TEAM ID Team Chief Team Lead ........
    23 1 Belcher Barney ........
    24 2 Barney Long ........
    25 3 Long Salpas .........

    The TAV ID is used to link the teams to the events in another table. What my boss would like is to do a search of a Team Members name and pull up any event they have been a part of. So if I enter Barney in the associated form, I would like the query to give me Team ID 1 and Team ID 2 results.

  5. #5
    Little is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    I have tried to use the criteria and I feel like that is where I am messing up.

    TAV ID TEAM ID Team Chief Team Lead ........
    23 1 Belcher Barney ........
    24 2 Barney Long ........
    25 3 Long Salpas .........

    What I did was create a query with fields Team Lead, Team Chief, and so on. In the fields criteria I put Like "*"&[Forms]![frm_TeamMembers]![Member_Selection]&"*". So when Barney's name is entered in the form, I would hoping that the query would bring back Team 1 and Team 2. No results would show and I thought it was because the query was looking for a record that showed true for barney in both fields. So then I put Like "*"&[Forms]![frm_TeamMembers]![Member_Selection]&"*" in the first field's criteria row and in the OR row of the second field. The query worked then, but failed as I added the rest of the positions. I'm sure its something simple I am missing. I think a lot of OR statements would be too much???

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to change your table design to the second one I provided. Access is not a bigger version of excel

    The problem with your way is that it is not normalised - so if you need to add or remove a position or rename it, not only do you need to change the table but also all resultant queries, forms and reports.

    google 'normalisation' to find out more but in essence it means a) store data only once, b) don't store calculated values and c) don't build data into the structure

    your table is breaking rule c by having different positions as field names.

    If you did it the normalised way the query will simply be

    SELECT *
    FROM myTable
    WHERE Person='barney'

    your way you have to test every position column

    SELECT *
    FROM myTable
    WHERE TeamChief='barney' OR TeamLeader='barney'....

    And if this is a big table you will need to index each field - whereas the normalised way you only need to index one field. It will be comparatively slow anyway because it will be making the same comparison for each position per record, rather than once

    Similarly if you need to link this table to some other table based on the person, you will need an aliased table for each field all using left joins making for a very slow, cumbersome and difficult to maintain query

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You can have many OR operators in your Query Object (the query you named and saved). The trick to causing your query to be dependent on controls within a form is to make sure the form is open before using the query.

    Another thing you want to make sure of is that each control that is part of your query passes an actual value to your query and the value passed is the correct data type.

    Also, do not miss the point we are trying to communicate to you regarding normalization of data, as it pertains to the structure of your tables.

  8. #8
    Little is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    Thank you. I was able to make the OR statement work. I had to but them in different rows for each field, but I am going to try what was suggested upon.

  9. #9
    Little is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    I am going to check out normalization because I want to do this the right way. I will also work on reconstructing the database in the way you suggested so that it will be more efficient as well as correct. Thank you very much for your assistance. I greatly appreciate it.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    This seems to be a comprehensive list of resources.
    https://www.accessforums.net/showthr...708#post329708

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update and Append Entire table in Access
    By Yoyo120 in forum Access
    Replies: 1
    Last Post: 06-05-2014, 02:42 PM
  2. Insert entire row from query into new row in another table
    By chris.williams in forum Programming
    Replies: 3
    Last Post: 10-13-2011, 01:38 PM
  3. Replies: 3
    Last Post: 09-18-2011, 03:46 PM
  4. Replies: 1
    Last Post: 07-21-2010, 07:27 AM
  5. How to loop code to run through entire table
    By kmajors in forum Reports
    Replies: 9
    Last Post: 04-23-2010, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums