Crosstab Query the best option?

[Chopper83150]

I have been struggling with this report issue for awhile. I would appreciate any thoughts/insights.

I have a table called invoices, which contains the following fields
INVOICEID
GUESTID
BOOKINGSID
InvoiceDate
RoomNo
PRODUCTID - contains rooms/massage/taxi/extrabed/ (lookup)
Amount
PaymentType - contains; cash / credit cards/ bank transfer (lookup)
Shift

I need to create a report the the following format
ROOMS | MASSAGE
CAsh |Credit Card | Bank Transfer| CAsh |Credit Card | BankTransfer
01/11/13 $20 $200
02/11/13 $300
03/11/13
SO the date down the left column, the products along the top with the payment types under the products.

I hope I have explained this properly, please forgive my "newbieness"

I have created a crosstab query that will give me what i want but for only one product. the sql for that query is as follows:

TRANSFORM Sum(Invoices.Amount) AS SumOfAmount
SELECT Invoices.InvoiceDate
FROM PaymentTypes INNER JOIN Invoices ON PaymentTypes.ID = Invoices.[PaymentType]
WHERE (((Invoices.PRODUCTID)=6))
GROUP BY Invoices.InvoiceDate
PIVOT PaymentTypes.PaymentTypes;

I dont know my next step or how to get / link all the other productIds together in one report. There are a total of 7 products.

Any help is greatly appreciated

Regards,

 

[JHB]

Remove the Where clause or use "Or" between the different Invoices.PRODUCTID.

WHERE Invoices.PRODUCTID=6 Or Invoices.PRODUCTID=AnOtherNumber

 

[Chopper83150]

Thanks for the response.
With that query i have the data that I want but i don't know how to get that data into a report with the correct formatting

When I run my query;

TRANSFORM Sum(Invoices.Amount) AS SumOfAmount
SELECT Invoices.InvoiceDate, Invoices.PRODUCTID
FROM PaymentTypes INNER JOIN Invoices ON PaymentTypes.ID = Invoices.PaymentType
GROUP BY Invoices.InvoiceDate, Invoices.PRODUCTID
PIVOT PaymentTypes.PaymentTypes;

I get all the info i want but there are multiple records for the same date.
Same applies when I transpose this to my report.
i was using;

=IIf([ProductId]=6,[Cash],"0")

TO show cash payments for product 6 and

=IIf([ProductId]=5,[cash],"0")

TO show cash payments for product 5.

Problem is in the report they are on different lines based on the date.
I am very new to this and its really doing my head in

Thanks in advance

 

[JHB]

Can you show what you get and how you want it, (print screen or Excel sheet)?

 

[Chopper83150]

This is what I get

 

[Chopper83150]

This is what I want

I hope that helps.. THanks Again

 

[JHB]

It is the GROUP BY on the Invoices.PRODUCTID that give you the problem, so remove it, also in the SELECT.

TRANSFORM Sum(Invoices.Amount) AS SumOfAmount
SELECT Invoices.InvoiceDate 
FROM PaymentTypes INNER JOIN Invoices ON PaymentTypes.ID = Invoices.PaymentType
GROUP BY Invoices.InvoiceDate 
PIVOT PaymentTypes.PaymentTypes;

Else post a stripped version of you database with some sample data, (zip it).

 

[Chopper83150]

Okay, but if I remove the PRODUCTID then the query just returns the total sales for the date broken down into CASH, CREDIT CARD, BANK TRANSFER.

I need to get those three totals (CASH, CREDIT CARD, BANK TRANSFER) for rooms, laundry, Taxi etc for each date

 

[Chopper83150]

View attachment NEW_2013-11-07.zip

 

[JHB]

This is what I get View attachment 50960

Do you get that???
What query in you attached database do you run then, because I can't find it?

 

[Chopper83150]

Oops, very sorry, try this one, check the report please, that is the layout I need but as you can see I cant get it to show all the data on one line per date range..

I dont even know if this is possible

regards, Chopper

View attachment NEW_2013-11-09.zip