Query Question

[DavidW]

I have got 2 tables that are joined by "veh" and "date". When each record if
the vehicle number is not the same , as in one vehicle did use misc fuel and
gasoline, and the other only used misc fuel, the query will not show both
entries, it will only show both entries by "veh" in the first table
"gasoline" , if there is only misc fuel it will not show up. Kinda
confusing!

Here is an Example
tblgasoline
veh date gal
1 10/10 5.5
2 10/10 11

tblmiscfuel
veh date mgal
1 10/10 45
2 10/10 20
3 10/10 50

what it is doing no
gasquery
veh date mgal gal
1 10/10 45 5.5
2 10/10 20 11

what it needs to do
gasquery
veh date mgal gal
1 10/10 45 5.5
2 10/10 20 11
3 10/10 50

Thanks David

 

[Jeff Boyce]

David

I suspect your query uses a standard join, which tells Access to find all
records from both tables, but ONLY when the ID field(s) match. It looks
like you have a situation in which you want to see ALL records, even if
there isn't a match in one of the tables.

Good news, and bad ... If you will always be working from the "misc" table
to the gasoline table, change the join properties (right click on the join,
pick "all of 1, any of 2"). But if the "missing" records could be in
either, ... that'll be more work.

Good luck

Jeff Boyce
<Access MVP>

 

[DavidW]

Jeff
This is going to be difficult because the missing record might be in one or
the other, how do you go about that senerio
Thanks for Responding
David

 

[Kelvin]

I know there is a way to do this with joins but here is a simple method just
using basic queries.

1) Create a unions query of your 2 tables. For tblGasoline include all 3
fields and add another field "mgal" set to 0. For tblmiscfuel do the same
but this time include "gal" and set to 0. Make sure the order of the fields
is the same for the 2 tables.

select veh, date, 0 as mgal, gal from tblgasoline
union select veh, date, mgal, 0 as gal from tblmiscfuel;

1 10/10 0 5.5
2 10/10 0 11
1 10/10 45 0
2 10/10 20 0
3 10/10 50 0

2) Create a group by query of the union query. summing gal and mgal

1 10/10 45 5.5
2 10/10 20 11
3 10/10 50 0

Kelvin