Forum Discussion
Unique Field Format for a Calculated Field
I am trying to create a new Calculated Field in a table that populates the field in a specific format.
The expression I am using is [Intake Date] & [Intake Count] is for a Field I named Intake ID. The new field will be used as the Primary Key for the Table which basically counts the record entries for a given day and converts them into a reference number. To illustrate an example, I want the combination of the Intake Date input of 2/14/2022 and the Intake Count (an AutoNumber) of 1 to read as 202202140001 in the Intake ID Field. This represents yyyymmdd####. I have played around with it a bit, but cannot get it to display the way I want. Any suggestions or examples would be greatly appreciated.
Bad juju tends to follow designs which calculate values based on other fields and store that calculation somewhere. Worse juju follows from trying to make that be a Primary Key.
A much more reliable, stable, approach will be to create a basic, default Access AutoNumber field in this table. Make THAT the primary key for the table.You can do the calculations for daily counts simply by running an aggregate query on the table, grouped on [Intake Date].
SELECT [Intake Date], Count(PrimaryKeyField) AS [Intake Count]
FROM tblYourTableNameGoesHere
GROUP BY [Intake Date]
ORDER BY [Intake Date];
There is no reason to store that count as a calculated value in this table or any where else. It's easily calculated for DISPLAY when needed.
