Query from multiple tables

[CraigBFG]

I need to run a query/report which contains 3 unrelated ledger tables related to a single contract table. However, if I simply enter these fields, I get what I can only describe as "cascading duplication" where there are no records

IS there any way to do this without running 3 seperate queries??

ContractTable SalesTable | PurchaseTable | SubcontractTable
-------------------------------------------------------------------------------------
Contract1 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
Contract2 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
Contract3 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
etc

Thanks

 

[RV]

You can use UNION to combine queries.

RV

 

[CraigBFG]

Never used those, how would this be set up??

 

[Mile-O]

Craig,

A UNION query is a collection of SQL statements that have the same structure (i.e. number of fields, data types) which are placed - like building bricks - "on top of each other".

The SQL statements are joined with the UNION keyword like so:

SELECT DataID, MyData, YourData
FROM DataTable
UNION
SELECT StuffID, MyStuff, YourStuff
FROM StuffTable;

You can only create a UNION query from the SQL View accessed from the Query Design Grid.

 

[CraigBFG]

That explains it, but only 2 of my tables have the same layout.

Is my requirement achieveable???

 

[Mile-O]

Then you'll be able to join two of them.

What's the three query layouts?

 

[CraigBFG]

ContractTable SalesTable | PurchaseTable | SubcontractTable
-------------------------------------------------------------------------------------
Contract1 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
Contract2 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
Contract3 Sum(Sales) | Sum(Purchases) | Sum(Subcontract)
etc

So something like....

For each contract within the contracts table, show me sum(SalesTotal), Sum(PurchaseTotal) & Sum(SubcontractTotal).

 

[CraigBFG]

In a gridview, the query would look like this - which doesn't work and gives me "cascading duplication".

SELECT tContracts.ContractName, tPInvoice.Total, tSalesInv.Labour, tSalesInv.LabourCIS, tSalesInv.Materials, tSubbyInv.Labour, tSubbyInv.LabourCIS, tSubbyInv.Materials
FROM ((tContracts INNER JOIN tPInvoice ON tContracts.ContractID = tPInvoice.ContractId) INNER JOIN tSalesInv ON tContracts.ContractID = tSalesInv.ContractId) INNER JOIN tSubbyInv ON tContracts.ContractID = tSubbyInv.ContractId;

 

[Pat Hartman]

You cannot join tables that have no relation to each other just because they share a common field.

To produce a report that totals properly, you'll need to create a main report with three subreports or use a Union query.

A union query needs its select queries to produce identically formated recordsets. Therefore, if one query has more fields than the others, the select's for the others need to include dummy fields.

Select "tbl1" as DataSource, fld1, fld2, fld3 From tbl1
Union Select "tbl2" As DataSource, fldA, null, fldC From tbl2
Union Select "tbl3" As DataSource, fld1, fld2, "fldC" From tbl3;

I also included a DataSource field. You will need something similar to identify what type of data a row contains so you can label it properly or break if necessary.

 

[CraigBFG]

Ok, I've sort of got this Union business working, but it still isn't quite right.

The results need to group on the contract, as per my original example - but here's what I come up with.

Select ContractId, sum(Labour) As SLLabour, sum(Materials) As SLMaterials, 0 As SCLabour, 0 As SCMaterials,0 As PL From tSalesInv Group by ContractId
Union Select ContractId, 0,0, sum(Labour) As SCLabour, sum(Materials) As SCMaterials,0 From tSubbyInv Group By ContractId
Union Select ContractId, 0,0,0,0, sum(Total) From tPInvoice Group By ContractId;

see attachment

any ideas???