Access 2003 Calls per hour 1

[wwgmr]

Hi everyone hope things are going good. Hope more that you can help me lol I posted this under MS Office but I think maybe I should have posted here.

Here is my problem.
I'm good in excel but access I just seem to fight with.

I have Excel sheet with list of calls we take over 31000 rows which is why I feel access should be beter to work with. I have brought it into access 2003 and have full table.
I just want to count number of calls that come in during an hour per day. I have column that is Created date

I wanted to setup report or query- then report? To group first by day of week then by hour so it would look like this or close to
Day Hour Total Calls
Sat
8am 3
9am 5
extra till
Mon
8 am 0

Doesn't have to start at 8am just using as example.
Is this possible? I'm working with 1 months worth of data. Trying to look at each day of the week and see what is average calls I get during that part of day.

Thank you all, I'm sure anyone works with access will find easy to do but I seem to have mental block when it comes to this program lol

 

[BoxHead]

I assume your date field includes the time and displays as
10/14/2005 3:27:22 PM.

The following should get you started. Replace 'tblTime' with your table name and 'myDate' with the name of your date field.

SELECT Format([myDate],"mm/dd/yyyy") AS Expr1, Format([mydate],"ampm") AS Expr2, Format([mydate],"ddd") AS Expr3, Format([mydate],"h:""00 ""ampm") AS Expr4, Count(Format([mydate],"hh:""00 ""ampm")) AS Expr5
FROM tblTime
GROUP BY Format([myDate],"mm/dd/yyyy"), Format([mydate],"ampm"), Format([mydate],"ddd"), Format([mydate],"h:""00 ""ampm")
ORDER BY Format([myDate],"mm/dd/yyyy"), Format([mydate],"ampm"), Format([mydate],"h:""00 ""ampm");

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[wwgmr]

Thanks for quick reply, where would I put that in macro or build expression in Report detail? Thanks!

 

[BoxHead]

Go to the query tab of the database window and double-click on Create Query in Design view. Click Close on the Add Table window. Right-click on the empty grey area above the query grid and select SQL View. Delete the word SELECT and the semi-colon. Paste in the text from my earlier post and then replace the table and field names as instructed.

From the View menu, select Datasheet View.

That should show you The dates, the hours and the count of calls (records) per hour.

Back at the View menu, select Design View. That will show you the table fields in the top half and a group of columns in the bottom half. Drag the fields into the query to get the information you need for your report and save the query.

Base the report on the query, not the table.

If this is all new to you, I'd strongly suggest that you look through the help files on building queries. This is the sort of report on which companies make satffing and spending decisions.

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[dhookom]

wwgmr,
It would be appropriate if you shared your field and table names. Also, do you want to combine/count all the Monday 10:00 AMs together? Are the date and time values both stored in one or two date/time fields?

It should be fairly straight-forward to create a query that groups by day of week and hour. You can find several date/time functions by opening a module and searching Help.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wwgmr]

Thank you very much that worked great, yes I do want to group by days of the week as well but in query design I should be able to get it to do such. It was format command I think I was missing where I was just doing it straight. But this works and I have got over a mental block I've been having with access one step. The help files MS offers are great, has caught me up to speed rather well with access, Just I always end up going back to do it in Excel which I feel more comfy with. Lol

But this time I decided I need to learn and will do it step by step with access Thanks again for help (I maybe back again if I can't work out the days but I have been improving )


wwgmr