Counting Only Certain Records

[jtphenom]

Hi folks,



I apologize if this has already been discussed somewhere else on here.

I'm building a database in Access 2007 that will be used to track lab usage at my job. I have a table that has the user ID, date of sign-in, time of sign-in, etc. Now I'm trying to build a query to count the number of sign-ins in a given hour. So here's what I have, and it's not working:

First field in my query is my sign-in date field where the date == a given date. Second field is where I'm having trouble. Here's what I have in the field box:

8 AM:[Copy Of SignIn]![Sign_In_Time] Between #8:00:00 AM# And #8:59:59 AM#

I'm naming the field "8 AM" (it won't let me name it "8:00 AM" for some reason but that is a minor issue), and I'm trying to tell it I want all entries between 8:00 and 8:59. I haven't even gotten to the count part yet. But am I supposed to use Group By or what?? Either way, it won't let me enter that in the field box. It's telling me I'm using an invalid date value.

Can anyone help me?

Thank you!
James

[Rixxe]

Wouldn't some SQL work, I think it would be less complex:

SELECT Count(*)
FROM TableName
WHERE SignInDate = SignInDate and [Sign_In_Time] Between #8:00:00 AM# And #8:59:59 AM#

You might have to play around with the Red bit, using is equal or == or whatever it is in access (Many languages hurts the brain)
Obviously you can change the second SignInDate to be equal to a TextBox if thats what you want to do. Or you could change it to Today() or Now() if you want it for todays date only.

Also should mention it might be '' instead of ## around the datetime portion of that query, can't be sure as i've never used the Time section.


Good Luck.

[jtphenom]

Thank you for the reply, Rixxie. But a couple of issues here:

1. I'm not great with SQL (don't know much at all), and I'd prefer to know how to do it without using SQL (especially so I can teach my boss who doesn't know SQL at ALL. lol).

2. I don't know what you mean by these two things:

SignInDate = SignInDate and "Obviously you can change the second SignInDate to be equal to a TextBox if thats what you want to do."

Thanks!
J

[Rixxe]

Thats no problem, I can understand that you want to use VBA or another language, makes sense if you know it.
Only issue is in that case i cannot really help you out, as i've never learned VBA!

In C# at least, if you assign a Field to itself as the WHERE criteria, it will only return records with that date. Kind of pointless now i think about it for your case.... errrr
Anyway, Are you going to enter the date in yourself? Or is it going to be predefined?
If someone will enter it in, create a form + textbox or use a Calendar, get the result from that Control and do something like:

WHERE SignInDate = [TextBox/Calendar/Control]

Or just use Today()/Now() or whatever it is in access to select todays date.


I'll give this a go for the lols:

Dim strSQL as string
strSQL = "SELECT Count(*)" & _
"FROM TableName" & _
"WHERE SignInDate = '" & Me.TextBox.Value & "' "& _
"and [Sign_In_Time] Between #8:00:00 AM# And #8:59:59 AM#;"

Hopefully that will get it going if you really need it now, if not.. good luck!

[jtphenom]

Actually, I wasn't wanting to use another language. I wanted to do it right in the Query Design View. I don't know much if anything about creating forms, controls, etc. I just wanted to get the right query through the Query Design View if that makes sense.

Thanks for the help you've provided. I understand if you can't help anymore.

[Rixxe]

Oh Right, i see what you mean now.

Well are you using a form? or just tables?
This might help, there are also a number of sites similar to this if you get stuck.
Everything is there using the design view, have a play using the SignInDate, for now i'd just use: WHERE [SignInDate] = '10/01/2011' or whatever date you want.

http://office.microsoft.com/en-us/ac...010096311.aspx

[jtphenom]

Thank you for the link. I am just using a table for now. I'm creating the query for the purpose of later creating a report. I think I've got the date part figured out. My biggest problem is getting a count of the number of records between 2 times on a given date. I'll check that link out.

[jtphenom]

Oh I just realized I've read that link before. Either they didn't answer my question or I'm too dumb to realize that they did and to apply it to my specific problem. Ugh. :-/

[jtphenom]

I think I'm realizing that the main problem is that I can't figure out how to have "WHERE" criteria for each field separately. I'm trying to have my query give me counts of sign-ins for each hour. So for each field I need to be able to get the count of only the sign-ins for the hour that that field is meant for. But it looks like, even with SQL, the "WHERE" criteria can only be applied to the table, not to each field respectively. You can't have different criteria for each field.

[Rixxe]

As far as i know, unless you use an ID , and for each ID you say :

Where ID = 1, do this....

You will be unable to select specific fields as far as i know, Obviously if there is a date field, just filter by date.
I'd upload a sample of your database for people to have a look at, its kinda difficult to understand exactly what you want without seeing it.

Cheers