Need help with Update query

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

 

[dee]

You shouldn't have a NumSits field in the LookUpSalesman table. You can
compute it on the fly any time you want with the following. I bet that you
can do something similar with NumSales also which could well mean that you
can dump the entire LookUpSalesman table once all queries, forms, and reports
using it are modified.

SELECT LeadHistory.Salesman,
Count(LeadHistory.Disposition) AS NumSits
FROM LeadHistory
WHERE LeadHistory.Disposition)="Sit Assigned waiting Disposition"
GROUP BY LeadHistory.Salesman;

Ok. Thanks, it works.

For NumSits Qr6:
SELECT LeadHistory.Salesman, Count(LeadHistory.Disposition) AS NumSits
FROM LeadHistory
WHERE (((LeadHistory.Disposition)="Sit Assigned waiting Disposition"))
GROUP BY LeadHistory.Salesman;

For NumSales Qr7:
SELECT LeadHistory.Salesman, Count(LeadHistory.Disposition) AS NumSales
FROM LeadHistory
WHERE ((Left([LeadHistory].[Disposition],4)="Sale"))
GROUP BY LeadHistory.Salesman;

But now I have to figure percentage of sales for each salesman
(NumSales/NumSits)*100.
Don't I need an updateable query to do this?

 

[John Vinson]

Don't I need an updateable query to do this?

Only if you want to store that percentage in a table... AND YOU DON'T.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

 

[dee]

Only if you want to store that percentage in a table... AND YOU DON'T.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

I agree, but how do I do this. I have 2 queries. One to get the NumSits
and another to get the NumSales for each salesman.

How do I get the (NumSales/NumSits)*100. for each salesman? I don't
know how.

 

[dee]

Please ignore my last post. Got it all working. Thanks guys