Chopper83150
New member
- Local time
- Today, 14:49
- Joined
- Nov 5, 2013
- Messages
- 9
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,
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,