Dcount Help please

[rede96]

I have a query where I want to add sequential counting to all items that are part of a group. I have grouped the query on MaterialCode and want to add a sequencial counter for all orders that have been assigened to a materialcode

For Example:

Key MaterialCode Order Counter
abc1 111111A 987654 1
abc2 111111A 687654 2
abc3 222222B 655511 1
abc4 333333A 251544 1
abc5 333333A 555555 2
.....


Can anyone help me with the right method please.

 

[plog]

aWhat's the hierarchy for ordering? I see that you are grouping similiar Material Codes together. From there what field determines order?

In general you will create a query with your data then have a calculated field based on a DCount which counts the number of similiar items that aren't as high as the current one based on your ordering field:

 

[rede96]

aWhat's the hierarchy for ordering? I see that you are grouping similiar Material Codes together. From there what field determines order?

In general you will create a query with your data then have a calculated field based on a DCount which counts the number of similiar items that aren't as high as the current one based on your ordering field:

I'm not sure what you mean?

The fields I am counting are the number of different Production Orders that apear for the same Material code. So if I have 5 different production orders that are linked to one material number then I'd like to count them 1,2,3,4,5 ...

I have found some examples of where DCount works with an autonumber ID, but my ID is alpha number as it is a cimbination of Production Order, Material and Data as that is the only way to have a unique ID for each record.

 

[plog]

To assign an order number (which is what you are asking) you need a way to determine the order. Suppose we were ordering all families children oldest to youngest. We would first group them by family, then we would assign them a number based on their birthdate (the order field), the one with the earliest birthdate would get 1, the most recent birthdate would be assigned the last value.

To assign order, you need a field to determine that. Which is yours?

Follow up question--what field type is that field?