Joins

[cscsonline]

I have query 1 from table A that gives me several records per ID.
I have query 2 from Table B that gives me a single record per ID
I have query 3 which incorporates query 1 and query 2 but the results repeat the results from query 2 several times (the same number of times as there are records in query 1).
How can I join these two queries so that I get one record per ID from query 2 and several records per ID from query 1. Is this an outer-join?

 

[Remou]

I do not believe that you can do this with any particular join type, unless you can join several fields and thus achieve an exact match. It is possible that you require SELECT DISTINCT on query 2, but it is hard to say what is required from the information you supply. A little sample data and an expected outcome are always welcome in this forum.

 

[dhookom]

cscsonline,
Can you give us just a few records from each individual query and then how you would expect them to display in a combined query?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cscsonline]

Query 1
SELECT Jobs.Jobs.Number, Jobs.JobName, JobDisbursements.DisbursementCode, JobDisbursements.Quantity, JobDisbursements.UnitPrice, [Quantity]*[UnitPrice] AS ST, JobDisbursements.OtherCosts, ([ST]+[OtherCosts]) AS Tot
FROM Jobs INNER JOIN JobDisbursements on Jobs.JobNumber = JobDisbursements.JobNumber;

gives

multiple disbursements per Job Number

Query2
SELECT Invoices.JobNumber, Sum(Invoices.Disbursement) AS SumOfDisbursement
FROM Invoices
GROUP BY Invoices.JobNumber;

gives one number (totalled) by Job number

Query3
SELECT Query1.Jobs.Number, Query1.JobName, JQuery1.DisbursementCode, Query1.Quantity, Query1.UnitPrice, Query1.ST, Query1.OtherCosts, Query1.Tot Query2.SumOfDisbursement, Query1.Tot -Query2.SumOfDisbursement AS Difference
FROM Query1 INNER JOIN Query2 on Query1.JobNumber = Query2.JobNumber

End result multiples SumOfDisbursement by the number of entries from Query1 instead of taking the sole sum from Query2

Query1 Query2
Jobs.JobNumber-------Invoices.JobNumber
Jobs.JobName SumOfDisbursement
JobsDisb.DisbCode
JobsDisb.Quantity
JobDisb.UnitPrice

Jobs.JobNumber
Jobs.JobName
JobsDisb.DisbCode
JobsDisb.Quantity
JobDisb.UnitPrice

Repeating as needed One Entry

Query3
Query1.JobNumber)
Query1.JobName )
Query1.DisbCode ) repeating as number of entries e.g. 6 times
Query1.Quantity )
Query1.UnitPrice )
Query2.SumOfDisbursement ) if above number is 6 it multiples Sum by 6

I hope this is clear. Thanks for any help.

 

[dhookom]

You still haven't keyed in how you would expect your query to display the results. If you are using this query/recordset for a report then you have several options.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cscsonline]

Like this

a b c d e f g h i j
Job Job Disb Quantity Unit ST Other Tot SumOf Diff
Number Name Code Price Costs Disb

d*e f+g h-i

should be like this
123 Name 478 10 2.20 22.00 2.00 24.00 18.00 4.00
123 Name 567 6 1.00 26.00 1.00 27.00 18.00 9.00

Total 16 3.20 48.00 3.00 51.00 36.00* 13.00

36.00 should be 18.00 as I don't want column i repeating as it is a sum already

 

[dhookom]

So, your issue is displaying the results in a report? In the Total, you can omit the Sum() and simply display the SumOfDisbursement.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cscsonline]

OK Thanks a lot.

By doing this will the total line compute properly: i.e. 51-18=33

 

[dhookom]

In a report or group footer, you should be able to use a control source like:
=Sum([TotalField]) - [SumOfDisbursement]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]