Adding a numerical sequence value to a field

[Mattbro]

Hi all,
I’ve a database measuring work process. The user identifies themselves by selecting a button on the Switchboard Form, with their initials which opens up a form-FormAllocate (numbered 1 to 12 depending on which user). This has a blank textbox-“BarcodeNumber”. The user scans the product and the afterupdate event fires the query which matches the barcode on the product, with the list of barcodes from TbBarcode:

INSERT INTO TbData ( [Barcode Number Full], [User], TimeAllocated )
SELECT TbBarcodes.[ Barcode Number Full] "AA" AS User, Now() AS TimeAllocated
FROM TbBarcodes
WHERE (((TbBarcodes.[ Barcode Number Full])=[Forms]![FrmAllocate1]![ BarcodeNumber]));

This gives me the identification of the user, the barcode of the product scanned, and a timestamp indicating when the task was performed on TbData. The barcode number being the primary key.

My problem is, the nature of the work has changed and I need to accommodate multiple identical barcodes on a single batch)-ranging from single items up to 40 items.
I need to code a second value into the process such as a number that increases each time the same barcode is used, resetting to 1 when a new product barcode is entered. E.G.:
19A00001-1
19A00001-2
19A00002-1
And so on.
I had thought of an autonumber but I cannot set a Primary key using an autonumber in a calculated field.
Any advice appreciated.


Mattbro

[Minty]

Is this a count of that barcode number per day, week, year , forever ?
You can use the DCount() function to find out how many of something is in your data.

There are problems with this approach, is this being used in a multiuser environment ? What happens if someones deletes a duff record?

SO unless this is something you really really need think carefully about another possible route to take.

[Mattbro]

Hi. The nature of the workload is that a product can come through the system with several aspects to record. Further work may be required following QC that would require more aspects to record. This could be weeks/months ahead of the first event. Only one user can operate the system at any time from a front end, and users would not be able to delete records. As the barcode is the PK, I need to facilitate multiple instances, so a DCOUNT might not be appropriate.....unless I use the Barcode and the timestamp in a concatenated field as the PK...

[Minty]

You could create a composite key on the barcode. But looking at your data (which I'm guessing is very simplified) I'm not sure you are approaching this from the right angle.

Why not record the events against the bar code (Product) as a child record.
So your product PK in your products table is then related to a child event record.

You can then easily timestamp and count the events in that child table without having to worry about messing with your products identity.
Why change the barcode?

[Micron]

My question is, is this 19A00001-1 of primary importance as data? I've read (but maybe not entirely absorbed) the posts and don't see that it is. What seems to be of primary importance is that you can enter the bar code several times. You've not said if you need to be able to differentiate the 3rd from the 1st as data in any manner. Maybe the fact that you can see all of them is enough. Or, if they are time stamped, you also can know the order without trying to employ a numbered appendix.

I also tend to think there are some approaches that maybe should be changed. F'rinstance, it's generally accepted that pk's should not be meaningful data, which you are doing. Also, wondering if this "(numbered 1 to 12 depending on which user)" means you have 12 forms, which would definitely should be changed (sorry, haven't looked at your db). One day you may find that now they want to allow for multiple concurrent users, which means you're back to the design stage again.

[Mattbro]

Hi-you are right-as such it is not of primary importance and yes, I need to be able to distinguish between the different events on the same serial number. I’ve looked into the meaningless/meaningful PK argument and both aspects have advantages and disadvantages. I have to admit I am using meaningful PKs in order to prevent duplication as I am not sure of other ways of achieving same. Any advice as to how to achieve this would be appreciated. And yes, you are also right-this version does have 12 forms and 12 queries-shortcomings in my own abilities-if there are any pointers as to how to rebuild avoiding this yet still identifying the user, I would be very interested. As it stands however, only one individual can perform this task at any time. Also-thanks Minty-you are probably right into looking at it as a parent child relationship. I’ll look into a rehash.
Thanks all,
M

[Micron]

re 12 forms for 12 users - 1 form, get the credentials of the person opening it. Usual approaches are Environ variables (built in) or a popular function called fosUserName. You need a user data table to support either method, getting whatever you want from it where the Windows user loginID matches.
re advice on how to prevent duplication: you were given that in post 4, although I lean more towards a composite index as opposed to composite PK.
You can get sequential operations on a bar code by time stamping them, although I don't know if the stamp would provide the sort of identifier you want. Regardless, you can avoid using data as PK by employing an autonumber PK field. I've seen cases where data is used as PK's quite successfully on a large scale, but your situation is that
a) you're wanting to concatenate PK data with some suffix, which IMHO it's best to avoid concatenating fields
b) the bar code is intrinsic data that if you don't concatenate, it's duplication prevents it from being a PK field, so it's catch 22.

If you had a parent (main) table with all the data that the bar code represents (its attributes), the child (many) table would be the related records, one for each operation on a bar code.