Update Query problem

[tiger0268 via AccessMonster.com]

I am not very experienced in SQL, so I need some help. Alright, typically
when I create an update query, I do a select query and get my desired results
and then convert it over to an Update query; however, this time I have run
into a few problems. I know it is simple, I just can't figure it out. Below
is my select query:

SELECT MAIN.PostID, Count(MAIN.PostID) AS PostCount, MAIN.Title, First(MAIN.
PostDate) AS FirstOfPostDate, MAIN.PostNumber, MAIN.Post
FROM MAIN
GROUP BY MAIN.PostID, MAIN.Title, MAIN.PostNumber;

Basically, I have the following scenario:
ID Title Date PostNumber Post
1 string1 Date1 1 Post1
1 string1 Date2 1 Post2
2 string2 Date3 1 Post3
2 string2 Date4 1 Post4
3 string3 Date5 1 Post5
3 string3 Date6 1 Post6
3 string3 Date7 1 Post7
....

I want it to look like this:
ID Title Date PostNumber Post Expr:
Count
1 string1 Date1 1 Post1
2
2 string2 Date3 1 Post3
2
3 string3 Date5 1 Post5
3

And I want to assign the CountValue to the PostNumber.

ID Title Date PostNumber Post Expr:
Count
1 string1 Date1 2 Post1
2
2 string2 Date3 2 Post3
2
3 string3 Date5 3 Post5
3

 

[Guest]

Am I understanding you correct in that you want to update the same field that
you are counting? You can not rollup records and update them at the same
time. You could change your totals query to a make table query then use that
table to update the orignal table records.

 

[tiger0268 via AccessMonster.com]

Sorry for the confusion. I am counting all records that are the same for
PostID. Like in the below scenario, I should have a count of 2 for ID "1", 2
for ID "2", and 3 for ID "3". I then want to update their respected
PostNumber field with their respected group count. FYI...ID is not a primary
key or an autonumbering...it is more like an identifier I am using to group
LIKE items.

ID Title Date PostNumber Post
1 string1 Date1 1 Post1
1 string1 Date2 1 Post2
2 string2 Date3 1 Post3
2 string2 Date4 1 Post4
3 string3 Date5 1 Post5
3 string3 Date6 1 Post6
3 string3 Date7 1 Post7

Should update to look like:

ID Title Date PostNumber Post
1 string1 Date1 2 Post1
1 string1 Date2 2 Post2
2 string2 Date3 2 Post3
2 string2 Date4 2 Post4
3 string3 Date5 3 Post5
3 string3 Date6 3 Post6
3 string3 Date7 3 Post7

Is there a good way to do this or should I go with a make table query?

Am I understanding you correct in that you want to update the same field that
you are counting? You can not rollup records and update them at the same
time. You could change your totals query to a make table query then use that
table to update the orignal table records.

I am not very experienced in SQL, so I need some help. Alright, typically
when I create an update query, I do a select query and get my desired results

[quoted text clipped - 38 lines]

3 string3 Date5 3 Post5
3

 

[Guest]

I know of no other way than to use the make table.

But you do know that storing caluclated data is a bad idea as the
information gets outdated.

Sorry for the confusion. I am counting all records that are the same for
PostID. Like in the below scenario, I should have a count of 2 for ID "1", 2
for ID "2", and 3 for ID "3". I then want to update their respected
PostNumber field with their respected group count. FYI...ID is not a primary
key or an autonumbering...it is more like an identifier I am using to group
LIKE items.

ID Title Date PostNumber Post
1 string1 Date1 1 Post1
1 string1 Date2 1 Post2
2 string2 Date3 1 Post3
2 string2 Date4 1 Post4
3 string3 Date5 1 Post5
3 string3 Date6 1 Post6
3 string3 Date7 1 Post7

Should update to look like:

ID Title Date PostNumber Post
1 string1 Date1 2 Post1
1 string1 Date2 2 Post2
2 string2 Date3 2 Post3
2 string2 Date4 2 Post4
3 string3 Date5 3 Post5
3 string3 Date6 3 Post6
3 string3 Date7 3 Post7

Is there a good way to do this or should I go with a make table query?

Am I understanding you correct in that you want to update the same field that
you are counting? You can not rollup records and update them at the same
time. You could change your totals query to a make table query then use that
table to update the orignal table records.

I am not very experienced in SQL, so I need some help. Alright, typically
when I create an update query, I do a select query and get my desired results

[quoted text clipped - 38 lines]

3 string3 Date5 3 Post5
3

 

[tiger0268 via AccessMonster.com]

I was going to have the update query run on a every time a new record was
created on a form event so maybe I can just try and do it through VBA, but
thanks for helping.

I know of no other way than to use the make table.

But you do know that storing caluclated data is a bad idea as the
information gets outdated.

Sorry for the confusion. I am counting all records that are the same for
PostID. Like in the below scenario, I should have a count of 2 for ID "1", 2

[quoted text clipped - 35 lines]

 

[John Vinson]

I was going to have the update query run on a every time a new record was
created on a form event so maybe I can just try and do it through VBA, but
thanks for helping.

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.

A Totals query will let you dynamically count the records. There is no
need or benefit to storing that count in your table.

John W. Vinson[MVP]