A query that tells me how many Incidents between certain hours.

[TrulyVisceral]

Name: 2.png  Views: 14  Size: 27.1 KB  ID: 33887" class="thumbnail" style="float:CONFIG" />
This is an Excel chart, but I just wanted to give a visual example.
What I really wanted to do can't be done due to limitations in the Access chart creation apparently. As such, I got told to do something else with the chart instead.
I wanted to create a query that tells me how many incidents there were between the hours of each of those three sections (6-8, 8-17 and 17-21).

I dunno what you'd need to help me in this. Please tell :3

[isladogs]

Ok I'll answer this as well.
One way would be to create a table with two fields - IncidentHour (0-23) and GroupName
Now join this table and your incident table or query joining by IncidentHour.
Convert to aggregate query by clicking the Totals button, group by IncidentHour and Sum the Incidents field

[TrulyVisceral]

Ok I'll answer this as well.
One way would be to create a table with two fields - IncidentHour (0-23) and GroupName

Wait, do I put the numbers 0 through 23 in the IncidentHour field, while leaving the GroupName empty?

Coolcool.

[isladogs]

No - you need to populate the GroupName field as well e.g. A, B, C or possibly use the labels similar to those shown in your chart.

[TrulyVisceral]

No - you need to populate the GroupName field as well e.g. A, B, C or possibly use the labels similar to those shown in your chart.

Ai'ght, I'm back. Maybe I should provide the following:

1)The graph (without the coloured sections) comes from this query

2)"DigHoras" is just the numbers 0-23

3)"QRYPastHoras" is the same as point #1, but it doesn't show those that don't have entries:



edit: I feel like I'm misunderstanding something (as usual).
The end result is simply to have 3 groups, displaying the sums of all incidents between the hours of 6-8, 8-17 and 17-21
This is gonna give that? Cause I feel I gave some info wrong, if my understanding of your solution is correct.

[Micron]

see if this gets you started

SELECT DISTINCT DSum("[Cantidad]","[PastHoras]","[Hora]>=6 and [Hora]<=8") AS Expr1, 
DSum("[Cantidad]","[PastHoras]","[Hora]>=8 and [Hora]<=17") AS Expr2
FROM PastHoras;

This assumes the table/query is named PastHoras with fields Cantidad and Hora.

[isladogs]

QRYPastHoras" is the same as point #1, but it doesn't show those that don't have entries:

I've already answered this point for someone recently - possibly you?
If not, I'll get back to you if necessary. Logging off for a while

[TrulyVisceral]

I've already answered this point for someone recently - possibly you?
If not, I'll get back to you if necessary. Logging off for a while

It was, yes, the answer is #1. Sorry for going off for a day, I had other stuff to do. Anyways, I could do nothing with the answer you gave me, made the table but... what then?

This is basically what I understood, group by hour and then sum the totals. But the resulting table is exactly the same as the original query.

see if this gets you started

SELECT DISTINCT DSum("[Cantidad]","[PastHoras]","[Hora]>=6 and [Hora]<=8") AS Expr1, 
DSum("[Cantidad]","[PastHoras]","[Hora]>=8 and [Hora]<=17") AS Expr2
FROM PastHoras;

This assumes the table/query is named PastHoras with fields Cantidad and Hora.

Well, the query is QRYPastHoras, so close enough. So I did that and got this:

Expr1	Expr2
62	430

I'm assuming it just summed everything together.

[Micron]

Yes. You change Expr to something more meaningful. E.g. "SixToEight". Probably not a valid solution if the time slots you need will fluctuate.

[TrulyVisceral]

Ok, cool... HOWEVER, those are ALL of the incidents summed together, not just of 6-8?
But what about the other 2 slots? 8-17 and 17-21.

Hold on, I got something.

[isladogs]

Back in post #1, you wrote this:

I wanted to create a query that tells me how many incidents there were between the hours of each of those three sections (6-8, 8-17 and 17-21).

My suggestions were designed to achieve that
See attached for an example database showing what I mean

I get confused by your mixture of languages which I'll refer to as Spanglish so I've largely ignored your naming apart from Hora

I've coped the first set of data from post #5 - I realise this is a query but as I don't have the underlying data, I've used a table for this: tblIncidentTotals
I've added a second table as suggested earlier: tblHourGroups with 2 fields: Hora & GroupName
I've populated this as 5 groups A-E to include all the hours on your chart
A: 0-6; B: 6/7; C: 8-16; D: 18-21; E 22/23 which means B/C/D are your coloured graph sections

Finally I've made a query qryGroupIncidentTotals with these results



See attached database. Hopefully you can adapt to suit your needs

[TrulyVisceral]

I get confused by your mixture of languages which I'll refer to as Spanglish so I've largely ignored your naming apart from Hora

That is exactly that (it's an actual term, btw. Spanglish I mean). Bad habit, haven't stopped for years, it gets confusing for me and everyone, because I always work in english, but not everyone around me does, so it's weird m8. Hell, not even my supervisor has that much of a grasp on english. Also, I try to make some names shorter, so as to not give that much of a headache when it comes to programming, and because ssome spanish words are longer than english (eg. Past < Pasado) even if it's just a few letters, I tend to do that.

Anyways, thanks for this m8. I just changed the ABC Naming to the actual groups (6-8 etc...).


I'll try to make all future projects entirely in english, just to make it easier whenever I ask for help.

[isladogs]

One suggestion. Add another field to your tblHourGroups which is either A-E as I did or more simply 1-5.
Then add that as the first field of your final query shown above so it sorts them in the correct order.
The field doesn't need to be visible
At the moment the groups are sorted in 'alphabetical order' as the fields are text

BTW I've no idea what your comment Y33T means in the rep points section - Spanglish again or intended as an emoji?

[TrulyVisceral]

One suggestion. Add another field to your tblHourGroups which is either A-E as I did or more simply 1-5.
Then add that as the first field of your final query shown above so it sorts them in the correct order.
The field doesn't need to be visible
At the moment the groups are sorted in 'alphabetical order' as the fields are text

BTW I've no idea what your comment Y33T means in the rep points section - Spanglish again or intended as an emoji?

Term used to express excitement; especially used in basketball when someone has shot a three-pointer that they are sure will go in the hoop.

Also, it's in "leet speak" (stylized: 1337 5P34K). it's just "Yeet".

[isladogs]

LOL. I expect everyone except me knew that without needing it explained...