Can (or how can) I do this

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,


0
Utf
9/18/2007 3:52:07 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2558 Views

Similar Articles

[PageSpeed] 59

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

"rich" <rich@discussions.microsoft.com> wrote in message 
news:DA355A77-FFBF-424D-9940-38E2757EBED1@microsoft.com...
> 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,
>
> 


0
John
9/18/2007 4:37:09 PM
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" wrote:

> 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
> ..
> 
> "rich" <rich@discussions.microsoft.com> wrote in message 
> news:DA355A77-FFBF-424D-9940-38E2757EBED1@microsoft.com...
> > 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,
> >
> > 
> 
> 
> 
0
Utf
9/18/2007 5:38:01 PM
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
..

"rich" <rich@discussions.microsoft.com> wrote in message 
news:1EDC787A-943C-4581-A7AB-AC6C4312A7F9@microsoft.com...
> 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" wrote:
>
>> 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
>> ..
>>
>> "rich" <rich@discussions.microsoft.com> wrote in message
>> news:DA355A77-FFBF-424D-9940-38E2757EBED1@microsoft.com...
>> > 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,
>> >
>> >
>>
>>
>> 


0
John
9/18/2007 7:10:43 PM
Reply:

Similar Artilces:

can not send my attachments from microsoft word
was reading others problems, i have a free trail of norton on computer, could this be the problem? and if so is there away to change setting so i can have full use of my computer with out loseing protection? thanks "brit_minor" <brit_minor@discussions.microsoft.com> wrote in message news:1C41CDA9-DB66-4C63-9580-53246A811F5A@microsoft.com... > was reading others problems, i have a free trail of norton on computer, > could > this be the problem? and if so is there away to change setting so i can > have > full use of my computer with out loseing pro...

Can't Upgrade an MDB, says it's already open
I am trying to open an MDB under Access 2007 and publish it with my self-cert. I can open it, but the Publish menu item is greyed out. Maybe this is because 2007 can't publish an older MDB. So, I try "Convert", but that gives me this message box: You attempted to open a database that is already opened exclusively by user 'Admin' on 'MYMACHINENAME'. Try again when the database is available. The only thing that has got the database open is the copy of Access that I'm using to try to convert it! Any idea how I get around this? Phil Hibbs. Phil Hibbs <snar...

How can I retrieve the format of a cell?
Dear all, A cell A1 is conditionally formatted so that it is either red (FF0000), green (00FF00) or blue (0000FF). May I know if I can write a function for another cell B1 such that it shows a word "RED" if A1 is red, a word "BLUE" if A1 is blue and a word "GREEN" if A1 is green? Thanks in advance. Best Regards, Chris You can use programming to determine the colour. Chip Pearson has instructions on his web site: http://www.cpearson.com/excel/CFColors.htm Chris wrote: > Dear all, > > A cell A1 is conditionally formatted so that it ...

Mac people can't open my jpeg attachments
I have Vista Home Premium and Outlook 2003. When I try to send a jpeg as an attachment, most people can open it, but not those with Macs. It says "Windata" or something like that. This is new behavior, has not been a problem before. Is there a setting I need to change? Thank you. You need to send email in HTML or Plain Text, not RTF. "Janja" <Janja@discussions.microsoft.com> wrote in message news:AD42A7FF-C449-4008-81E2-637D76FF11B8@microsoft.com... :I have Vista Home Premium and Outlook 2003. When I try to send a jpeg as an : attachment, most people ca...

How can i know who published a gantt ?
Friends, I need know who published , ie, who migrate from database draft to published a gantt; sql query also can be; regards ...

Can't open after reinstalling
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel get this error message: "microsoft word has encountered a problem a need to close mac... etc" i have office 2008 with the latest update. i tried removing and deleting every single microsoft file (preferences, application support, etc.) then reinstalling but it keeps sending the message. However i tried creating a new user account and it worked on that one. What can i do to make this work? If you haven't already done so, make sure that Office (12.2.1) and OS X (10.5.8) are fully updated & that you'v...

Can Excel evaluate a series of numbers
I made an excel file to generate results from one number as the input a couple of other fixed numbers to reference against and a couple of algebra and quadratic equations to generate the desired answers. Can I make a list of numbers in my excel file and have excel run through the equations already set up and generate a list of results? If not, how do I make a file that will step through a list of numbers, run excel on each and save the results for each? Thank you, Chad Sellers Can you just copy your formula? For example, let's suppose that the "one number" is in cell ...

