Can (or how can) I do this

[Guest]

Hello,

My Access application tracks an employee's progress through a process
similar to hiring. There are 5 steps in the process. I track 4 of the steps
by counting the number of times a value appears in a date field for that
step. The count is then displayed and used in a calculation. The 5th step
is more complicated. The 5th step needs to determine if the employee has
verified that he can access all the applications that he should have access
to.

My process tracking table has the first 4 dates. The application access
info comes from a different table. This process tracking table also has a
field to store the count of the number of applications where access has been
verified. On the form to do all this, I use the Forms' current event to work
with the four dates. This is all set and working fine. But for the
application access, I need to run a query against the 2nd table to get a
count, then get the count stored in the process tracking table.
After I have the count in the process tracking table, I can do the necessary
calculations to determine a "percentage of hiring process completed".

How can I run that query and get the value back to the table?

My query to get the count looks like:
SELECT Count (*)
FROM <tblName>
WHERE EmplNum = Me.EmplNum

What I need should look something like this (Syntax is not correct)
Update tblProcessTracking
Set AppsVerifiedCount =
Select Count(*)
FROM <tblName>
WHERE EmplNum = Me.EmplNum

Can I do this? If not, can someone help me with how to accomplish this?

Thanks in advance,

 

[John Spencer]

You can't use aggregate queries in an update query. You can use the VBA
Aggregate functions. My question is why do you want to record the data in a
table when you can always get the needed value using a query and always know
that the value is current?

Update tblProcessTracking
Set AppsVerifiedCount = DCOUNT("*","tablename","EmplNum=""" &
tblProcessTracking.EmplNum & """"

Add a where clause to restrict the records that get updated

But you can also have the value available by simply using DCOUNT function or
using a subquery such as
SELECT *,
(SELECT Count(*) FROM TableName
WHERE TableName.EmplNum = tblProcessTracking.EmplNum) as CountTimes
FROM tblProcessTracking

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,

Thanks for the reply.

I don't necessarily need to store the value. I thought it would make things
easier but I can easily remove it.

I'm still a little confused so I'd like to make sure I'm understanding you
correctly.

My master form has 2 txtboxes to display the count that I'm after (and the
total # of apps). Do I set the Control Source = the DCount statement which
then looks like this:

DCount("*", "tblEmplApplMapping", "S3ID = '" & Me.S3ID & "'")

I'm still getting comfortable with the syntax so the SQL statement would
look like:
Select Count(*)
FROM tblEmplApplMapping
WHERE tblEmplApplMapping.S3ID = Me.S3ID

OR

should I use the master-form's On Current event and say the txtbox = the
DCount?

thanks,
Rich

 

[John Spencer]

You should be able to use the Dcount statement as a control's source./ You
cannot use a query as a control's source.

Sorry, for the short answer. Gotta go - time to pick up the grandkids.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..