Calculating sales statistics (2000(SP3))

Topic

New Reply

I am working on an inventory and Point of Sale database for a small art gallery and one of the reports they need is monthly sales statistics based on various price points: <$10, $10.01 – 25, $25.01 – $50, etc. I did various searches and can't find any threads on this in the Lounge.

I assume I need to do some sort of crosstab for this, but I'm not sure how to go about it. The selling price for each item is recorded in a SalesDetails table. I'll happily supply any other info you need.

Reply | Quote

Replies

You could build a temporary table with the appropriate rows as well as the amount and an amount column. The amount column could be a number where 1 – < $10, 2 – $10.01 to $25, etc.
You could then base a crosstab query on this temporary table.

Reply | Quote

I’m not sure I’m following you. Could you expand on this a bit?

How do I link this to my actual sales data?

Reply | Quote

Presume that the temporary table has the following fields:
RowField1, eg Customer
RowField2, can be anything
AmountType, 1 = <$10, 2 = $10.01 to $25, 3= $25.01 to $50
Amount

The AmountType would be calculated as above, this would be a VBA code segment.

Then create the crosstab query based upon the temporary table with the AmountType as the Column header and summing on the amount. Then base the report on the crosstab query.

Reply | Quote

You can create a crosstab query without using a temporary table. The Switch function lets you divide the sales amount into categories.

See the attached example. I set the Column Headings property of the crosstab query to make the columns appear in the desired order.

Reply | Quote

Thanks to both of you. I knew I had seen something like this before. This is the info I needed.

You get at least 2 beers for all the time you’ve saved me researching this one.

Reply | Quote

Okay, you can upload them to my PC. I’ll tell my firewall to open the Beer Port.

Reply | Quote