Trouble creating an Update Query

[Dos Equis]

Please help,

I am trying to create an update query to update teh # of subscribers in
my database. I have all subscribers listed by area and want to update
the area table with the number of subscribers in the data base.

What I have so far:

UPDATE tbl_Area.#ofSubHomes
SELECT Count (AreaID)
AS tbl_Area.AreaID FROM tbl_SubscriberData.areaID

The subscribers are in tbl_SubscriberData and the field with their area
is AreaID

The area table is tbl_Area and the field to be updated with the count
is #ofSubHomes
tbl_Area.AreaID should be used as a criteria for each record in the
query, I have 28 Areas so I'd like it to update each record on it's own
instead of my running it 28 times for different records.

Thanks,

Byron

 

[Jeff Boyce]

Byron

If you already have a way to count subscribers, you don't need to store the
calculation. Just run the query when/where you need to know how many...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

Please help,

I am trying to create an update query to update teh # of subscribers in
my database. I have all subscribers listed by area and want to update
the area table with the number of subscribers in the data base.

I'd suggest that as a calculated field, you should not store this
count ANYWHERE in any table. Once you store it, it's vulnerable to
being *wrong* - you add a new subscriber or delete a subscriber, and
hey presto, you have a different count which will NOT be reflected in
your table field.

What I have so far:

UPDATE tbl_Area.#ofSubHomes
SELECT Count (AreaID)
AS tbl_Area.AreaID FROM tbl_SubscriberData.areaID

The subscribers are in tbl_SubscriberData and the field with their area
is AreaID

No Totals query - including any totals operator such as Count or Sum -
is ever updateable.

The area table is tbl_Area and the field to be updated with the count
is #ofSubHomes
tbl_Area.AreaID should be used as a criteria for each record in the
query, I have 28 Areas so I'd like it to update each record on it's own
instead of my running it 28 times for different records.

You can do so, using the DCount() VBA function, but... WHY?

A simple TOtals query

SELECT tblSubscriberData.AreaID, Count(*) As CountOfSubscribers
FROM tblSubscriberData
GROUP BY AreaID;

will show you exactly the data you want to see; or, if you want to see
other fields from tblAra,

SELECT tblArea.ThisField, tblAreaThatfield, Count(*)
FROM tblArea INNER JOIN tblSubcriberData
ON tblSubscriberData.AreaID = tblArea.AreaID
GROUP BY tblArea.ThisField, tblAreaThatField, tblArea.AreaID;

This query can be used as the basis for a form or report and will
always show the right count.

John W. Vinson[MVP]

 

[Dos Equis]

Thank you for helping, Why? I'm new and learning processes, in
addition, I have to use the count to creat a payment schedule for
employees. I am under the impression that I need a point of reference
for this second calculation. I think that it's possible to use a query
as that point of reference but not sure how to do it and need to
research it more I guess.

Thanks again,

Byron

 

[John Vinson]

Thank you for helping, Why? I'm new and learning processes, in
addition, I have to use the count to creat a payment schedule for
employees. I am under the impression that I need a point of reference
for this second calculation. I think that it's possible to use a query
as that point of reference but not sure how to do it and need to
research it more I guess.

You can base a second query (with calculations) on this first totals
query, or you can recapitulate the calculation. It's *sometimes*
necessary to store the calculated value in a temporary table, if the
totals query is just too slow to be practical (which can happen with
large tables), but try using the query based on a query first. Good
luck!

John W. Vinson[MVP]

 

[Dos Equis]

John,

The query is very fast as I only have about 300 records to search and
sum. I tried the SQL you wrote in my query and kept receiving an error
about syntax, not sure what that was about but ended up with:

SELECT tbl_Area.CID, Count(*) AS Subscribers
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscriberData ON tbl_Area.AreaID = tbl_SubscriberData.AreaID
GROUP BY tbl_Area.CID, tbl_SubscriberData.SubDoNotDeliver
HAVING (((tbl_SubscriberData.SubDoNotDeliver)=No));

and it works fine. The SubDoNotDeliver is because we have area coverage
and some people do not want the paper. I excluded those and receive an
accurate answer. I did this all in design view and Access 2K wrote the
code so I can't explain some of it but now understand more than I did.

My pay query will have to calculate the totals in this query by $.20
and the homes in a given area by $.04 then add the two; doing this for
all cariers. For instance, I have 1 carrier who delivers 760 papers 8
of which are subscribers. He makes $31.52 per week, after 4 weeks his
pay is .05 per paper. He then makes $39 (I don't set the pay, just
calculate the total).

Guess I'll be working on it shortly. Thanks again,

Byron