Counter based on data category

[chudok01]

I am trying to get a counter to count based on data. column 1 shows what I am needing.

[Welshgasman]

Have you tried Count() and GroupBy ?

[CJ_London]

Need to know more - for example, does the order of the second column matter? I.e in the first group does t matter if 17358 is the third record in the group? Or could it be in a different position?

Reports have the functionality you require but if this is not for a report then the answer to the above question will dictate which direction a solution might take

[chudok01]

Need to know more - for example, does the order of the second column matter? I.e in the first group does t matter if 17358 is the third record in the group? Or could it be in a different position?

Reports have the functionality you require but if this is not for a report then the answer to the above question will dictate which direction a solution might take

Yes the second column needs to be in that same order. This data has to do with recreating the line key for Bill of material. The first column would be the Header item and then second row are the item components that make up this Header BOM. Even if the column is not there I mainly need the count on the first column.

[CJ_London]

Yes the second column needs to be in that same order.

This is not excel, in databases you need a sort order so you need to include a field that specifies that order as simply sorting on it would change it because it is text and sorting text ascending would put the purely numeric values before the none numeric values.

A possible alternative is to use a rownumber function.

See this link and take a look at the code which has such a function

https://www.access-programmers.co.uk...report.326715/

[chudok01]

Have you tried Count() and GroupBy ?

Yes I have, not working for what I need

[June7]

One set has numbers descending (17359, 17358) another set has numbers ascending (15227, 15235). And then numbers are between two values beginning with alpha. This will require complicated code to calculate a sort value. What are the rules for this ordering?

[chudok01]

One set has numbers descending (17359, 17358) another set has numbers ascending (15227, 15235). And then numbers are between two values beginning with alpha. This will require complicated code to calculate a sort value. What are the rules for this ordering?

Attached is a sample of what I can do in Excel. I really need to do the same concept in Access. The items in column B will be sorted and together. Yes we have numerical and alpha in our item numbers. Just feeling that this is really not feasible in Access easily.

[Welshgasman]

Have a look here
https://www.youtube.com/watch?v=2yj6x5E4Z_0

You need a sequential field of some sort to compare against, just as you are doing in Excel.

[June7]

But what dictates that the rows are in that order to begin with?

Records in a db table have no inherent order (bolts dropped in a bucket).

Order of Details values does not follow any intrinsic sorting rules (alpha or numeric). If Details must be in order shown, then yes, this is not easily done. Even Excel has to follow rules when sorting rows.

[chudok01]

I am able to use something like this in excel and it works great =IF(B2=B1,A1+1,1)
I need something as simple as this in Access. These are grouped together in Access so I could get the same result. So if Access cannot do this - then I need to stick with excel. I was just hoping I could do this inside of Access.

[CJ_London]

I need something as simple as this in Access.

as stated in the thread, you need to be able to specify an order - just as your excel file is ordered (you would not get the result you required without it)

So to answer your question, it is simple - providing you have the appropriate data to create the order. In your case, you don't have the appropriate data since in post #1 the details column is in random order after the based on column and in post #4 you have stated the order has to remain the same. Perhaps you have a PK or a timestamp field which would do that.

In post #8 you provide different data and no indication as to whether an order has been applied

The rownumber functions I provided a link to in post #5 provides the same functionality as your excel calculation - i.e. works on the basis of the first column (your column B) changing value to reset the counter.

[NoellaG]

What you need is a windowing function like ROW_NUMBER() or RANK(). I know how to use them in SQL but not the Acces syntax. Maybe someone else on this forum can give you the Access syntax.

[chudok01]

CJ_London,

I am sorry for any confusion. in post one, it does not matter what is in the details column, what I need a counter in is the column called "item counter based on". I need to focus on that field. Yes I did post other data just trying to get the focus on the column that I need to count.

I did look at your post - but it appears that these are on a form. I will need to export the results to excel for data upload into an ERP system. Is there a way to do that with form data?

[Welshgasman]

What you need is a windowing function like ROW_NUMBER() or RANK(). I know how to use them in SQL but not the Acces syntax. Maybe someone else on this forum can give you the Access syntax.

Such a function is in that link that @CJ_London posted?
All the O/P has to do is use that in his query.