MS Access Join Query

A

AR

I am trying to create a query containing specific information. Let me
explain...

I have the following tables:

Checkout Table contains the following fields:

CheckoutID
CheckoutDate
ClearanceCost
PaymentMethodID
OperatorID

PaymentMethod table contains the following fields:

PaymentMethodID
PaymentMethod
ClearanceCharge

ProductSale table contains the following fields:

ProductSaleID
UnitCost
CheckoutID

Assume the PaymentMethod table contains the following data:

PaymentMethodID PaymentMethod ClearanceCharge
1 Cash 0
2 Maestro Dom 0.565
3 Electron 0.35
4 Solo 0.345
5 Visa Delta 0.37
6 Mastercard 0.195
7 Visa 0.195
8 Amex 0.266

I want to calculate sum(ProductSale.UnitCost) where PaymentMethod is NOT
equal to Cash or Amex. I have mocked this up and it seems to work:


SELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales,
Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS StreamlineCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=2 Or (Checkout.PaymentMethodID)=3 Or
(Checkout.PaymentMethodID)=4 Or (Checkout.PaymentMethodID)=5 Or
(Checkout.PaymentMethodID)=6 Or (Checkout.PaymentMethodID)=7))
GROUP BY Checkout.CheckoutDate ORDER BY Checkout.CheckoutDate DESC;


I also want to calculate sum(ProductSale.UnitCost) where PaymentMethod=Amex.
Again, I have mocked this up and it seems to work:

SELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales,
Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS AmexCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=8))
GROUP BY Checkout.CheckoutDate
ORDER BY Checkout.CheckoutDate DESC;

These queries only return results where the ValueOfSales and NumberOfSales
is not zero... i would like them to return results for each day even if the
result is zero...

Also, I want the results from both queries as one result set i.e. I want the
results to look like:

Checkoutdate ValueOfSales NumberOfSales StreamlineCost AmexCost
2008.01.27 200.00 5
14.95 0.00
2008.01.28 235.64 7
15.98 1.45
....

I want two extra columns to be displayed ... even though their values might
be zero for a particular day.

Any help appreciated...

Thanks
 
A

Allen Browne

Part 1: Get all dates
===============
The extra days have to come from somewhere, so you will need a table that
contains all the dates. Create a table with one Date/Time field named
TheDate. Mark that field as primary key, and save the table as tblDate. You
can then use the function below to populate it with all the dates you need,
e.d.:
? MakeDates(#1/1/2008#, #1/1/2020#)

Now create a query using your existing query and tblDate.
In the upper pane of query design, drag the date field from tblDate.TheDate
and drop onto the date field in your query.
Access draws a line joining the 2 tables.
Double-click this line.
Access pops up a dialog with 3 options.
Choose the one that says:
All records from tblDate, and any matches from Query1.
This is called an outer join, and delivers a record for every date.
Add criteria under tblDate.TheDate if you don't want all 12 years.
If outer joins are new, this might help:
http://allenbrowne.com/casu-02.html

Part 2: Convert rows to columns
=======================
Use a crosstab query to convert the rows you have into columns.

Choose Crosstab on the Query menu.
Access adds Total and Crosstab rows.
Make the date field a Row Heading
Make the payment method a Column Heading.
Make the value of sales the Value.

More info about manipulating crosstabs:
http://allenbrowne.com/ser-67.html

Finally, here is the function to populate tblDate:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
K

KARL DEWEY

To have it list all dates create a table named CountNumber with field
CountNUM containing integers from 0 (zero) through your maximum date spread.
Use this query ---
SELECT DateAdd("d",[CountNUM],[Enter Report Start Date]) AS CheckoutDates,
Sum(IIf(DateAdd("d",[CountNUM],[Enter Report Start
Date])=[CheckoutDate],[UnitCost],0)) AS ValueOfSales,
Sum(IIf(DateAdd("d",[CountNUM],[Enter Report Start
Date])=[CheckoutDate],1,0)) AS NumberOfSales,
Sum(IIf(DateAdd("d",[CountNUM],[Enter Report Start
Date])=[CheckoutDate],(IIf([PaymentMethodID]=8 Or
[PaymentMethodID]=1,0,[ClearanceCost])),0)) AS StreamlineCost,
Sum(IIf(DateAdd("d",[CountNUM],[Enter Report Start
Date])=[CheckoutDate],(IIf([PaymentMethodID]=8,[ClearanceCost],0)),0)) AS
AmexCost
FROM CountNumber, Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
WHERE (((DateAdd("d",[CountNUM],[Enter Report Start Date])) Between
CVDate([Enter Report Start Date]) And CVDate([Enter Report End Date])) AND
((Checkout.CheckoutDate) Between [Enter Report Start Date] And [Enter Report
End Date]))
GROUP BY DateAdd("d",[CountNUM],[Enter Report Start Date])
ORDER BY DateAdd("d",[CountNUM],[Enter Report Start Date]) DESC;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Is this what you are looking for ---
SELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales,
Count(*) AS NumberOfSales, Sum(IIf([PaymentMethodID]=8 Or
[PaymentMethodID]=1,0,[ClearanceCost])) AS StreamlineCost,
Sum(IIf([PaymentMethodID]=8,[ClearanceCost],0)) AS AmexCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
GROUP BY Checkout.CheckoutDate
ORDER BY Checkout.CheckoutDate DESC;

--
KARL DEWEY
Build a little - Test a little


AR said:
I am trying to create a query containing specific information. Let me
explain...

I have the following tables:

Checkout Table contains the following fields:

CheckoutID
CheckoutDate
ClearanceCost
PaymentMethodID
OperatorID

PaymentMethod table contains the following fields:

PaymentMethodID
PaymentMethod
ClearanceCharge

ProductSale table contains the following fields:

ProductSaleID
UnitCost
CheckoutID

Assume the PaymentMethod table contains the following data:

PaymentMethodID PaymentMethod ClearanceCharge
1 Cash 0
2 Maestro Dom 0.565
3 Electron 0.35
4 Solo 0.345
5 Visa Delta 0.37
6 Mastercard 0.195
7 Visa 0.195
8 Amex 0.266

I want to calculate sum(ProductSale.UnitCost) where PaymentMethod is NOT
equal to Cash or Amex. I have mocked this up and it seems to work:


SELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales,
Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS StreamlineCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=2 Or (Checkout.PaymentMethodID)=3 Or
(Checkout.PaymentMethodID)=4 Or (Checkout.PaymentMethodID)=5 Or
(Checkout.PaymentMethodID)=6 Or (Checkout.PaymentMethodID)=7))
GROUP BY Checkout.CheckoutDate ORDER BY Checkout.CheckoutDate DESC;


I also want to calculate sum(ProductSale.UnitCost) where PaymentMethod=Amex.
Again, I have mocked this up and it seems to work:

SELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales,
Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS AmexCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON
Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID =
Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=8))
GROUP BY Checkout.CheckoutDate
ORDER BY Checkout.CheckoutDate DESC;

These queries only return results where the ValueOfSales and NumberOfSales
is not zero... i would like them to return results for each day even if the
result is zero...

Also, I want the results from both queries as one result set i.e. I want the
results to look like:

Checkoutdate ValueOfSales NumberOfSales StreamlineCost AmexCost
2008.01.27 200.00 5
14.95 0.00
2008.01.28 235.64 7
15.98 1.45
...

I want two extra columns to be displayed ... even though their values might
be zero for a particular day.

Any help appreciated...

Thanks
Click to expand...
Click to expand...
 
Top