Report layout problem related to summing amounts by Date, payment type and product

[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/extra bed/Laundry (Via a lookup)
Amount
PaymentType - contains; cash / credit cards/ bank transfer (Via a lookup)
Shift Morning/Afternoon (Via a lookup)

I need to create a report in the following format;
The date down the First column, the products along the top with the payment types under the products.
For example along the top of the report the word ROOM, spanning 3 columns. Column 1 heading is CASH, Column 2 heading is CREDIT CARD and Column 3 heading is Bank Transfer, then next to ROOM is the word LAUNDRY, spanning 3 columns. Column 1 heading is CASH, Column 2 heading is CREDIT CARD and Column 3 heading is Bank Transfer. ETC

Basically the idea of the report is to summarize the daily sales for each product, separated into payment types

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

I have created a crosstab query that will give me the data that i want but i dont know how to show the data in the report. I only want one row per date but i am getting multiple rows

My crosstab query is;

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 dont know my next step or how to get / link all the productIds together in one report. There are a total of 5 products.
Once this is done my DB is almost finished
PLease find attached a stripped down version of the DB

NEW_2013-11-09.zip

Any help is greatly appreciated

Regards,
Chopper

[Rainlover]

I downloaded your database but it is no good to me because like many others, I have made the decision to stick with the more stable 2003. You could convert your to 2003 if you have problems with the answer.

Basically your problem is in the way you have your tables set out and the relationships they have.

EG One Table stores just one record for the customer details. Another table has the dates etc of the many purchases made.

The report can be broken down in a similar way. Place the customer details in the header, and the purchases in the detail of the report.

Hope this is enough to get you started.

The last thing you will need is a crosstab query.

[June7]

I see 8 products in the Products table. You have a report that displays 3 products using a method that emulates a crosstab. Why don't you expand it for as many products as you want? If you intend to print, will need very wide paper. Otherwise, use conventional report Grouping & Sorting with aggregate calcs in footers.

Review http://allenbrowne.com/ser-67.html

What is purpose of ABF table with single field DayNumber? Why does it have 45 records?

There is a circular relationship between Booking, Invoices, Guests tables. This can cause issues. GUESTID should probably not be in Invoices. http://www.codeproject.com/Articles/...atabase-Design

Rainlover, converted database from 2003 to 2007 4 years ago. Runs stable enough. The ribbon takes a little getting used to but some new features in 2007 are nice to have. Of course, if you have a lot of customization built into the mdb, might not carry over nicely to 2007. Also, 2007 does drop the security features offered by 2003.

[chopper83150]

I downloaded your database but it is no good to me because like many others, I have made the decision to stick with the more stable 2003. You could convert your to 2003 if you have problems with the answer.

Basically your problem is in the way you have your tables set out and the relationships they have.

EG One Table stores just one record for the customer details. Another table has the dates etc of the many purchases made.

The report can be broken down in a similar way. Place the customer details in the header, and the purchases in the detail of the report.

Hope this is enough to get you started.

The last thing you will need is a crosstab query.

The customers are irrelevant to the report I am trying to create .. I am trying to create a summary of sales, I dont care who bought what, I only care what was bought and how it was paid for. Thanks anyway

[chopper83150]

The report only has 3 products because there is no ponit creating the entire report when I cant get the report to display what I want for even the 3 products shown.
ABF is irrelevant to this post.
Circular relationship fixed, guestid in invoices removed, thanks for the heads up.

Thank you for your reply but my original question remains:

"I need to create a report in the following format;
The date down the First column, the products along the top with the payment types under the products.
For example along the top of the report the word ROOM, spanning 3 columns. Column 1 heading is CASH, Column 2 heading is CREDIT CARD and Column 3 heading is Bank Transfer, then next to ROOM is the word LAUNDRY, spanning 3 columns. Column 1 heading is CASH, Column 2 heading is CREDIT CARD and Column 3 heading is Bank Transfer. ETC

Basically the idea of the report is to summarize the daily sales for each product, separated into payment types"

REgards,

Chopper

[June7]

The report in the posted database has that output. However, since there is apparently more than one record for each date, could do aggregate calcs in query:

SELECT InvoiceDate, Sum(IIf([Productid]=6,[Cash],0)) AS RoomCash FROM Invoices GROUP BY InvoiceDate;

Do similar calcs for the other products and payments. Make that query the RecordSource of the report.

The problem with basing a report on denormalized data is if you add products have to modify query and report design.

[chopper83150]

Thanks for your help,
sorry to be a pain but I have created a query using;

SELECT InvoiceDate, Sum(IIf([Productid]=6,[Cash],0)) AS RoomCash FROM Invoices GROUP BY InvoiceDate;

when i try to run it I get a parameter popup asking to input a parameter for Cash.
Have I misunderstood your instructions?

BTW It is a good point you raised however it is unlikely any additional products will be added and I really dont know what Im doing
So I guess I'm just muddling around try ito build something that works.
Any suggestions you have will be greatly appreciated.

[June7]

Sorry, should have been: FROM MONTHLYSALES

Actually, the intermediate crosstab is not need:

SELECT Invoices.InvoiceDate, Sum(IIf([Productid]=6 And [PaymentType]=1,[Amount],0)) AS RoomCash
FROM Invoices
GROUP BY Invoices.InvoiceDate;

Or:
TRANSFORM Sum(Invoices.Amount) AS SumOfAmount
SELECT Invoices.InvoiceDate
FROM (PaymentTypes INNER JOIN Invoices ON PaymentTypes.ID = Invoices.PaymentType) LEFT JOIN Products ON Invoices.PRODUCTID = Products.PRODUCTID
GROUP BY Invoices.InvoiceDate
PIVOT [Category] & [PaymentTypes];

Problem with this crosstab is that if a Category & PaymentType combination has no data then a field will not be generated. Review the 'Specify Column Headings' section in the Allen Browne link provided earlier.

ProductID in Products needs to be set as primary key.

Why do you have field named InvoiceDate but set caption of Date Paid in the table?
And ProductID in Products has caption of Invoice.
This just causes confusion.
Also, you have lookups with alias set in table. More confusion. http://access.mvps.org/access/lookupfields.htm
I NEVER bother setting these properties in table.

[Rainlover]

The customers are irrelevant to the report I am trying to create .. I am trying to create a summary of sales, I dont care who bought what, I only care what was bought and how it was paid for. Thanks anyway

You should be able to swap Customers for Products and give you the result you want.

Shame I don't have a copy of the Database but it sure does from what I have read, look like the design of the tables is all wrong.

The use of a crosstab query is the biggest clew.

June should be able to help so I wish you well.

[chopper83150]

Thank You June for your help, I am very grateful. The layout and the setup of my DB is the way it is simply because I have no idea what I am doing. This is my first DB.
I appreciate yours and rainlovers input and suggestions and will try to address the issues in my design. Thanks again

[Rainlover]

Thank You June for your help, I am very grateful. The layout and the setup of my DB is the way it is simply because I have no idea what I am doing. This is my first DB.
I appreciate yours and rainlovers input and suggestions and will try to address the issues in my design. Thanks again

More than happy to help if you need it. Just convert your Database to 2003 and I will rearrange as required your tables and explain why so that you will know next time.

If you prefer to just drop it then I wish you well.

[chopper83150]

More than happy to help if you need it. Just convert your Database to 2003 and I will rearrange as required your tables and explain why so that you will know next time.

If you prefer to just drop it then I wish you well.

I would love your help, I tried to save my DB as 2002-2003 but apparently it is not compatible

[Rainlover]

Try deleting all the Code behind the Forms.

If that does not work then delete everything except the Tables and any Queries that you may have.

It is Midnight here so I may not do anything till tomorrow, but if I have the Tables at least then that would be good.

[chopper83150]

Thanks again for the offer, I deleted everything but my table and queries and it still would not let me save as a 2003 DB??

[Rainlover]

Attach what you have and I will get a friend to convert/fix it for me.

Another idea is to export your tables to a new blank database and then convert that.

I hope not but it looks like you have a corrupt database. Don't worry though because we can fix that.