Access Query pulling records multiple times

[SandyG]

Good day! I am trying to create a query and I'm having issues with it. I'm hoping for some help.

Main table - Owner/Operator (has basic info - name, email address, phone number, Company name)
Truck Table - Lists trucks & trailers that are associated to Owner/Operator (one-to-many relationship)
Driver Table - Lists Fleet drivers & co-drivers that drive for Owner/Operators (one-to-many relationship)

Trying to create a query that provides all the fields from these tables, to be downloaded into Excel.

Every time I run the query though, if an Owner/Operator has 5 trucks and 5 different drivers, it will list the Owner/Operator 25 times! It associates each truck with each driver. I only want it to list the 5 trucks, and the 5 drivers.

Is it possible to do this in one query, or do I need multiple? Right now, I have 2 queries... one for the trucks & one for the drivers. It's a work-around, but I really want it in one query.

Can anyone help?

 

[Joe4]

How have you joined these three tables in your query?
Are both the Truck and Driver table joined directly to the Main table?
Or did you join the Driver table to the Truck table instead?
I think you want both joined directly to the Main table.

If you are unsure, change your query to SQL View, and copy and paste your SQL code here.

 

[SandyG]

How have you joined these three tables in your query?
Are both the Truck and Driver table joined directly to the Main table?
Or did you join the Driver table to the Truck table instead?
I think you want both joined directly to the Main table.

If you are unsure, change your query to SQL View, and copy and paste your SQL code here.

For my query, the Owner/Operator table is the main table. The truck table and the driver tables are both joined to the Owner/Operator table by the ID (auto-number) from the Owner/Operator table. I had used (I believe) outer joins for both, pulling only the pertinent information from the Truck and/or Driver tables in my query. If I just use just the Truck or Driver table with the Owner/Operator table, I have no problems with the query. It's when I try to pull them both, that I'm getting the multiple lines. If I have 5 trucks and 5 drivers, when I run the query, I get 25 lines for the Owner/Operator. It's putting each truck with each driver, and that's not what I need. I need it to just list the 5 trucks, and the 5 drivers. I know it has to do with the SQL code, but I'm not good with that. I will be able to post the SQL code later, as it's at home on my work computer.

 

[Joe4]

It would be helpful if you could post the SQL code of your query, and some sample data from your three tables.
The structure of these things can vary, and is of the utmost importance to solving the issue, and I don't like to make assumptions.

 

[SandyG]

It would be helpful if you could post the SQL code of your query, and some sample data from your three tables.
The structure of these things can vary, and is of the utmost importance to solving the issue, and I don't like to make assumptions.

I will do that this afternoon, after I get home from work. Yes, I also have another work computer at home (different job). The database that I'm working on is on their server, and I connect with their laptop. I will post the SQL code, and show some sample data.

I know there has to be a fix, because I can't be the only person that ever had to deal with this. I have a workaround right now (2 queries - one for trucks and one for drivers), but I really need for my user to be able to pull one report with this data.

 

[SandyG]

I will do that this afternoon, after I get home from work. Yes, I also have another work computer at home (different job). The database that I'm working on is on their server, and I connect with their laptop. I will post the SQL code, and show some sample data.

I know there has to be a fix, because I can't be the only person that ever had to deal with this. I have a workaround right now (2 queries - one for trucks and one for drivers), but I really need for my user to be able to pull one report with this data.

