How do I create a query for... 1

[Aximboy]

I need to create a report that shows the monthly total per column.
For example, these are the records from a table:

ID Payment Date Payment Method Coupon Mailed Date Responded Turnaround Time
1 02/01/2014 Cash 03/01/2014 05/12/2014 73
2 02/15/2014 Credit 02/25/2014 03/01/2014 5
3 03/25/2014 Credit 04/01/2014 05/01/2014 31
4 03/25/2014 Credit 05/15/2014 05/25/2014 10
5 04/10/2014 Cash 04/25/2014 05/10/2014 16


I want the query to show something like this:

Number of Number of Number of Coupon Date Average
Month Payments Cash Payment Credit Payment Mailed Responded Turnaround Time
February 2 1 1 1 39
March 2 2 1 1 21
April 1 1 1 16
May 1 4


Please help.
How do I create this query?

 

[dhookom]

Please re-try your post using TGML (try the Pre tag) so we can make heads or tails of your field names that contain spaces (I assume) and your data. Then use the Preview button to make sure your post is legible.

For instance this doesn't take too long to create:

[pre]
ID [Payment Date] [Payment Method] [Coupon Mailed] [Date Responded] [Turnaround Time]
1 02/01/2014 Cash 03/01/2014 05/12/2014 73
2 02/15/2014 Credit 02/25/2014 03/01/2014 5
3 03/25/2014 Credit 04/01/2014 05/01/2014 31
4 03/25/2014 Credit 05/15/2014 05/25/2014 10
5 04/10/2014 Cash 04/25/2014 05/10/2014 16
[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

Thank you for the tip on posting.


I need to create a report that shows the monthly total per column.
For example, these are the records from a table:

[pre]
ID [Payment Date] [Payment Method] [Coupon Mailed] [Date Responded] [Turnaround Time]
1 02/01/2014 Cash 03/01/2014 05/12/2014 73
2 02/15/2014 Credit 02/25/2014 03/01/2014 5
3 03/25/2014 Credit 04/01/2014 05/01/2014 31
4 03/25/2014 Credit 05/15/2014 05/25/2014 10
5 04/10/2014 Cash 04/25/2014 05/10/2014 16
[/pre]

I want the query to show something like this:
[pre]


[Month] [Number of [Number of [Number of [Coupon [Date [Average
Payments] Cash Payment] Credit Payment] Mailed] Responded] Turnaround Time]
February 2 1 1 1 39
March 2 2 1 1 21
April 1 1 2 16
May 1 4
[/pre]

Please help.
How do I create this query?

 

[dhookom]

Start with a normalizing union query to make your columns into rows:

SELECT ID, "Payment Date" as Transaction, [Payment Date] as TheDate, [Payment Method], [Turnaround Time]
FROM ttAximboy
UNION ALL
SELECT ID, "Coupon Mailed", [Coupon Mailed], NULL, NULL
FROM ttAximboy
UNION ALL
SELECT ID, "Date Responded", [Date Responded], NULL, NULL
FROM ttAximboy;

Then create a crosstab query based on the ttuniAximboy union query:

TRANSFORM Count(ttuniAximboy.ID) AS CountOfID
SELECT Format([TheDate],"mmmm") AS TheMonth, Avg(ttuniAximboy.[Turnaround Time]) AS [AvgOfTurnaround Time], 
Sum(Abs([Payment Method]="Cash")) AS CashPayments, Sum(Abs([Payment Method]="Credit")) AS CreditPayments
FROM ttuniAximboy
GROUP BY Format([TheDate],"mmmm")
PIVOT ttuniAximboy.Transaction;

You should end up with

[pre]
[TheMonth] [AvgOfTurnaround Time] [CashPayments] [CreditPayments] [Coupon Mailed] [Date Responded] [Payment Date]
April 16 1 0 2 1
February 39 1 1 1 2
March 20.5 0 2 1 1 2
May 1 4
[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

Thank you so much dhookom for helping me.

However, I am getting “Data type mismatch in criteria expression” error.
I am suspecting it is the [Turnaround Time] field because it is the difference of [Coupon Mailed] minus [Date Responded] fields.
Any suggestion?

 

[dhookom]

Does your table actually have a [Turnaround Time] field? If so, what is its type?
What happens if you remove the field from all queries?

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

The [Turnaround Time] field is a calculation of [Coupon Mailed] minus [Date Responded] fields. Both the [Coupon Mailed] & [Date Responded] fields are in date format.

If I remove the [Turnaround Time] field, I do not get any error and the result is perfect.

 

[Aximboy]

Ok, so after more trial and error, I think the culprit is the [Date Responded] field because some of the records have a NULL [Date Responded] data which also affects the calculation in the [Turnaround Time] field.
My new question is, is there a work around the NULL data in the [Date Responded] field?

 

[dhookom]

You could try remove the null records from the union query by changing the last part to:

UNION ALL
SELECT ID, "Date Responded", [Date Responded], NULL, NULL
FROM ttAximboy
WHERE [Date Responded] Is Not Null;

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

OMG it worked!!! Thank you so much dhookom!!! You're the best!!!

 

[Andrzejek]

Aximboy, you have been a member of TT for over 10 years and I hope you got some good use and help out of TT. But you have never given any stars to people who help you.

This way you can show appreciation for help received, and help other TT users to see the posts / answers which address the issue at hand.

On dhookom’s post, click on
[blue]
Like this post?
Star it![/blue]



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Aximboy]

Thank you Andrzejek, I never noticed that feature before.
Now that you mentioned it, I owe dhookom big time!!!