Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Buidling a complicated query

    I am working a form to email and I have come across a conundrum. The table that is the record source for this form has a lot of data that can go into this form, but all of the data
    there is insufficient for my form. I need at least another table.

    The only way to do that I think is a query that pulls two tables together and can be used for a record source for this form; not just the one table that I had originally used.



    However, you can't just start adding tables to a query willy-nilly, or higgedly-piggedly. No sir. The these two table are in the same db, but what else must they have in common that would allow
    them to be used in a query?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou reed

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Generally speaking, a field or fields to link them on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you already have 2 tables that contain data that you need to use? If so, those tables should be related by a key field. Usually the main table has the Main key or Primary Key(PK) and the 2nd or related table has the Foreign Key(FK) which is the value that links to the PK from the main table. In your query, you add both tables, then link the 2 tables by those 2 fields(click on one field and drag line to the other field).

    Table1: Table1ID(PK), Table1Date
    Table2: Table2ID, Table1ID(FK)

    In your query you link both tables by the Table1ID fields.

    Maybe post the fields in both tables so we can see what you have.

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    That seems like it would work. I am sorry for the brain-locked. I should have got this one myself.

    Respectfully.

    Lou Reed

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happens to us all.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, you mention tables with common links. I am aware of that.

    I have a Personnel Table that I would call the main table, it is in the Center of the diagram. The Meeting Table that gives the date of meetings and maybe some other info such as meetingID also existsnthis meeting table is not directly linked to the Personnel Table. There is an Attendance Table between them.

    The Attendance Table connects, both the Meeting Table and the Personnel Table.

    My guess is since the Attendance Table lies between them, is to collapse the Meeting Table into the Attendance Table (obviously making it bigger) a two into one maneuver; now the two tables with all of the relevant info exist, and are directly linked and this makes a query design possible or at least much easier.

    Does this sound like a good idea? As I said it makes things easier.

    I am just not sure if it is necessary.

    Any help appreciated, Thanks in advance.

    Respectfully,


    Lou Reed
    Last edited by Lou_Reed; 03-17-2017 at 07:05 AM. Reason: correction

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Screenshot

    Here is a screenshot of the relationships in the db.

    I hope it make my point easier to understand.

    Respectfully,

    Lou Reed
    Attached Thumbnails Attached Thumbnails 2017-03-17_9-12-19.png  

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You would combine them in a query. tblAttendance should have PersonnelID and MeetingID from the other 2 tables. This way you can record who attended which meeting in tblAttendance. So don't combine any tables, seems like they are set up correctly.

  9. #9
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Is this the correct way to build the querry that you specified?

    I have attached the zip file of the latest build with the query added. It is QryPersonnel. Is this how you suggested in you most recent post?

    R,

    Lou Reed
    Attached Files Attached Files

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure what your question is now. I'd agree that the tables are fine like they are. qryPersonnel simply grabs active employees. Did you want it to do more?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    No, I did not expect it to do any more. I just wanted to check on whether or not it is correct before building on it.

    If you say they look fine, then I guess that it is okay to start building on them.

    Thanks for your input.

    Respectfully,


    Lou Reed

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Out of curiosity, what does "start building on them" mean? What comes next, in your opinion?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    That is simple. If I think that these tables are okay, I will put in some fake data and see how the whole db system runs. If they are not good, then I will find the error. There is still much to do in creating this db. If they were not good/correct relationships, then what good would it do to start the next part of the db build or "start building on them" as I said.

    I hope that this answers your questions.

    Respectfully,

    Lou Reed

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not really. If you had said create forms then I would have disagreed.

  15. #15
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, then what is the next step? I do not want to begin building new parts of a database based on a sketchy foundation. I am sorry that your question was not answered, so please answer it for me.

    I think the game plan to complete the db that I outlined above is sound. Please tell me where faults are.

    I think making sure the foundation is sound before building on it, is a good idea.

    Respectfully,

    Lou Reed

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-08-2014, 06:08 AM
  2. Complicated Query
    By RozS in forum Access
    Replies: 5
    Last Post: 01-28-2014, 02:49 PM
  3. MOST COMPLICATED query ever
    By dastr in forum Queries
    Replies: 1
    Last Post: 07-05-2012, 04:29 AM
  4. Query with Complicated Requirements
    By Briana in forum Queries
    Replies: 1
    Last Post: 06-13-2012, 08:05 PM
  5. Complicated Query needs some date range help
    By KevinMCB in forum Queries
    Replies: 1
    Last Post: 01-11-2011, 12:25 PM

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