[MENTION]
SELECT [tbl_Owner Operators].ID, tbl_terminals.Terminals, tbl_Regions.Regions, [tbl_Owner Operators].LastName, [tbl_Owner Operators].FirstName, [tbl_Owner Operators].DBA, [tbl_Owner Operators].Email, [tbl_Documentation Checklist].[Contractor Service Agreement], [tbl_Documentation Checklist].[Schedule A Form], [tbl_Documentation Checklist].[Contractor Insurance], [tbl_Documentation Checklist].[Escrow Deduction], [tbl_Documentation Checklist].[Escrow Transfer], [tbl_Documentation Checklist].[Lease Schedule and Signed Lease], [tbl_Documentation Checklist].[Employee Lease Agreement], [tbl_Documentation Checklist].[URS Application], [tbl_Documentation Checklist].[Employee Data Record], [tbl_Documentation Checklist].[Direct Deposit request], [tbl_Documentation Checklist].[W-9], [tbl_Documentation Checklist].[Federal ID Number], [tbl_Documentation Checklist].[Valid DL], [tbl_Documentation Checklist].CARBCompliance, [tbl_Documentation Checklist].Worksheet, [tbl_Documentation Checklist].[2290 Hwy Use Tax Form], [tbl_Documentation Checklist].[Tractor-Trailer Title], [tbl_Documentation Checklist].[Trailer Registration], [tbl_Documentation Checklist].[DOT Inspection], [tbl_Documentation Checklist].[WGT Slip], [tbl_Documentation Checklist].[Lien Holder Name], [tbl_Documentation Checklist].[Lien Holder Address], [tbl_Documentation Checklist].[Lien Holder City], [tbl_Documentation Checklist].[Lien Holder State], [tbl_Documentation Checklist].[Lien Holder Zip], [tbl_Documentation Checklist].[Title Work Required], [tbl_Documentation Checklist].[Plate Transfer], [tbl_Documentation Checklist].OpenorEnclosed, tbl_Trucks.TruckNo, tbl_Trucks.TractorPrice, tbl_Trucks.[Tractor Purchase Date], tbl_Trucks.TractorYear, tbl_Trucks.TractorMake, tbl_Trucks.TractorVIN, tbl_Trucks.TractorAxles, tbl_Trucks.TractorEmptyWeight, tbl_Trucks.[Tractor Suspension], tbl_Trucks.TractorLength, tbl_Trucks.[Tractor Gross Weight], tbl_Trucks.TrailerUnitNo, tbl_Trucks.TrailerPrice, tbl_Trucks.[Trailer Purchase Date], tbl_Trucks.TrailerYear, tbl_Trucks.Trailermake, tbl_Trucks.TrailerVIN, tbl_Trucks.TrailerAxles, tbl_Trucks.TrailerEmptyWeight, tbl_Trucks.[Trailer Suspension], tbl_Trucks.TrailerLength, tbl_Trucks.OverallLength, tbl_Trucks.TractorNoUnitstobeHauled, tbl_Trucks.TrailerNoUnitstobeHauled, tbl_Trucks.TotalNoUnitstobeHauled, tbl_Trucks.[Fuel Type], tbl_Trucks.[Fuel Capacity], tbl_Trucks.[Tractor Type], tbl_Trucks.[Tractor Body Structure], tbl_Trucks.[TRL Width], tbl_Trucks.[Trailer Type], tbl_Trucks.[Trailer Body Structure], [tbl_Owner Operators].HireDate, [tbl_Documentation Checklist].[Articles of Incorporation (CDA)], [tbl_Documentation Checklist].[Authorization Letter (CDA)], [tbl_Documentation Checklist].[Annual Inspection (CDA)], [tbl_Documentation Checklist].[Purchase Info (CDA)], [tbl_Documentation Checklist].[Cargo Insurance Election Form (CDA)], [tbl_Documentation Checklist].[Escrow Account (CDA)], [tbl_Documentation Checklist].[Fuel Card Form], tbl_Addresses.OwnerAddress1, tbl_Addresses.OwnerAddress2, tbl_Addresses.City, tbl_Addresses.State, tbl_Addresses.ZipCode, tbl_Trucks.DriverforOwner, [tbl_Owner Operators].PrimaryPhone, [tbl_Owner Operators].UserID, [tbl_Owner Operators].PayrollID, [tbl_Owner Operators].[Client ID], [tbl_Owner Operators].NewHire, [tbl_Owner Operators].Replacement, [tbl_Owner Operators].Active, [tbl_Owner Operators].[Start Date], [tbl_Owner Operators].FirstHaulDate, [tbl_Owner Operators].Transfer, [tbl_Owner Operators].TransferDate, [tbl_Owner Operators].Terminated, [tbl_Owner Operators].TerminationDate, [tbl_Owner Operators].PayType, [tbl_Owner Operators].CargoInsurance, [tbl_Owner Operators].PaymentMethod, [tbl_Owner Operators].[Record Added], [tbl_Owner Operators].[Re-Hire], [tbl_Owner Operators].[Re-Hire Date], [tbl_Owner Operators].Addendum, [tbl_Owner Operators].[Signed Contract], [tbl_Owner Operators].[ELD Install Date], [tbl_Owner Operators].[Option A], [tbl_Owner Operators].[Bonus Eligible], [tbl_Owner Operators].[Option B], [tbl_Owner Operators].[Option C], [tbl_Owner Operators].[Date Modified], [tbl_Owner Operators].[Insurance Contract], [tbl_Owner Operators].[ELD Active], [tbl_Owner Operators].[ELD Bonus Paid Date], tbl_Addresses.[Active Address], tbl_Trucks.Inactive, [tbl_Fleet Drivers].[First Name], [tbl_Fleet Drivers].[Last Name], [tbl_Fleet Drivers].[Fleet Driver], [tbl_Fleet Drivers].CoDriver, [tbl_Fleet Drivers].Birthdate, [tbl_Fleet Drivers].Active
FROM tbl_terminals INNER JOIN (tbl_Regions INNER JOIN (((([tbl_Owner Operators] LEFT JOIN [tbl_Documentation Checklist] ON [tbl_Owner Operators].ID = [tbl_Documentation Checklist].[EmployeeID]) LEFT JOIN tbl_Addresses ON [tbl_Owner Operators].ID = tbl_Addresses.EmployeeID) LEFT JOIN [tbl_Fleet Drivers] ON [tbl_Owner Operators].ID = [tbl_Fleet Drivers].EmployeeID) LEFT JOIN tbl_Trucks ON [tbl_Owner Operators].ID = tbl_Trucks.EmployeeID) ON tbl_Regions.RegionID = [tbl_Owner Operators].Region) ON tbl_terminals.TerminalID = [tbl_Owner Operators].Terminals
WHERE ((([tbl_Owner Operators].Active)=Yes) AND ((tbl_Addresses.[Active Address])=Yes) AND ((tbl_Trucks.Inactive)=No));
[/MENTION]

