Query help-breaking down data according to another field

[msk7777]

I will do my best here to explain myself, this is my first post in this forum so be sure to tell me if I am not following procedure. I have a query that pulls data from an ODBC link. It pulls all calls in according to the date I enter (parameter: >=DateAdd("h",7,[Start Date])And<=("h",19,[End Date])). This works great, I get all records of calls abandoned on that date between the hours of 7am to 7pm grouped by phone line. I have fmany fields but the ones in question really are Timestamp(date/time), Application(name of phone lines), and CallsAbandoned (number of calls abandoned for that particular phone line). I now need to add to this query. I need to add additional columns breaking down the number of calls abandoned between certain time frames (example: 7-10AM, 10-1PM, 1-3PM,3-5PM, and 5-7PM).
Basically what I want is it to be grouped by the Phone Line name (Application) so it shows the total calls abandoned (which I already have it doing) and then in the next columns a breakdown of how many of those calls were abandoned in between the above-stated time lines. Can anyone help direct me in this? Thanks in advance!

msk7777

 

[SoCalAccessPro]

Use a crosstab query.

Format you call time so that it falls in a specific range. You'll need some logic or a function to do this.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[msk7777]

Thanks for the advice! Can you give me an example of what you mean by "format your call time so that it falls in a specific range"?

 

[dhookom]

This [tt][blue]>=DateAdd("h",7,[Start Date])And<=("h",19,[End Date])[/blue][/tt] should not work as posted.

If you want to split your times into ranges, I would use a small lookup table of ranges.
[tt][blue]
tblTimeRanges
===============
TimeFrom
TimeTo
RangeName
[/blue][/tt]

You can then add this table to your query and set the criteria to something like:
[tt][blue]
TimeValue(Timestamp) Between TimeFrom and TimeTo
[/blue][/tt]

If you want your ranges as columns, you would use a crosstab query with the RangeName as the Column Heading. You also should not be using parameter prompts for user interface.


Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

check out thread701-1483948 for more on "groupings

 

[SoCalAccessPro]

Add this function to a module in your DB. Modify it to fit your useage.

Public Function TimeBlock(dtmDate As Date)
Select Case DatePart("H", dtmDate)
Case 0 To 6
    TimeBlock = "Graveyard"
Case 7 To 12
    TimeBlock = "Morning"
Case 13 - 18
    TimeBlock = "Evening"
Case Else
    TimeBlock = "Unk"
End Select
End Function

In your Crosstab, make the column heading =TimeBlock(yourdatefield). The above will generate 4 columns.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[msk7777]

Thanks everyone, that is a lot of great information. FYI, I am using Access 2007. Ok,

Duane, quick question, if I go down your path, how can i create a table with the information when it is pulling from a linked ODBC? I can't modify the information being pulled in.

Tyrone, I have to play around with crosstab queries a bit and then try your route too.

 

[dhookom]

If you can't create a local table as per the link from Leslie's reply, then I would use the small public function.

Duane
Hook'D on Access
MS Access MVP

 

[msk7777]

Ok, I will be honest, I really don't understand the whole idea of creating table buckets. I would be more than happy to learn. I don't want to take up all your time but can either of you maybe direct me to something that would walk me through it? I appreciate your help. Thanks!

 

[dhookom]

Do you understand how to create a table? If so, the table structure would be like:
[tt][blue]
tblTimeRanges
===============
TimeFrom date/time
TimeTo date/time
RangeName Text
[/blue][/tt]

[tt][blue]
tblTimeRanges
===============
TimeFrom TimeTo RangeName
7:00 AM 10:00 AM 7-10 AM
10:00 AM 1:00 PM 10 AM to 1 PM
1:00 PM 3:00 PM 1 to 3 PM
--- etc ---
[/blue][/tt]

You can then create your query as per the previous suggested link. If you can't figure this out, reply with how far you have progressed and your current tables and fields.


Duane
Hook'D on Access
MS Access MVP

 

[msk7777]

Man I'm an idiot, I just realized what you are talking about. Guess I can understand that phrase "can't see the forest for the trees". Ok, I get the table bucket now. I will play with it tomorrow. Going back to something you said earlier, what did you mean by "You should not be using parameter prompts for user interface"?

 

[dhookom]

faq701-6763

Duane
Hook'D on Access
MS Access MVP