help combining queries please 2

[twospoons]

ok... i have a couple of tables

Sales(CompanyNo, SaleID, ReceiptID, SaleDate, VendorID, ItemDescription, PaymentType, Amount, Taxable, Discount, SalesTax)

ConsignorNames(VendorID, ConsignorName, ConsignorNo, CommPercent, CCharge)

these are linked by the VendorID.

i also have another table that just stores a beginning and ending date to limit the queries search range.

SearchSummary(BeginDate, EndDate)

i am trying to build a report that contains the totals by VendorID of taxable sale, exempt sale, discount, commission, credit card fees and grand total. i have build three seperate queries that contain all of the information i am needing... but i can't figure out how to combine these three queries so that all of the information is in one location so that i can print a report from it.

here is the sql from the queries:

Daily_Sales_tax: the first gets the sum of all the taxable sales amounts, and the discounts and commissions associated with those sales...

SELECT Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Sum(Sales.Amount) AS Amount_tax, Sum(Sales.Discount) AS Discount_tax, Sum([Amount]*[CommPercent]) AS Commission_tax
FROM SearchSummary, ConsignorNames INNER JOIN Sales ON ConsignorNames.VendorID = Sales.VendorID
WHERE (((Sales.SaleDate)>[SearchSummary.BeginDate] And (Sales.SaleDate)<[SearchSummary.EndDate]))
GROUP BY Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Sales.Taxable
HAVING (((Sales.Taxable)=Yes));

Daily_Sales_exempt: the second is the same as the first but tax exempt sales instead of taxable...

SELECT Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Sum(Sales.Amount) AS Amount_exempt, Sum(Sales.Discount) AS Discount_exempt, Sum([Amount]*[CommPercent]) AS Commission_exempt
FROM SearchSummary, ConsignorNames INNER JOIN Sales ON ConsignorNames.VendorID = Sales.VendorID
WHERE (((Sales.SaleDate)>[SearchSummary.BeginDate] And (Sales.SaleDate)<[SearchSummary.EndDate]))
GROUP BY Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Sales.Taxable
HAVING (((Sales.Taxable)=No));

Daily_Sales_cc: the third is a total of all the charged amounts and the credit card fees associated...

SELECT Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Sum(Sales.Amount) AS Amount_cc, Sum(Sales.Discount) AS Discount_cc, Sum(([Amount]-[Discount])*[CCharge]) AS CCFee
FROM SearchSummary, ConsignorNames INNER JOIN Sales ON ConsignorNames.VendorID = Sales.VendorID
WHERE (((Sales.SaleDate)>[SearchSummary.BeginDate] And (Sales.SaleDate)<[SearchSummary.EndDate]) AND ((Sales.PaymentType)="Charge"))
GROUP BY Sales.VendorID, ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName;

i tried using the ConsignorNames along with the three queries i just created to create one query that links them all together...

SELECT ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Daily_Sales_tax.Amount_tax, Daily_Sales_exempt.Amount_exempt, [Discount_tax]+[Discount_exempt] AS Discount, [Commission_tax]+[Commission_exempt] AS Commission, Daily_Sales_cc.CCFee, [Amount_tax]+[Amount_exempt]-([Discount_tax]+[Discount_exempt])-([Commission_tax]+[Commission_exempt])-[CCFee] AS Total
FROM ((ConsignorNames INNER JOIN Daily_Sales_tax ON ConsignorNames.VendorID = Daily_Sales_tax.VendorID) INNER JOIN Daily_Sales_exempt ON ConsignorNames.VendorID = Daily_Sales_exempt.VendorID) INNER JOIN Daily_Sales_cc ON ConsignorNames.VendorID = Daily_Sales_cc.VendorID;

but i realize it is only displaying the vendors that have a hit in the table AND the three queries... i tried reworking it with AND/OR statements instead of all the inner joins... but i still can't get it to work properly.

any help is greatly appreciated!

 

[twospoons]

here's the examples of using AND and OR

this first one is only returning three results cause it is looking for records that match the VendorID in all four sources...

SELECT ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Daily_Sales_tax.Amount_tax, Daily_Sales_exempt.Amount_exempt, [Discount_tax]+[Discount_exempt] AS Discount, [Commission_tax]+[Commission_exempt] AS Commission, Daily_Sales_cc.CCFee, [Amount_tax]+[Amount_exempt]-([Discount_tax]+[Discount_exempt])-([Commission_tax]+[Commission_exempt])-[CCFee] AS Total
FROM ConsignorNames, Daily_Sales_tax, Daily_Sales_exempt, Daily_Sales_cc
WHERE ConsignorNames.VendorID = Daily_Sales_tax.VendorID AND ConsignorNames.VendorID = Daily_Sales_exempt.VendorID AND ConsignorNames.VendorID = Daily_Sales_cc.VendorID;

this returns like 4141 results because its matching every possible combination of my table and queries

SELECT ConsignorNames.ConsignorNo, ConsignorNames.ConsignorName, Daily_Sales_tax.Amount_tax, Daily_Sales_exempt.Amount_exempt, [Discount_tax]+[Discount_exempt] AS Discount, [Commission_tax]+[Commission_exempt] AS Commission, Daily_Sales_cc.CCFee, [Amount_tax]+[Amount_exempt]-([Discount_tax]+[Discount_exempt])-([Commission_tax]+[Commission_exempt])-[CCFee] AS Total
FROM ConsignorNames, Daily_Sales_tax, Daily_Sales_exempt, Daily_Sales_cc
WHERE ConsignorNames.VendorID = Daily_Sales_tax.VendorID OR ConsignorNames.VendorID = Daily_Sales_exempt.VendorID OR ConsignorNames.VendorID = Daily_Sales_cc.VendorID;

there should be about 38 results in the correct query but i can't figure it out.

please help

 

[twospoons]

anybody???

 

[lespaul]

why don't you provide some sample table data and what your final query results should be from those tables.

leslie

 

[PHV]

You may try something like this:
SELECT N.ConsignorNo, N.ConsignorName, Nz(T.Amount_tax,0) AS Tax, Nz(E.Amount_exempt,0) AS Exempt, Nz([Discount_tax],0)+Nz([Discount_exempt],0) AS Discount, Nz([Commission_tax],0)+Nz([Commission_exempt],0) AS Commission, C.CCFee, Nz([Amount_tax],0)+Nz([Amount_exempt],0)-Nz([Discount_tax],0)-Nz([Discount_exempt],0)-Nz([Commission_tax],0)-Nz([Commission_exempt],0)-Nz([CCFee],0) AS Total
FROM ((ConsignorNames N
LEFT JOIN Daily_Sales_tax T ON N.VendorID = T.VendorID)
LEFT JOIN Daily_Sales_exempt E ON N.VendorID = E.VendorID)
LEFT JOIN Daily_Sales_cc C ON N.VendorID = C.VendorID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[twospoons]

ok... thanks phv... i think i'm moving in the right direction now... i've never used left join before... how exactly does that work?

 

[lespaul]

Understanding SQL Joins does a great job explaining the differences in types of joins.

Glad you're making progress!

Leslie

 

[twospoons]

awesome! i got it working... thanks a million