Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Access 2007 Help Please


  • Please log in to reply
5 replies to this topic

#1 jtphenom

jtphenom

  • Members
  • 251 posts
  • OFFLINE
  •  
  • Local time:08:28 PM

Posted 25 March 2011 - 11:58 AM

Hi folks,

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 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?

EDIT: It finally let me enter that expression above, but it is giving me a count of 35 (which is all of them!), when I actually only have 1 entry for that time range.

Thanks!
J

Edited by jtphenom, 25 March 2011 - 12:01 PM.


BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:08:28 PM

Posted 27 March 2011 - 08:43 PM

I would guess that if you have the data in two fields, date and time, you need to query for both fields ... if date=xxx AND time=yyy, otherwise you're picking up everybody who logged in between 8 and 8:59 regardless of date, which might well be all the records you now have.

#3 jtphenom

jtphenom
  • Topic Starter

  • Members
  • 251 posts
  • OFFLINE
  •  
  • Local time:08:28 PM

Posted 27 March 2011 - 09:30 PM

tos226, thank you for your reply. I do already have that factored in. Please see my original post where I said "First field is my sign-in date field where the date = a given date."

Edited by jtphenom, 27 March 2011 - 09:31 PM.


#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:08:28 PM

Posted 28 March 2011 - 09:16 PM

Using Access2003
see if those pictures will help you.
I built a simple 3-field table.
Queried for both date AND time (on one row in design view means AND, and you can read the SQL view how the syntax goes)
http://img863.imageshack.us/img863/3232/accessquery.png
Ignore the silly looking 1899 after "Between" for the time field - it's because you use 2 fields rather than one date/time, I think.
Regardless, the result is ok - records 3 and 5 were not included, since not 3/25/2011.
Good luck with your project :)

#5 jtphenom

jtphenom
  • Topic Starter

  • Members
  • 251 posts
  • OFFLINE
  •  
  • Local time:08:28 PM

Posted 29 March 2011 - 09:44 AM

Thank you! Now I just need to figure out how to return a count of the rows that match my criteria, instead of displaying those rows.

#6 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:08:28 PM

Posted 29 March 2011 - 07:28 PM

Display counts-only suggestion:
http://img31.imageshack.us/img31/2148/pcount.png

Your next challenge is to read about how to get that Total row into design (cheat by reading HELP and the SQL view till it makes sense), Parameter queries and then putting query results on official-looking report :) for the lab supervisor.
Enjoy Access. I haven't used it for so many years, I can't remember any of it :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users