It will not let me add any attachments. Only the SQL code.

Bottom line - I have a main table "tbl_Owner Operator". There are several tables that link back to this table, and they all have a one-to-many relationship. For the "tbl_Trucks" and "tbl_Fleet Drivers", I need to pull information from each of these. Now, they don't link back together, but they each link to "tbl_Owner Operator" via ID (EmployeeID in tbl_Trucks & tbl_Fleet Drivers).

I have used Inner Joins to pull all the data, and up until now, it was working great. If there is an Owner/Operator listed in the "tbl_Owner Operator" and that Owner has 5 trucks, then there are 5 entries in "tbl_Trucks". Now, if that Owner also has 5 fleet drivers, driving for him, they are listed under "tbl_Fleet Drivers", and there will be an entry for each driver. The drivers are not associated with the trucks.

I need to pull a query using these tables, but not have the query list the Owner/Operator 25 times. (trucks 12345, 56789 and drivers John Doe, Santa Claus).

Run query - will list Owner/Operator, then truck 12345 with driver John Doe. Next line, will list Owner/Operator, then truck 12345 with driver Santa Claus. Next line, lists Owner/Operator with truck 56789 and driver John Doe. Next line, lists Owner/Operator, with truck 56789 and driver Santa Claus. That is not what I'm looking for.

It should list the Owner/Operator with all of his/her trucks, and all of his/her drivers.

I can do a query with either the trucks or the drivers, and it works fine. It's when I try to combine it.

Any help would be very much appreciated!

 

[Joe4]

OK, that query is a LOT more complex than you first let on. There are a lot more than 3 tables in it!
When you run into issues like this, it is usually best to start small, then slowly build on to it (checking the results of each step along the way). Then it is easier to tell exactly where things got off track.
So I would recommend starting with just your three tables, to see if you can get what you want, then build off of that.

Note: You cannot attach files, but you can post data. Simply open your table/query, highlight the data you want to copy, and paste it here like below:

1	1/15/2010	1/15/2016
2	1/15/2010	1/15/2018
3	1/1/2010	4/1/2016
4	1/1/2010
5		1/1/2016
6	2/28/2004	2/28/2010

<caption> Table1 </caption><thead>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] date1column [/TH]
[TH="bgcolor: #c0c0c0"] date2column [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Just be sure NOT to post any sensitive data, or the entire data table, if it is a lot of rows.

 

[SandyG]

OK, that query is a LOT more complex than you first let on. There are a lot more than 3 tables in it!
When you run into issues like this, it is usually best to start small, then slowly build on to it (checking the results of each step along the way). Then it is easier to tell exactly where things got off track.
So I would recommend starting with just your three tables, to see if you can get what you want, then build off of that.

Note: You cannot attach files, but you can post data. Simply open your table/query, highlight the data you want to copy, and paste it here like below:

1	1/15/2010	1/15/2016
2	1/15/2010	1/15/2018
3	1/1/2010	4/1/2016
4	1/1/2010
5		1/1/2016
6	2/28/2004	2/28/2010

<tbody>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] date1column [/TH]
[TH="bgcolor: #c0c0c0"] date2column [/TH]

</tbody>

Just be sure NOT to post any sensitive data, or the entire data table, if it is a lot of rows.

Thank you..... the query worked until I attempted to add the additional table (tbl_drivers). Originally, the drivers were listed under the tbl_trucks, but then the VP decided that the drivers should not be associated with the trucks (drivers change, trucks don't), so I created a new table for the drivers. When I added the new table to the query, that's when the problem began (because the drivers and trucks don't associate to each other, but they do associate back to the Owner/Operator)

 

[Joe4]

So, can you post sample data from just the three tables (mainly so we can see the key fields and how they relate to the other tables), and the SQL code of just the very simple query joining the three tables?
If you can do that, then I can try to recreate your situation here, and see if I can help you get it straightened out.