Copying Data?

[Guest]

I don't know if this is possible in Access, but here's my problem:

I've got a table ('AutoNumb', 'Parcel', 'Size', 'Count', '#') with a list of
parcel numbers in one of the fields. Some of the parcel numbers have
duplicates. I added a field 'counts' that holds a count of the parcel
numbers.

Now, if there are three or more of a parcel, i need to number the first ones
as 01, the second as 02, the third as 03, etc...

I can run queries to make a new table ('Count', '#') with all record counts,
number these all "01", then all records where count is over 1, number these
"02", count over 2, etc.

I need to get the #s from the new table to the original table. Update query
will not work because count is the only related field. Is there a way to
automatically (VBA) copy the '#' field from new table to the old table? The
only way i can get the data over is highlight the entire column and paste it
into the table.

Any help is greatly appreciated,

-Matt

 

[Tom Ellison]

Dear Matt:

You want to assign values to the new column "counts" based on a query you
have that gives these values. Please post that query so we can see how you
generate the unique "counts" for each Parcel.

Tom Ellison

 

[Guest]

I made Count and # datatype text in both tables.

Then I built an index of Parcels.Count & Parcels.# and set unique on and
ignore nulls. Then I run this query. I had to repeat the query as many
times as was the maximum count.

UPDATE Parcels INNER JOIN [Parcel Count] ON Parcels.Count = [Parcel
Count].Count SET Parcels.[#] = [Parcel Count].[#]
WHERE (((Parcels.[#]) Is Null) AND ((Val([parcel
count].[#]))<=[parcels].[count]));

This was the test data.
Parcels --
Autonum Parcel Size Count #
1 1 1 3 03
2 1 1 3 02
3 1 1 3 01
4 2 2 2 02
5 2 2 2 01

Parcel Count --
Count #
3 01
3 02
3 03
2 01
2 02