Incremental Sequential Numbering by Year via Calculated Field

[stormwaterguru]

I think I've seen some advisement to not use calculated fields directly in tables. However, I'd still like to know if there is a way to do the following:

Assign incremental sequential number by Year, i.e. YYYY-DDMM-xxx (xxx being the incremental number within the Year the entry occurs)

The reason I would like to have it calculated in Table is that the data may be imported in rather than interacted with via an Access form.

From what I can tell, DCount would be the way to establish the incremental sequential by year. Being that DCount is not usable in the calculated field of Tables, I don't see a method to do this? It seems like the best I could do is pull date as formatted above and tack on the autonumberID to ensure it remains unique.

Other ideas to execute the ideal formatting seem like they are outside of Table level by utilizing a query or report- would this be correct?

 

[CJ_London]

The advice is to not have a field storing more than one value. Better to just have it as a separate ‘counter’ field and calculate it when the record is saved. The datepart would be the date formatted as required and to which you can concact the counter field when required

As far as importing in, import via an append query to your table rather than directly to the table.

And have to ask what you see as the benefit of what you want to do - you have a text field which will be slower to search/filter than a numeric field - and assuming you could use a calculated field, they can’t be indexed

 

[MarkK]

IMO, a sequence number in table data is a waste. It adds friction to the process of saving a row, and provides zero return on investment.
• Payoff: It does not describe when the item was created. It does not provide any meaningful sort or filter opportunities. It has no relationship to any properties of the object the row describes. It does not uniquely identify a row.
• Payoff = zero.

• Cost: You have to invent the data, it has to be sequential, you have to validate, you have to pick up where you left off, you have to index to avoid duplicates, you have to restart at arbitrary points in time. If users can delete rows you have gaps. If gaps don't matter, see "Payoff:" above. If gaps matter, you must constantly enumerate, find, and insert or re-evaluate.
• Cost = considerable.

What is the payoff in your mind? What problem does it solve?

 

[The_Doc_Man]

Why? The calculation relies on looking at existing data and determining how many records for the group already exist by using dMax() NOT dCount(). But the entire query would be processed in memory before any new row is added so all rows would end up with the same sequence number. If I'm wrong, someone will jump in with the solution.

Pat, I do not believe you are wrong, but the acid test is whether the query executes quickly or slowly. Remember that VBA and SQL are in two different memory environments.

Count, Max, Avg, and other SQL aggregate functions are different from the DCount, DMax, DAvg, and other Domain aggregates. Count() can operate within SQL - but DCount() can't. Oh, it will provide a count - but it had to slip back to the VBA environment to do it, if I recall correctly. I believe the all-SQL way to do this is a sub-query that contains/provides the Max() of the field? Because I recall using something like that for my old check-book tracker.

 

[CJ_London]

OP isn’t clear whether they are appending a single record or multiple records. If the latter you can use a row number function starting from a known position determined using dmax

 

[CJ_London]

Do you have an example using "row number" whatever that is?

see attached.

because this is an append query, it doesn't need to be more sophisticated than it is - only requirement is that a unique value from one row to the next is passed, if it isn't you will get a duplicate.

So if your values are

1
2
3
3
4
you will get a duplicate rownumber on the second 3

and if they were in the order

1
2
3
4
3

you won't get a duplicate row number

I have other versions that are stable in a select query and handle sorting and filtering (with this version, selecting a row triggers a requery, so the numbers will change).

you can see some variations of rownumber here

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

 

[CJ_London]

So modify the dmax to have a criteria to identify the group. I'm not prepared to spend my time on something that is poorly defined without a clear understanding on why it is required.

 

[CJ_London]

No idea what you mean. Provide some data and the expected result from that data

 

[CJ_London]

Did you try the app I uploaded?

 

[June7]

No, this cannot be accomplished by a simple calc in table.

Generating custom unique identifier is a fairly common topic. I had to do this for tracking laboratory samples. Samples are numbered sequentially for each year. The numbers had to be accounted for so no gaps and code deals with that. Data was not imported so number was assigned when user initiated new record. Review https://www.accessforums.net/showthread.php?t=23329

Assigning this new data to imported records can be accomplished with or without VBA.

I tested this mod to CJ's query and it worked: IIf(Year(Date()) <> Year(SaleDt),DMax("cnum","table6"), 0)+rownumber([rep] & [sale]) AS Expr1
Then INSERT without rownumber() - had to add a PK field to Table3:
INSERT INTO Table6(rep,sale,saledate,cnum)
SELECT rep,sale,saledt, Nz(DMax("cnum","table6","Year(SaleDate)=" & Year(SaleDt) & " AND PK<" & [PK])+1,1)+DCount("*","Table3","Year(SaleDt)=" & Year(SaleDt) & " AND PK <" & [PK]) FROM Table3;

An UPDATE action so Table3 is not needed but using rownumber() again:
UPDATE Table6 SET cnum = IIf(Year(Date()) <> Year(SaleDate),DMax("cnum","table6"), 0)+rownumber([PK])
WHERE cnum IS NULL AND (((rownumber(-1))=False));
Now UPDATE without rownumber() function (tested with 3 years of data):
UPDATE Table6 SET cnum =Nz(DMax("cnum","table6","Year(SaleDate)=" & Year(SaleDate) & " AND PK<" & [PK])+1,1)+DCount("*","Table6","Year(SaleDate)=" & Year(SaleDate) & " AND PK <" & [PK] & " AND cnum IS NULL")
WHERE cnum IS NULL;

Since domain aggregates in SQL can really slow performance, VBA recordset might be more efficient.

Could also run UPDATE on entire table to assure no gaps but if this number has meaning to people, that can negate usefulness.

Alternative is to use RunningSum property of textbox on report, again deleting or sorting records would reassign sequence and might lose usefulness

Several assumptions involved and we really need more info.

 

[CJ_London]

Maybe the OP can figure out how to make it work for his "over group" situation.

It is perfectly doable by modifying the rownumber function and ensuring the query is sorted by 'group'. But without data and a clearer understanding of the requirement, not prepared to spend time providing further example.