Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •