Newbie to Access. Help with a series of queries.

[mdmcguire]

Hello all.

Essentially I have two tables that I need to set up queries from. The first table is called tblDB. It has two fields: name, and ID. This has the name of each person in my group and their ID number. The second table is called tblScanner. It also has two fields: ID and timestamp. This table is generated from a badge reader the reads in a persons ID number and time stamps it. I would like to set up queries that return each person in the groups name once (if they exist in tblScanner) and the number of times they have scanned their badge in the current month.

I realize that this seems very basic. And it is. I am just very new to acces. I've been able to build these two tables and a form to read data from the badge scanner, but now I'm stuck on the queries because I know almost zero SQL. I've been looking at examples for almost a week now and am getting nowhere. If there is a better way to achieve this, let me know. Thanks in advance.

Matt

[June7]

Should the count include this most recent scan? If so, that requires saving record to table first.

One way to get the count is with a domain aggregate function by expression in a textbox ControlSource property:

=DCount("ID","tblScanner","ID=" & Me.ID)

Use as much criteria as needed to get the required count.

= DCount("ID","tblScanner","ID=" & Me.ID & " AND Month([date fieldname])=" & Month(Date()))

Save the current record and refresh the form and the count will calc.

Record entry/edit is committed to table when form is closed, move to another record, or run code.

[Dal Jeanis]

SELECT TD.ID, First(TD.Name), Count(TS.TimeStamp)
FROM tblScanner AS TS INNER JOIN tblDB AS TD ON TD.ID = TS.ID
WHERE TS.TimeStamp >= #10/01/2013# AND TS.Timestamp < #11/01/2013#
GROUP BY TD.ID;

The hard-coded dates with the hash-tags # around them should be replaced with a function that determines the first day of the desired month and the first day of the next month, respectively.

The reason you need "less than the first day of the next month" is that you're dealing with timestamps, and just after noon on #10/31/2013 12:20:00 PM# is mathematically greater than #10/31/2013#.

[mdmcguire]

Hi Dal Jeanis,

Thank you very much for your help with this. The output is exactly what I wanted. Can you reccommend any reference to learn access/SQL from?

Matt

[Dal Jeanis]

Start with post #5 in this thread https://www.accessforums.net/access/...ess-38592.html

[Dal Jeanis]

ignore this post.