populating all matching fields with a number

[Boomshank]

Hello,

I have a table of our customer orders which contains the following fields:

Client Code
Client Name
Output
Date
Count
Post Code (Ascending)
Run No.

Different clients share the same post code so what i would like to do is on each date, every client that shares the same postcode should be given the same run No. Then at each change in post code the run no. is to increase by 1.

so if on the 1st June 3 clients have the same postcode all three should be given the same run number. i.e Run1, then the next set of postcodes need to be run2

Is this possible? If so how do i go about doing it?

Many Thanks

 

[John Big Booty]

Have a look at the attached DB, particularly query QRY_EmplSort. It should get you started.

 

[Galaxiom]

Normalization alert. The run number should not be stored in the ClientOrders table.

RunNumber should be stored in a separate RunNumbers table with RunNumber, RunDate and Postcode fields. The RunNumber for an Order should be determined through the relationship between ClientOrders and RunNumbers tables joined on Postcode and RunDate.

This requires a single record per Postcode and RunDate that applies to every order matching that postcode and date. These two attributes define the RunNumber. A RunNumber stored in the Orders table could conflict with this definition and is hence a breach of normaization.

There may also be a normalization issue with the Postcode field being included in the ClientOrders table. Assuming it repesents the delivery postcode this would be determined through a relationship with the DeliveryAddress table which would be joined to Orders through something like ClientAddressNumber and ClientID.

BTW. I strongly advise you to drop the spaces in your field and table names. It just make more work having to manually type the [] around everything.

 

[Boomshank]

Thanks Galaxiom

What i did to solve this was

Create a new table - CustomerAddress which held the ClientCode and PostCode.

I created a relationship between my CustomerOrder and Address tables using the ClientCode.

I then created a query that shows the orders and postcode which i called GetPostcode

Next i created a query that shows all of the duplicates for date and postcode from my GetPostcode query. I then appended this data to a new table which i called RunNo and set the RunNo. field to Autonumber.