By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Need help with Update query

P: n/a
dee
I have a table 'LeadHistory' which has among others, the following
fields.

Salesman(Text)
SalesmanAssmntDate(Date/Time)
Disposition(Text)

I also have a table 'LookUpSalesman' which has among others, these
following fields.

Salesman(Text)
NumSits(Number)
NumSales(Number)

I'm trying to make a query or queries which would count the records in
[LeadHistory].[Disposition] = "Sit Assigned" for each Salesman, and
then update the [LookupSalesman] table with these counts accordingly.
Now I'm stumped. I attempted to design the query to update the
'LookUpSalesman' table.

UPDATE LookUpSalesman SET LookUpSalesman.NumSits = DCount("
[LeadHistory]![Disposition]","LeadHistory","[LeadHistory]![Disposition]
= ""Sit Assigned waiting Disposition"" ");

It updates every salesman's 'NumSits' field with the total count of
"Sit Assigned" occurrences in the 'LeadHistory', regardless of who the
salesman was.

What I need is to update each salesman's 'NumSits' field with "Sit
Assigned" occurrence count for only those records that match his name.

I'm not a programmer and don't know how to do this, though I've tried
for 2 days now. Could someone help? Please?

Dec 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Please don't multipost. I took the time to write up a lengthy message for you
explaining the problems with your table structure, how to fix it and I designed
queries to get what you want -- and then I discovered you had posted this
question elsewhere, and it had already been answered. You didn't have the
courtesy to return to this thread to write, "Sorry. I don't want responders to
duplicate their efforts, so I'm abandoning this thread in favor of the other
one."

Please see the following Web page to find out what you can expect and what is
expected of you when participating in the newsgroups:

http://www.mvps.org/access/netiquette.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"dee" <pr******************@comcast.netwrote in message
news:11**********************@n51g2000cwc.googlegr oups.com...
>I have a table 'LeadHistory' which has among others, the following
fields.

Salesman(Text)
SalesmanAssmntDate(Date/Time)
Disposition(Text)

I also have a table 'LookUpSalesman' which has among others, these
following fields.

Salesman(Text)
NumSits(Number)
NumSales(Number)

I'm trying to make a query or queries which would count the records in
[LeadHistory].[Disposition] = "Sit Assigned" for each Salesman, and
then update the [LookupSalesman] table with these counts accordingly.
Now I'm stumped. I attempted to design the query to update the
'LookUpSalesman' table.

UPDATE LookUpSalesman SET LookUpSalesman.NumSits = DCount("
[LeadHistory]![Disposition]","LeadHistory","[LeadHistory]![Disposition]
= ""Sit Assigned waiting Disposition"" ");

It updates every salesman's 'NumSits' field with the total count of
"Sit Assigned" occurrences in the 'LeadHistory', regardless of who the
salesman was.

What I need is to update each salesman's 'NumSits' field with "Sit
Assigned" occurrence count for only those records that match his name.

I'm not a programmer and don't know how to do this, though I've tried
for 2 days now. Could someone help? Please?

Dec 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.