Join Trouble

Sir Phoenix

Board Regular
Joined
Aug 30, 2006
Messages
146
I have two entities. A truck and some truck parts.

A truck can have many parts.

I have a query that tells me info about the truck, as well as how much total time is left on the truck's parts. To do this, I take the sum of the 'time left' on each truck part, then group it by truck.

However, I have two issues.

I need to see all trucks, even if they don't have any parts. An inner join excludes these trucks.

If I try to solve the above with a left join (show all trucks, and show all matching parts), I get an error, I assume from trying to SUM a field that doesn't exist. (In the case of trucks without parts)

How do I solve this?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
How are you tables setup? What data are you using? An example of your query would be helpful.

CT
 

Sir Phoenix

Board Regular
Joined
Aug 30, 2006
Messages
146
SELECT [Truck].truckID, [Truck].[Order#], [Truck].Priority, Sum(TBA.Hours) AS SumOfHours
FROM [Truck] LEFT JOIN TBA ON [Truck].truckID = TBA.truckID
GROUP BY [Truck].truckID, [Truck].[Order#], [Truck].Priority
ORDER BY [Truck].Priority;

Or switch "LEFT JOIN" with "INNER JOIN" to get the first version.

If I try to run this, I get "No current Record" (left join)

or

Some trucks will be omitted, the ones without TBAs. (inner join)
 
Last edited:

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
Ok, I have table Truck:

----------------------------------------------------------------
| truckID | Order# | Priority |
----------------------------------------------------------------
| 1 | 1 | 1 |
----------------------------------------------------------------
| 2 | 2 | 2 |
----------------------------------------------------------------
| 3 | 3 | 3 |
----------------------------------------------------------------

table TBA

-------------------------------------------
| TruckID | Hours |
-------------------------------------------
| 1 | 10 |
-------------------------------------------
| 2 | 5 |
-------------------------------------------

Query:

Code:
SELECT Truck.truckID, Truck.[Order#], Truck.Priority, Sum(TBA.Hours) AS SumOfHours
FROM Truck LEFT JOIN TBA ON Truck.truckID = TBA.TruckID
GROUP BY Truck.truckID, Truck.[Order#], Truck.Priority
ORDER BY Truck.Priority;

Yields

-------------------------------------------------------------------------------------
| truckID | Order# | Priority | SumOfHours |
-------------------------------------------------------------------------------------
| 1 | 1 | 1 | 10 |
-------------------------------------------------------------------------------------
| 2 | 2 | 2 | 5 |
-------------------------------------------------------------------------------------
| 3 | 3 | 3 | |

Everything looks ok to me.
 

Sir Phoenix

Board Regular
Joined
Aug 30, 2006
Messages
146
Hrm. Looking at this query again, I see that it didn't get it's data from the Truck table, but from a query that selects the Truck table. I rewrote it only using tables and it seems to work now. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top