Numbering duplicate records 1

[jonohara]

I have a table of customer orders containing the parcel type and address of the recipient (coming from our access based shopping cart). Sometimes multiple parcels go to the same address. So there are parcels with the same address.

I need to give each parcel a 'part lot number' (for the courier), depending on the number of parcels going to the address e.g when only one parcel is going to an address then the part lot number will be 1. If there are two parcels going to the same address then the part lot number should be 2 etc.

Any ideas

thanks

 

[Blorf]

A real simple query should do the trick.

Select your table or query with your shipping data, include the address, or customer number or what ever makes the location unique, and a calc field, like Count:1 or 1 as count. Then, sum on count.

This will give you a total by location.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[jonohara]

Thanks Blof

I think I didn't explain well enough. If there are 4 parcels to one address I need the first to be 1 of 4 (4 being the count expression you mentioned) the second to be 2 of 4, the third to be 3 of 4 etc. It's the 1,2,3,4 figure I can't work out how to do.

Thanks again

Jon

 

[Blorf]

That kind of thing is really hard with just SQL.

Is the data in a table? If so, then a quick VBA function will do it. Send me your table name, with the Unique Customer field name, and a name of the fields for the numbers, meaning X of Y and I will send you back a quick function.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[willir]

jonohara

In reality you have a one-to-many situation. One address, many (okay, just several) packages. Even though most deliveries will most likely have only one delivery.

You could probably add a number field (integer should work just fine) to your table. Check to see how many packages are going to the same address, and sequentially number the fields. (Always have 1 for the first one)

A better normalized solution would be to have a "lot" table. The lot table would have a record for each package sharing the same address. You will have a primary key on the delivery table. The lot able will need this as the foreign key. The primary key for the lot table would be the foreign key + lot number.

Richard

 

[jonohara]

Thanks for your responses.

Chaz, the data is all in one table (tblOrders), with parcelID (unique), OrderNo (not unique), Add1, PostCode, PartLotNo (this is the tricky one), and LotNo (the 'of 4' bit, calulated by counting the duplicate addresses).

Thanks Again.

Jon

 

[Blorf]

After I sat down to do this, I realized it can be done with just a query.

The query assumes that each field in your table is a text field. Within the Dcount functions are conditions like [Add1]= (and an appostrophie), the actual field, and another apostrophie. For fields that are numeriec, remove the appostrophie. Forgive spelling.

Hope this works for you,
ChaZ

SELECT TblOrders.Ordno, TblOrders.Add1, TblOrders.PostCode, TblOrders.UniqueID, DCount("[Ordno]","TblOrders","[Add1]='" & [Add1] & "' AND [POSTCODE]='" & [POSTCODE] & "' AND [UNIQUEID]<='" & [UNIQUEID] & "'") AS Num, DCount("[Ordno]","TblOrders","[Add1]='" & [Add1] & "' AND [POSTCODE]='" & [POSTCODE] & "'") AS [Of]
FROM TblOrders
GROUP BY TblOrders.Ordno, TblOrders.Add1, TblOrders.PostCode, TblOrders.UniqueID
ORDER BY TblOrders.Add1, TblOrders.PostCode, TblOrders.UniqueID;


Ascii dumb question, get a dumb Ansi