Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charting sales by hour (2002 - XP)

    Hello,

    I have a database that puts a date and time stamp on each sale. I would like to create a chart that will show the average $ sales for each hour for a year. I can figure out how to get the query to sum the sales for each hour.

    TIA
    Bret

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting sales by hour (2002 - XP)

    Ooops, you are right I would be looking at thousands of points of data. The average would be correct. I just want something that will visually demostrate business volume. I think it might help in our scheduling of staff.

    I am totally unaware of how to create this calculated field. I would need to create one for each our that we do business wouldn't I?

    Bret

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting sales by hour (2002 - XP)

    Do you mean that you want a chart with 365 x 24 data points? That would be a problem, I think MS Chart can only handle 4096 data points.
    Or do you want to average the sales between 12 PM and 1 AM over the days in the year, and the sales between 1 AM and 2 AM etc.? In that case, create a query based on the data table, and add a calculated column Hr: Hour([TimeField]) where TimeField is the field containing the time stamp. Use the calculated field Hr in the chart.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting sales by hour (2002 - XP)

    You don't have to create a separate field for each hour. Create a query based on the data table, enter

    Hr: Hour([TimeField])

    in the Field: row in the first column, substituting the name of your timestamp field for TimeField, then add the sales field. Save this query. Then switch to the forms or reports tab of the database window, depending on whether you want to create the chart in a form or in a report. Click New, select Chart Wizard and the query you just created, and click OK. Add both fields to the list of fields for the chart, select a chart type, drag Hr to the axis and the sales field to the Data box. It will probably read SumOfSales or something like that. Double click it, and select Average instead of Sum. Then continue and finish.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting sales by hour (2002 - XP)

    This works great Hans but, it returns 110,126 total points. How do I get it to combine the hours 9 am to 10 am, 11 am to 12 pm, etc.?

    This is where I thought I might have to do this for each hour with something in the criteria field like, Between 9:00 am And 9:59:59 am.

    Bret

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting sales by hour (2002 - XP)

    I guess I was a little hasty in my last response. I didn't tell you about the report. For whatever reason the chart give totals by month. Here is a copy of the query.

    SELECT ([DateTime]) AS Hr, Avg(Invoice_Totals.Total_Price) AS AvgOfTotal_Price
    FROM Invoice_Totals
    GROUP BY ([DateTime])
    HAVING (((Avg(Invoice_Totals.Total_Price))>0));

    It seems to give me what I expected, but when I use the chart wizard as I said it give me monthly totals.

    Bret

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting sales by hour (2002 - XP)

    Try

    SELECT Hour([DateTime]) AS Hr, Avg(Invoice_Totals.Total_Price) AS AvgOfTotal_Price
    FROM Invoice_Totals
    GROUP BY Hour([DateTime])
    HAVING (((Avg(Invoice_Totals.Total_Price))>0));

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting sales by hour (2002 - XP)

    That returns only 17 points but it shows them all as 12:00:00 am.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting sales by hour (2002 - XP)

    Could you post a stripped down version of your table and query?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem. In this case, leave only the data table and the query.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

Posting Permissions

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