Query Join Problem

[Dave]

I have two queries that I can't seem to join together the way I need it to
(did left join on date & miles). One query is grouping/averaging/totaling.
And the other has the general info. I am working on a transport db that
groups by date and beginning mileage and calculates the total charge for that
run, but still shows each person with general info. It is creating duplicate
totals. This is what it is doing:

Name Date Start Miles From To Avg Time Avg Miles Totals
John Doe 03/03/08 98000 here there 1 60 $100.00
Mike Who 03/03/08 98000 here there 1 60 $100.00
Me Too 03/03/08 98000 here there 1 60 $100.00
James Bro 03/03/08 98500 here there 0.5 30 $50.00
John Doe 03/04/08 99000 here there 1.5 80 $150.00
Me Too 03/04/08 99000 here there 1.5 80 $150.00
Jones Ink 03/05/08 99200 here there 0.25 20 $50.00
Tina Tang 03/06/08 99201 here there 1 55 $110.00

This is what I need it to do:

Name Date Start Miles From To Avg Time Avg Miles Totals
John Doe 03/03/08 98000 here there 1 60 $100.00
Mike Who 03/03/08 98000 here there
Me Too 03/03/08 98000 here there
James Bro 03/03/08 98500 here there 0.5 30 $50.00
John Doe 03/04/08 99000 here there 1.5 80 $150.00
Me Too 03/04/08 99000 here there
Jones Ink 03/05/08 99200 here there 0.25 20 $50.00
Tina Tang 03/06/08 99201 here there 1 55 $110.00

Thank you in advance.

 

[Lord Kelvan]

what is the sql for the query

to get it open the query click on view in the menu bar then click sql
view.

Regards
Kelvan

 

[Dave]

Here is the SQL for the query that unites the other two. I had to remove one
of the outer joins because it gave me an error:
"...contains ambiguous outer joins."

SELECT [UB 3].Last_Name_Inmate, [UB 3].First_Name_Inmate, [UB 3].Date_Trans,
[UB 3].From, [UB 3].To, [UB 3].Round_Trip, [USMS
Query#test1].AvgOfNumber_Deputies, [USMS Query#test1].MaxOfDeputy_Charge,
[USMS Query#test1].[AvgOfSum All Times], [USMS Query#test1].[AvgOfTotal
Miles], [USMS Query#test1].MaxOfBilled_Amount, [USMS Query#test1].[Mile Chg],
[USMS Query#test1].[Dep Chg], [USMS Query#test1].Total, [UB 3].Start_Miles_1
FROM [USMS Query#test1] RIGHT JOIN [UB 3] ON [USMS Query#test1].Date_Trans =
[UB 3].Date_Trans
ORDER BY [UB 3].Date_Trans;

 

[Lord Kelvan]

cough try a left join not a right join

SELECT [UB 3].Last_Name_Inmate, [UB 3].First_Name_Inmate, [UB
3].Date_Trans,
[UB 3].From, [UB 3].To, [UB 3].Round_Trip, [USMS
Query#test1].AvgOfNumber_Deputies, [USMS
Query#test1].MaxOfDeputy_Charge,
[USMS Query#test1].[AvgOfSum All Times], [USMS Query#test1].
[AvgOfTotal
Miles], [USMS Query#test1].MaxOfBilled_Amount, [USMS Query#test1].
[Mile Chg],
[USMS Query#test1].[Dep Chg], [USMS Query#test1].Total, [UB
3].Start_Miles_1
FROM [USMS Query#test1] LEFT JOIN [UB 3] ON [USMS
Query#test1].Date_Trans =
[UB 3].Date_Trans
ORDER BY [UB 3].Date_Trans;

Hope this helps

Regards
Kelvan

 

[Lord Kelvan]

sorry if this is a double post as my privious one has appeared

Try a left join not a right join


SELECT [UB 3].Last_Name_Inmate, [UB 3].First_Name_Inmate, [UB
3].Date_Trans,
[UB 3].From, [UB 3].To, [UB 3].Round_Trip, [USMS
Query#test1].AvgOfNumber_Deputies, [USMS
Query#test1].MaxOfDeputy_Charge,
[USMS Query#test1].[AvgOfSum All Times], [USMS Query#test1].
[AvgOfTotal
Miles], [USMS Query#test1].MaxOfBilled_Amount, [USMS Query#test1].
[Mile Chg],
[USMS Query#test1].[Dep Chg], [USMS Query#test1].Total, [UB
3].Start_Miles_1
FROM [USMS Query#test1] LEFT JOIN [UB 3] ON [USMS
Query#test1].Date_Trans =
[UB 3].Date_Trans
ORDER BY [UB 3].Date_Trans;

 

[Dave]

I tried that before and it didn't work. I tried it again and still no luck. I
still get duplicates where I should be getting blanks.

 

[Lord Kelvan]

can you post some sample data from the two tables before they go
through the query so i can have a look

Regards
Kelvan

 

[Dave]

This is from the "USMS Query#test1":

Date AvgOfSum All Times AvgOfTotal
Miles AvgOfNumber_Deputies MaxOfBilled_Amount

MaxOfDeputy_Charge Mile Chg Dep Chg Total 1st Beginning Milage
1/3/2008 2 125 2 $0.05 $18.55 $6.06 $74.20 $80.26 120050
1/3/2008 2 90 2 $0.05 $18.55 $4.37 $74.20 $78.57 120060
2/3/2008 2 90 2 $0.05 $18.55 $4.37 $74.20 $78.57 120060
2/3/2008 2 100 2 $0.05 $18.55 $4.85 $74.20 $79.05 120200
2/3/2008 2 100 2 $0.05 $18.55 $4.85 $74.20 $79.05 120300
3/3/2008 1 60 2 $0.05 $18.55 $2.91 $37.10 $40.01 95000
3/4/2008 1 60 2 $0.05 $18.55 $2.91 $37.10 $40.01 95060
3/5/2008 1 0 2 $0.05 $18.55 $0.00 $37.10 $37.10 80000

SQL of "USMS Query#test1":

SELECT USMS.Date_Trans, Avg([USMS Time/Miles Totals].[Sum All Times]) AS
[AvgOfSum All Times], Avg

([USMS Time/Miles Totals].[Total Miles]) AS [AvgOfTotal Miles],
Avg(USMS.Number_Deputies) AS

AvgOfNumber_Deputies, Max(USMS.Billed_Amount) AS MaxOfBilled_Amount,
Max(USMS.Deputy_Charge) AS

MaxOfDeputy_Charge, [MaxOfBilled_Amount]*[AvgOfTotal Miles] AS [Mile Chg],
[AvgOfNumber_Deputies]*

[AvgOfSum All Times]*[MaxOfDeputy_Charge] AS [Dep Chg], [Mile Chg]+[Dep Chg]
AS Total,

USMS.Start_Miles_1
FROM USMS RIGHT JOIN [USMS Time/Miles Totals] ON USMS.USMS_ID = [USMS
Time/Miles Totals].USMS_ID
GROUP BY USMS.Date_Trans, USMS.Start_Miles_1
ORDER BY USMS.Date_Trans, USMS.Start_Miles_1;


This is from "UB 3":

Last Name First Name Date From To Round Trip 1st Beginning Milage
Crawfor Garrett 3/3/2008 CCSO USMS No 95000
Ohare Shawn 3/3/2008 CCSO USMS No 95000
Robert James 3/3/2008 CCSO USMS No 95000
Jones Michael 3/4/2008 CCSO USMS No 95060
e.............. 3/4/2008 CCSO USMS No 95060
f.............. 3/5/2008 CCSO USMS No 80000
CrawfordGarrett 1/3/2008 CCSO USMS Yes 120050
CrawfordGarrett 2/3/2008 CCSO USMS No 120300
Ohare Shawn 1/3/2008 CCSO USMS Yes 120050
Ohare Shawn 2/3/2008 CCSO USMS Yes 120200
Robert James 1/3/2008 CCSO USMS Yes 120060
Robert James 2/3/2008 CCSO USMS Yes 120060

SQL of "UB 3":

SELECT Inmates.Last_Name_Inmate, Inmates.First_Name_Inmate, USMS.Date_Trans,
USMS.From, USMS.To,

USMS.Round_Trip, USMS.Start_Miles_1
FROM Inmates INNER JOIN (USMS INNER JOIN [USMS Time/Miles Totals] ON
USMS.USMS_ID = [USMS

Time/Miles Totals].USMS_ID) ON Inmates.Inmate_ID = USMS.Inmate_ID;

 

[Lord Kelvan]

your problem is there is no way to say directally which record belongs
where because of the ability to duplicate data between the two queries
if you added the persons name to the USMS Query#test1 then you would
be able to link the two together but that of course wouldnt help if a
person travels twice in one day OR if two inmates have the same name.

though the data you supplied it does appear that 1 travel per inmate
per day but you have a primary key id for the inmate table which would
resolve duplication ie two inmates with the same name.

as stated above there is no unique way to bind the records and that is
why you are getting data filled where you dont want it

if you added inmate id to both queries and then do a left join between
the queries woudl that resolve your problem or woudl that make it so
the data in the test1 query wouldnt be summarised how you want it.

please tell me what you are trying to achieve not with data but with
words based on the table data not the query data.

ie i want to see how much money is being charged for the travel time
of each inmate.

Regards
Kelvan

 

[Dave]

I'm trying to make a report that will print out all the transports for the
month and the charges for them. There are two tables, one for inmate info and
the other for USMS. There is a one-to-many join between the two. All the
travel info is in the USMS table which I querry to add up the times and miles
to and fro. I run another query to average time/cost/miles based on similar
dates and starting mileage from the USMS table. I have one last query to join
inmate/usms table data with the query of time/cost/miles. Using a left/right
join it should only select data from the 1st query that matches date and
starting mileage.

If I add inmateID or USMSID to the 1st query which groups, averages, and
totals, it will duplicate in there as well. Access thinks I want the totals
in every field plus it will group the inmates together like you said if they
have the same name or go to two different places in the same day. I
appreciate you helping me figure this out. Thanks again.