Results 1 to 7 of 7
  1. #1
    Bentley is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    50

    Query pulling multiple Order from other tables

    Hello,

    I hope this is the correct location for this question. I have two separate tables that contain equipment numbers. The first is for existing equipment to be installed. the second is for the information to any repairs done to the equipment. and the third is for the replacement of the equipment. My question is how to create a query for a report that can link all three equipment numbers so the report list all the work done to that piece of equipment? I do have relationships created between the existing and the repairs to be done and if replaced.

    Thanks in advance,

    Ben

  2. #2
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Do you not have a table for Equipment (tblEquipment)? This would be the parent table to the three tables you notes, with the child tables having the PrimaryKey of tblEquipment as a field. This would then allow you to left join the three tables on tblEquipment to return all matching records in the three tables for the applicable piece of equipment.

  3. #3
    Bentley is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    50
    Okay, I will create this table with just equipment numbers and descriptions. So when writing the query, how do you return all matching values as the other three tables will be look ups based on the tblEquipment

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Just do Left Joins from your "main" equipment table to all these other tables, joining on the equipment number fields.

  5. #5
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    You should have something like:

    tblEquipment: EquipmentID, ...
    tblEquipmentInstalls: EquipmentInstallID, EquipmentID
    tblEquipmentRepairs: EquipmentRepairID, EquipmentID

    In Database Relationships, tblEquipment will be 1-sided with the other two tables joining on the same field as Many-sided.

    The third table, tblEquipmentReplaced - I don't think you need this table. If you replace a piece of equipment, then you add a new record in tblEquipment for the new piece, and (perhaps) the record for the old piece of equipment will have a field for Date Decommissioned ('retiring' the record instead of deleting it).

    If you think different, then please post your table structures with your reasoning so I and others can have a look.

  6. #6
    Bentley is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    50
    Thanks... That makes perfect sense.. I am still monkeying with the query. Is there any way to get the query to have the equipment list in sequential rows? Like date installed, then when its repaired on the next row?

  7. #7
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Off the top of my head (and not necessarily the best idea) maybe have another field in each table for the Type - R for Repair, I for Install - then you could sort on that field.

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

Similar Threads

  1. Query pulling data from multiple tables
    By jetman5843 in forum Queries
    Replies: 3
    Last Post: 04-08-2014, 12:27 PM
  2. Replies: 1
    Last Post: 07-26-2013, 06:01 AM
  3. Pulling from Multiple Tables in VB
    By Canadiangal in forum Programming
    Replies: 12
    Last Post: 04-04-2013, 10:20 PM
  4. Replies: 5
    Last Post: 12-27-2012, 02:54 PM
  5. Replies: 2
    Last Post: 03-29-2012, 04:03 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