Problem Joining Tables In A Query

[Brian]

I got some pointers yesterday from a menmber but haven't yet solved my
problem. I have a monthly expenses and a monthly sales report. I am trying
to combine them into one for comparative purposes. The problem is with the
dates. The "Sales Table" has a date field as does the separate "Exenses"
Table. Obviously the comparative reports need to total the sales and expense
for the same months. I am a bit too far down th line for a major re-write.

The advice I got yersterday was to create a third table (which I called
"AllDates" and imported from Execel a 10-year range of dates. My new query
joins the the two table's dates field to the 3rd table from which the date is
put in the query field but will not display all dates. Fooling around with
1-2-3 join options I can get a datasheet with all table 1 or all table 2
dates but not both.

Any one got an answer? Forgive the impertinence but if it requires a
complex WHERE statement could you spell it out for me.

Here's Hoping

 

[Ken Sheridan]

To show all rows from both tables you'd have to use LEFT OUTER JOINs between
the AllDates table and each of the Sales and Expenses tables. This would be
fine if there were the same number of rows per date in each, but if there
were two rows for one date in one and only one in the other then the single
row from the second table would be returned twice so the amount would be
added twice to the total.

A better way would be a UNION query like so:

PARAMETERS
[Start Date:] DATE TIME, [End Date:] DATETIME;
SELECT "Sales" AS TransactionType, TransactionDate, Amount
FROM SALES
WHERE TransactionDate BETWEN
[Start Date:] AND [End Date:]
UNION
SELECT "Expenses", TransactionDate, Amount
FROM Expenses
WHERE WHERE TransactionDate BETWEEN
[Start Date:] AND [End Date:];

This will give you a single result table with both sales and expenses, the
TransactionType column differentiating between the two.

In a report you can group on the TransactionType column, and sum the Amount
column in the group footer to give you total sales and expenses for each. In
the report footer you could show the difference between Sales and Expenses in
a text box with a ControlSource of (all as one line):

=Sum([Amount]*IIf([TransactionType]="Sales",1,0)-[Amount]*IIf([TransactionType]="Expenses",1,0))

If you want to cover more than one month in the repirt include the following
computed columns in each half of the UNION query:

YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth

and group the report firstly by TransactionYear then by TransactionMonth,
giving the latter a group header and footer. You'll then be able to include
the 'comparative' text box also in the month group footer as well as in the
report footer to give the differences per month.

Ken Sheridan
Stafford, England

 

[Brian]

Thank you for the very detailed comments. I have printed them out and I will
attack this with a fresh vigour tomorrow morning. I supriised myself by
actually understanding your argument so the solution may well be at hand.
Cheers!

To show all rows from both tables you'd have to use LEFT OUTER JOINs between
the AllDates table and each of the Sales and Expenses tables. This would be
fine if there were the same number of rows per date in each, but if there
were two rows for one date in one and only one in the other then the single
row from the second table would be returned twice so the amount would be
added twice to the total.

A better way would be a UNION query like so:

PARAMETERS
[Start Date:] DATE TIME, [End Date:] DATETIME;
SELECT "Sales" AS TransactionType, TransactionDate, Amount
FROM SALES
WHERE TransactionDate BETWEN
[Start Date:] AND [End Date:]
UNION
SELECT "Expenses", TransactionDate, Amount
FROM Expenses
WHERE WHERE TransactionDate BETWEEN
[Start Date:] AND [End Date:];

This will give you a single result table with both sales and expenses, the
TransactionType column differentiating between the two.

In a report you can group on the TransactionType column, and sum the Amount
column in the group footer to give you total sales and expenses for each. In
the report footer you could show the difference between Sales and Expenses in
a text box with a ControlSource of (all as one line):

=Sum([Amount]*IIf([TransactionType]="Sales",1,0)-[Amount]*IIf([TransactionType]="Expenses",1,0))

If you want to cover more than one month in the repirt include the following
computed columns in each half of the UNION query:

YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth

and group the report firstly by TransactionYear then by TransactionMonth,
giving the latter a group header and footer. You'll then be able to include
the 'comparative' text box also in the month group footer as well as in the
report footer to give the differences per month.

Ken Sheridan
Stafford, England

I got some pointers yesterday from a menmber but haven't yet solved my
problem. I have a monthly expenses and a monthly sales report. I am trying
to combine them into one for comparative purposes. The problem is with the
dates. The "Sales Table" has a date field as does the separate "Exenses"
Table. Obviously the comparative reports need to total the sales and expense
for the same months. I am a bit too far down th line for a major re-write.

The advice I got yersterday was to create a third table (which I called
"AllDates" and imported from Execel a 10-year range of dates. My new query
joins the the two table's dates field to the 3rd table from which the date is
put in the query field but will not display all dates. Fooling around with
1-2-3 join options I can get a datasheet with all table 1 or all table 2
dates but not both.

Any one got an answer? Forgive the impertinence but if it requires a
complex WHERE statement could you spell it out for me.

Here's Hoping