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. ...
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.