Can't get Word Mail Merge to find my Excel file of names and addre
I can't get my excel file to show up in the Select Data Source window. I clicked on "New Source" then selected ODBC DSN, selected Excel File, and got a Select Database and Table screen with no options for me, except ack and Cancel. I'm sure I'm doing something wrong, can anyone help? Thanks. Marilyn Collins Did you navigate [using the Directories list] to the folder where your Excel file is stored? Double-Click the folders following the path to that directory. Once you get to a folder containing Excel files they will appear in the left list of Database Nam...

i am receiving error while downloading my emails but i can send
hello everyone, I have a question for you. I am not able to receive emials from our email server.but i can send the email successfully. Any i dea.? i am stuck here. Thanks for yr help. Thanks, Bharat. bharat wrote: > > hello everyone, > > I have a question for you. > > I am not able to receive emials from our email server.but i can send the > email successfully. > Any i dea.? > i am stuck here. > > Thanks for yr help. > > Thanks, > Bharat. > > What is the exact error message you are receiving? ...

Crystal Reports: Information is needed before this report can be processed
When selecting any of the crystal reports, I always get this message: CrystalReportViewer: Information is needed before this report can be processed Has anyone seen this message or know how to fix it? Hi, This will happen if your AD server are 2000 Servers and your CRM Server is 2003 Server. There is a patch available, You have to contact CS by creating an incident(Free of charge). -Qureshi "ewoiruweo@wpeirwpe.com" wrote: > When selecting any of the crystal reports, I always get this message: > > CrystalReportViewer: Information is needed before this report can be &g...

Can't Lock in To Font Size And Type in OE 6
I hope that I am in the right pew. I've posted on several help boards but can't get any help with my problem I have WINME and OE 6-- When I compose a message and I want to change the FONT size which is set on 12 to a larger size I change the size OK. Just as soon as I hit the first key the size jumps back to 12. The same problem with FONT style. It won't stay fixed to any style but ARIEL. I've downloaded IE and OE but that didn't help. Can I get a CD to install which may help--or can some one suggest corrective action on this board? Thanks Herman Hi - act...

Can I see My Calendar as others see it
Can I view my calendar as if I am viewing it by someone with whom I share? no. Your permission levels are greater. Why do you want to be able to? -- Regards Judy Gleeson MVP Outlook in Canberra, Australia .. "laurelpowellcochrane" <laurelpowellcochrane@discussions.microsoft.com> wrote in message news:46AD6AD8-5B97-4D06-9F73-2D212B626E05@microsoft.com... > Can I view my calendar as if I am viewing it by someone with whom I share? ...

Can't Reply to Emails
I'm experiencing a problem with my Outlook Express. Whenever I try to reply to an email I send by clicking the "reply" button the email doesn't go through. The only way I can send a message out is by composing a new message. Has anyone out there encountered this problem? ...

How can I enumerate all installed frameworks ...
.... programatically using C#? Thanks. AA2e72E wrote: > .... programatically using C#? There's nothing in .NET that does this. AFAIK, you have to look at the registry and what's actually installed on the disk. On Jun 3, 9:25=A0am, Peter Duniho <NpOeStPe...@NnOwSlPiAnMk.com> wrote: > AA2e72E wrote: > > .... programatically using C#? > > There's nothing in .NET that does this. =A0AFAIK, you have to look at the > registry and what's actually installed on the disk. There's a fairly good chunk of code that does that here: http:...

Can't recover deleted items
I've seen this post several times, but thus far haven't found a posted solution. I've got a user who goes into Outlook and tries to recover her deleted items. When she does so, it boots her out of outlook. I originally thought this was a Win98 issue with Outlook 2000. I upgraded her to W2K pro and it is still doing it. I'm confrused. Could soemone out there lend a solution? Thanks, Mark Have her try logging into her mailbox on another computer to isolate the problem. If you run OWA, try it there, too. Markus wrote: > I've seen this post several times, but thus far h...

Can't find origination module
In GP 10, we have some invoices we need to delete - errors from when we just started up about a month ago. When we go to Transactions>>Sales>>Posted Transactions, we can bring up the invoice, but when we hit VOID, it gives us this message: "this transaction didn't originate in Receivables Management. Reversing entries will be made in Receivables Management and General Ledger only. Do you want to continue?" We hit NO because we don't want to screw up the system, but then we can't find the invoices anywhere else. We entered them in Transactions>>Sal...

