Query Grouped by Day of Week

[ibgreat]

I am trying to create a 3-Dimensional Chart (i.e. Bubbe) where my x-axis would be the day of the week, the y-axis would be time intervals and the data point would represent count.

The results would look something like:

5:00..0 0 0
5:30..0 0 0
6:00..0 2 1
6:30..0 1 0
etc
.......M T W etc

My table info is: tblBxData (BxDate, BxStartTime)

I am somewhat new to Access and not very familiar with the date/time functions. I believe nesting a GROUP BY statement would do this, but I am at a bit of a loss on how to define the time and date statements. I have looked around a bit and even posted elsewhere but haven't found anything similar. Any direction and resources to help me understand this are appreciated.

[CreganTur]

Take a look at the CrossTab query. It's one of the options when you click New on the Queries object section of the database window. You can use it to create a query exactly like you're wanting.

[OBP]

You can get the day of the week for the date using this in a new Column heading
day: Format([AppointmentDate],"ddd")
My example is using the Date field called "AppointmentDate" to do this.
The 3 dimesnional format can be achieved in 2 ways, the first is to use a Crosstab Query, which can be created using a Crosstab Query Wizard selected from the Query Tab's Menu "New".
The second method involves using a Pivot Table Form which can be selected from the Form tab's "New".

[OBP]

Sorry Randy, I didn't see you post I was busy typing.

[CreganTur]

Sorry Randy, I didn't see you post I was busy typing.

No worries; not like it's a compitition or anything. Plus it was a good idea to show him the Format function :thumb

[ibgreat]

Thanks once again to both of you, I had started to play with pivot table/charts but, was having problems with grouping by the day of the week.

Now that I know I was on the right track I will try to play with that and the cross tab queries a bit more tonight.

I haven't used these a whole lot in the past, so I am sure I will need a bit more direction. I don't fully grasp the sql syntax or the pivot view on the cross tab queries and pivot tables these. I'm sure a bit more playtime in the sandbox will help. I will post again tomorrow. For now I am off to a meeting.

Thanks!!!!

[ibgreat]

You can get the day of the week for the date using this in a new Column heading
day: Format([AppointmentDate],"ddd")
My example is using the Date field called "AppointmentDate" to do this.
The 3 dimesnional format can be achieved in 2 ways, the first is to use a Crosstab Query, which can be created using a Crosstab Query Wizard selected from the Query Tab's Menu "New".
The second method involves using a Pivot Table Form which can be selected from the Form tab's "New".

So here is where I am at....
I was able to use the Crosstab Query to get the dataset to show the data based on the day of the week in the columns.

Here is the query...

TRANSFORM Count(tblClientBx_D.BxIncID) AS CountOfBxIncID
SELECT tblClientBx_D.PersonID_Client, tblClientBx_D.BxTimeStart, Count(tblClientBx_D.BxIncID) AS [Total Of BxIncID]
FROM tblClientBx_D
GROUP BY tblClientBx_D.PersonID_Client, tblClientBx_D.BxTimeStart
PIVOT Format([BxDate],"ddd");

I think the next step is to get it to group by say 15 minute intervals. I thought about using the round function, but using this with times does not look like it would work without a seperate functino. Alternatively, I think what I want is . Right now (and with the rounding method) there is no data versus a value of zero in the appropriate group. On a pivotchart, I think I could adjust the axis versus grouping.

I initially thought once I had the query built I could reference it in a chart. The problem with this is that the chart will only take six variabes and each day of the week is different field. I started to play with the rowsource but without understanding how to get the y-axis to represent time of day I'm hitting a wall.

Again, any suggestions are greatly appreciated.

[OBP]

I have 2 initial reactions to what you want to do.
1. Export the Query to Excel and graph it there.
2. Build a Temporary Table holding both the Day of the week and the 1/4 hour rounded times and then Pivot Table that.

Of course you could build the Table and Export that to Excel to Graph.

[ibgreat]

ODB,

Thanks for the response! I still don't understand how to get the sums within 15 minute increments in the query. I know the charting functions are difficult to deal with in Access. But I actually think I can get the graph to look how I want when I have the data in the correct format. Staying within Access would obviously be easier for other users as well.

To build and populate a tempory table each time the user wants to view the data in a graphic representation seems like alot of system resources. If I am off base here in comparison to query let me know.

I think the ultimately I need to get the query correct first and I am not there yet.

Regards!

[OBP]

I will try and post something tomorrow, I had my grandson here today.

[ibgreat]

Very cool, hopefully you enjoyed yourself....I look forward to your thoughts.

[ibgreat]

Okay so in working with a friend we figured out where some of the problems were. We didn't need a crosstab query. Here is a simplified query that represents what the data need to look like.


SELECT Format([BxDate],"ddd") AS Day, tblClientBx_D.BxTimeStart
FROM tblClientBx_D

The only problem I am having now is that I need to convert the Day (which is the day of the week) to a representative integer (not the date number).

I know I could loop the data through VBA, but I would think there is an easier way to do this within the query.

For instance:
Mon would convert to 1,
Tue would convert to 2,
Wed would conver to 3

etc.

Unfortunately, Scatterplots only allow numberic values on the x-axis.:doh:

Once I get this I will need to alter properties of the MS Graph object that don't appear to be readily available in Access. I believe I had read are available via VBA. Any suggestions on resources for this? Specifically, I will be looking at the axes values. I will keep looking in the interim.

Regards.

[OBP]

Did you manage to get the 15 minute intervals?
To get the day number of the date use the weekday function like this
day: Weekday([Date Lead Recieved:],2)

[ibgreat]

Perfect! I hadn't found this function when I was looking, many thanks.:clap:


Did you manage to get the 15 minute intervals?


Yep, here is the code. This particular code averages the time to the nearest hour increment. By adding the 0.5 it bumps the value into to the correct "rounded" value.


CVDate(Int([BxTimeStart]*24+0.5)/24)

The 24 references the number of increments daily. So 15-minue intervales would be:


CVDate(Int([BxTimeStart]*96+0.5)/96).

ODB, as always I appreciate the time you spent looking at this with me.