Re: Trouble creating an Update Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 wrote:
On 24 Oct 2006 15:22:47 -0700, "Dos Equis"
<n.byron.griffin@xxxxxxxxxxx> wrote:

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]

.

Relevant Pages

  • Re: Trouble creating an Update Query
    ... I have all subscribers listed by area and want to update ... No Totals query - including any totals operator such as Count or Sum - ... GROUP BY tblArea.ThisField, tblAreaThatField, tblArea.AreaID; ...
    (microsoft.public.access.queries)
  • Re: Pivot table using query
    ... I believe you'll need to change your query so that, ... subscribers who saw the movie & then do the count of those ... subscribers per joined year. ... >>Tim Long wrote: ...
    (microsoft.public.access.queries)
  • Payment check
    ... I am creating a system to keep track of the subscribers to a very small ... newspaper I work for. ... And I want to create a query to check if the subscriber has paid the yearly ... an input mask that creates the date like this: ...
    (microsoft.public.access.queries)
  • Re: Pivot table using query
    ... Tim Long wrote: ... can anyone tell me where I am going wrong with the following query? ... > FROM Subscribers INNER JOIN Movieviewings ON Subscribers.Subscriber_ID = ... > Table: Subscribers ...
    (microsoft.public.access.queries)
  • Re: Report is making too many numbers
    ... Am I supposed to attached the new totals query to the already existing ... where the Award is stored. ... FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON ...
    (microsoft.public.access.reports)