Count each record in group, update table

[hovercraft]

How could I loop through each value in a table and count each record with a unique value in that field then update the table?

Here is what I would like the table data to end up as:

my_zip my_count
12345 1
12345 2
12345 3
12345 4
98765 1
98765 2
98765 3
98765 4
98765 5
98765 6
98765 7
98765 8
22222 1
22222 2
33333 1
33333 2
33333 3

So that for each unique zip code there is a count in order .

Does that make sense?

Thanks in advance,
Hovercraft

 

[dhookom]

Not really possible without specifying an "order" in your "count in order". You must have a field that identifies the order within each My_Zip value.

Normally you don't want to update the table since records might be added, deleted, or updated.

Duane
Hook'D on Access
MS Access MVP

 

[hovercraft]

Thanks dhookom,

The table is a temp table, the number of records will not change. How about order by my_zip?

I was thinking of using a function to create a recordset of the table and for each field value check to see if it's = or <> the previous value checked (by way of a global variable), if it's the same then i+1 ... kindof thing.
but this seems cumbersome.

Any ideas?

 

[dhookom]

You could create code that would open a recordset of your table and order by My_Zip. The code could then step through the records and update them one by one to the correct value.

This would not be possible with SQL since you haven't identified which of the same values of My_Zip get numbered 1 and which record is 2 etc.

Duane
Hook'D on Access
MS Access MVP

 

[hovercraft]

Thanks Duane,

The list of zips is ordered by zip. The first one in the record set would be = 1 and the second = 2 and so on until the zip then changes at which point the numbering would start over.
I guess "count" would be an incorrect term, as I'm really numbering the records with equal my_zip values.

Perhaps I could just create a new field for all the records in the table each with a value = 1 and then do a running sum.

What I'm REALLY trying to get to is limiting the number of records with similar zips on a report page, I.E. the first page list 6 of 1 value and if there are more than 6 records for the zip then the next page would list the next 6 and so on if that makes sense???

but first I need to figue out how many there are of each zip and number them in sequence for each zip.

Any thoughts?

 

[PHV]

What I'm REALLY trying to get to is limiting the number of records with similar zips
Why not use an aggregate query like this:
SELECT my_zip, Count(*) AS my_count
FROM yourTable
GROUP BY my_zip

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You don't have or at least haven't shown us a field or value that determines "The first one in the record set". Records are like marbles in a cloth bag. There is no first or second or third. You may have different colors of marbles (zip codes of records) but you would need to define a solution that would identify which red marble was first. The solution would have to generate the same red marble as the first no matter how many times I grabbed for a red marble even if the bag was shaken.

Why not simply set the report to only display 6 records on a page which would force a page break?

Alternatively, you could group your records by ZipCode and add a text box:
Name: txtCountZip
Control Source: =1
Running Sum: Over Group
Visible: No
Add a page break control at the very bottom of the detail section:
Name: PgBrk6
Then add code to the On Format event of the Detail Section:

  Me.PgBrk6.Visible = (Me.txtCountZip Mod 6 = 0)

Duane
Hook'D on Access
MS Access MVP