1. Calculating sales statistics (2000(SP3))

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.

2. Re: Calculating sales statistics (2000(SP3))

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.

3. Re: Calculating sales statistics (2000(SP3))

I'm not sure I'm following you. Could you expand on this a bit? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

How do I link this to my actual sales data?

4. Re: Calculating sales statistics (2000(SP3))

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.

5. Re: Calculating sales statistics (2000(SP3))

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.

6. Re: Calculating sales statistics (2000(SP3))

Thanks to both of you. I knew I had seen something like this before. This is the info I needed.
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

7. Re: Calculating sales statistics (2000(SP3))

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•