Excel 2000- protect one column- can only be modified by one person
I need to lock one column that can only be modified by one person. Can this be done? Joe There's nothing in Excel 2000 to set user range permissions. That feature was added in Excel 2002. fyi. -- Jim Rech Excel MVP "lunker55" <joec@shipwaystairs.com> wrote in message news:ObkUI8VAEHA.3936@TK2MSFTNGP11.phx.gbl... | I need to lock one column that can only be modified by one person. Can this | be done? | | Joe | | Thanks Jim "Jim Rech" <jrrech@hotmail.com> wrote in message news:O6t8%23rWAEHA.1420@TK2MSFTNGP11.phx.gbl... > There's nothing in E...

Can office small business 2003 be upgraded to professional 2007
I have Offise Small Business 2003 and need to upgrade to 2007 or newer. Can I upgrade from Small Business version to the Professional Version and have all the programs of 2007? Or can I only upgrade to the Small Business version? John in MN wrote: >I have Offise Small Business 2003 and need to upgrade to 2007 or newer. Can >I upgrade from Small Business version to the Professional Version and have >all the programs of 2007? Or can I only upgrade to the Small Business >version? > > Yes you can upgrade to Office 2007 Professional and the good news is...

Money 2003: help - how can I add trended items back into the cashflow analysis
I deleted some trended items from my cashflow analysis by accident on mny 2003, Is there any way of getting them back in there??? thanks in advance. Click on the 'customise cash flow' option which is either on the left hand side of the cash flow display, or right clicking on the chart. To put it back, you need to select an option in there. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or...

Outlook 2003, can it look like OXP?
Is there a way to get Outlook 2003 to look like Outlook XP (folder list on the left, inbox above, preview pane below)? I can't be in the minority that doesn't like the new layout. Thanks. symfnyx wrote: > Is there a way to get Outlook 2003 to look like Outlook XP (folder > list on the left, inbox above, preview pane below)? I can't be in > the minority that doesn't like the new layout. Thanks. One of the what's new, ahem, "features" that counted as a negative for me (and another reason that I haven't upgraded; no bang for the buck). Oooh, boy,...

Exchange 2000 : Can't hide Domain Admin owned mailboxes
We are currently migrating from Exchange 5.5 to Exchange 2000. Our postmaster doesn't have domain admin status and we don't want to change that if possible. In Active Directory Users & Computers, the postmaster can hide any Exchange 2000 mailbox except the ones that belong to domain admin accounts. The message «Access Denied» is shown when trying the command Is it normal ? Is there a solution other than giving domain admin status to the postmaster ? "R. Garant" <richard.garant@saaq.gouv.qc.ca.invalid> wrote: >We are currently migrating from Excha...

Can not insert some PDF files into Outlook 2003
Acrobat 6.0 PRO, Windows XP, Office 2003 I get this error message: Can't create file: XXXXXX.PDF. Right click the folder you want to create the file in and then click properties on the shortcut menu to check your permissions for the folder. We are trying to insert some PDF files into an E-Mail message, using Outlook 2003. The file(s) reside on a network drive (have even tried copying the file ot the local HD and testing). Only 3 particular files give this error message, while other PDF, Excel, or Word files insert with no problems. These are not links, but the actual files. And...

Can we install Rollup 2 for CRM 1.2 Build v1.2.3297.0
Hi, We have a pending upgrade from version 1.2 to v3.0 I was thinking of istalling the rollup for ver 1.2 before upgrading to version 3.0. The current build of the server is v1.2.3297.0 The link below suggests that the build #for rollup 2 1.02.3297.173 Has any body come up with issues when installing the rollup on the 1.2 server (build is 1.2.3297.0) http://support.microsoft.com/default.aspx?kbid=904435# Or is it better to try and directly upgrade to 3.0 rather than putting the roll up and then doing the upgrade? Suggestions Please.... If you are going to upgrade, then I would just ...

Can I change names of fields in calendar?
I am using a calendar like a project manager. I would like to change the names of fields eg Location to Action to be done, Contacts to Action officer. I have been able to change the column headings in Custome view, but this doesn't change teh names of the fields on the calendar form. That is teh form that appears when you make an appointment. No, you can't change them. You could use in-cell editing and create the items in table view, where the column display names can be changed. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook &a...

Can't use Word to Reply or Forward
If I try to Reply or Forward and e-mail while Word is set as my editor, the attempt results in a message about Word being unavailable or the wrong version. If I create a new e-mail with Word and type in an explicit e-mail address in the TO or CC fields, it works fine and sends the e-mail to the entered addresses. But if I use the TO... button to pop up the list of contacts, then select a contact and click OK, Word gives me an error "No such interface exists" and doesn't return the addresses. It was working fine Tuesday afternoon, but was broken the next time I used it ...