!No Duplicates!

[MyWaterloo]

I have a database for keeping track of samples that come in. I need the Sample ID's to not allow duplicates, but...I don't want to use an auto number. The auto number will not allow me to use a number again even if it is deleted. I need my numbers to be sequential with no duplicates. If I delete a record I want to be able to fill it in with a diff sample. This can't be that complicated...what am I missing? Any help?

Thanks

[ChipR]

Edit the table properties and set the Sample ID field to Indexed(No Duplicates).

[OldBirdman]

Solution 1) Build your table with SampleID as number (not AutoNumber) and make it the Primary Key. You will have to keep track of existing but unused keys.
Solution 2) SampleID as AutoNumber, but have a Deleted field and mark it as True if the sample is no longer valid. You can then overwrite with new sample.
Solution 3) Have an AutoNumber field, and just ignore it. Doesn't have to be the first field. Name it something like "ToKeepAccessHappy". If the fact that it is close to, but not exactly, like your SampleID, make it random, not sequential. Then it is easy to ignore.

OldBirdman

[MyWaterloo]

Thanks all. I'll give some of these a go.

[MyWaterloo]

OK, so I need a little more help. I have changed my ID field from an auto number to a regular number field and have indexed it to prevent duplicates. Works great. But....I still want to keep the auto number feature where the next number in the sequence is automatically entered in the ID field. I don't want to have to look up what the last number used was. I don't know how to reuse deleted auto number record numbers, and I don't know how to make a regular number that is indexed to prevent duplicates automatically insert the next number in the sequence???? Any ideas??? Thanks.

[OldBirdman]

I think you've painted yourself into a corner. If you don't want Access' autosequencing, and you don't want to do it yourself, I don't think it is going to be done.
Your first decision must be a)use Access autonumber or b)do it yourself.
Either can be made to work. Without knowing why you want to have everything sequential with no gaps, I think you are on your own.

[MyWaterloo]

I work at a lab. The lab takes in samples on a regular basis. I have a database that we keep track of the samples with. The "Sample ID" field was an auto number. An auto number worked best because the same person was not always entering samples and didn't know what number sample was last entered. An auto number inserted the next number without the employee having to look up what the last sample ID entered was. The problem with the auto number is if someone deletes a sample record, that number is gone forever. I can't assign it to another sample. At the end of the month/quarter/year the auto number sample ID numbers may indicate 500 samples, but in reality some of those samples may have been deleted/mistakes and we only have 492 samples. Is there now way to prevent duplicates in a record field and have that field insert the next number in a sequence without the field being "locked" as it is with an auto number? Or, is there anyway to "revive" a deleted auto number?

[DonRayner]

You could use a function something like this to get the next available number from your database. Just change the TableName and IDField with whatever the names you are using.

Public Function NumGen() As Integer
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select [TableName].[IDField] From Table4 Order by [IDField]")
rs.MoveFirst
NumGen = rs![IDField]
Do While Not rs.EOF
    rs.MoveNext
    If rs.EOF Then
        NumGen = NumGen + 1
        Exit Function
    Else
        If NumGen + 1 = rs![IDField] Then
            NumGen = rs![IDField]
        Else
            NumGen = NumGen + 1
            Exit Function
        End If
    End If
Loop
End Function

Then place a button on your form with the following code for the click event

Private Sub btnGetIDNum_Click()
txtIDNumber = NumGen()
End Sub

[OldBirdman]

There is no way to revive a deleted autonumber key. Again, if not autonumber, you are going to have to determine the next key for any new record. You are going to have to keep track of numbers made available from error entries. There are many ways to do this, and the choice might depend on the number of samples expected in the database.
You are going to have problems anyway. A record "removed" just before the end of a period (year/month/quarter/whatever) will be in your count if not overwritten immediately with a new sample.
From an audit standpoint, you are creating problems.
The COUNT function in SQL will give you an accurate count of records, whether keys are missing or not, if that is your objection. If you had an "Unused" field in your record, a query using the MIN function could find the lowest key so marked. Likewise, the MAX function against all the records would give the highest key used.

[FishVal]

Hello gentlemen.

As an addition to Don's suggestion I would like to propose SQL approach to find "numeration gaps".

SELECT t1.IDField
 
FROM tblTableName AS t1 LEFT JOIN tblTableName AS t2
  ON t1.IDField=(t2.IDField-1)
 
WHERE t2.IDField Is Null
 
ORDER BY t1.IDField;

This will return dataset where each record [IDField] value has no sequential number in table [tblTableName].

Regards,
Fish

[OldBirdman]

FishVal-Nice SQL, but I can't say I understand it, but it works. Whether there is a recordset with the missing values or one with the present values, code needs to open this recordset and look at it. The missing values recordset might be empty, and then the samples table must be opened for the highest key plus one.
DonRayner's post #8 finds the gaps, or returns the next number. The other issue, of end-of-period reporting, will still have to count the actual rows in the table, as at any time there can be gaps. Autonumber just guarantees that once a gap, always a gap.
I don't like to suggest code until the design is resolved, as it is often presented untested, and is therefore misleading. Although the SQL statement in post #10 by FishVal works, the code in post #8 by DonRayner does not; if the missing numbers are before the first table entry, the function may fail (will fail if no other gaps).
Finally, if records were simply marked as deleted, and then reused, the dataset would not grow as fast, and perhaps compact not needed.

[DonRayner]

Hey OldBirdman. The Function that I posted does work, I made sure of that before I posted it. The function will work because the SQL statement for the recordset opens it ordered on the field that we are checking for gaps in. The loop will step through the recordset until it either encounters a gap or eof and then calculates the next value accordingly.

Although I do rather like FishVals solution.

[OldBirdman]

I didn't actually run the code. I assume IDField has the following values: 3;4;5;7;9; in order. Then:

Line Result
5    MoveFirst-IDField=3
6    NumGen = 3
8    MoveNext-IDField=4 
14   NumGen = 4
8    MoveNext-IDField=5 
14   NumGen = 5
8    MoveNext-IDField=7 
16   NumGen = 6
17   Exit Function
 

But first missing ID is 1, then 2. Am I missing something?

[DonRayner]

Ahhhhhh got ya. My bad, I assumed that the function would be running on a table that had the inital record intact. But if the very first record is missing then the function fails. It should be NumGen = 1 (or whatever the first number is supposed to be) on line 6.

But I still like FishVals SQL solution better.

[OldBirdman]

I wasn't trying to get in any contest over this. I don't understand FishVal's SQL, but maybe I'll study it some more. Unfortunately, if I forget, and open it in design view, Access looses the part that it can't put into the design grid. If I don't notice, and close it, it is gone!
I'm kind of a purest, so line 8 should be moved to the end of the loop, at line 19.5.

[NeoPa]

Guys,

One problem inherent in any "find gaps" algorithm is that it is often not run (cannot be run) at exactly the time that the record is inserted (certainly not if it is also visible to the operator first). Even if it finds the recordset perfectly, it may still cause problems.

The approach that OB was trying to follow is actually perfect here. He was trying to determine from the OP (MyWaterloo) exactly why he was after what he was requesting. That is the crux of the matter (See post #7).

MyWaterloo should appreciate that he was asking the wrong question with his stated requirement. The key field is not a field by which you should try to determine the number of records in the table. The Count() (or DCount()) functions should be used for that. An AutoNumber field was never designed for that purpose, and shouldn't be used in such a way (unless problems are what you're after).

The code and SQL solutions are both interesting, but are probably not good fits for this question.

PS. As Fish's SQL (post #10) is quite informative anyway (there could be a number of occasions where techniques like this can be used) I've just laid it out a little more clearly. Have another look and see if it makes sense now.

[NeoPa]

I just ran through Fish's SQL again and discovered two things (Sorry to pick this to bits Fish. It's a good basis, really.)

1. It lists only the first blank entry (Actually the last valid entry before a blank one) if there are any contiguous records missing.
2. It includes the record after the whole recordset (It will always find the next available record, even if there are no gaps). This can be good or bad depending on what you're looking for.

[NeoPa]

Some SQL code to return the blank entries (as opposed to the ones prior to the gaps), and which excludes the last record as a gap :

SELECT t1.ID+1 AS Blank
 
FROM tblTest4 AS t1 Left Join tblTest4 AS t2
  ON t1.ID=(t2.ID-1)
 
WHERE t1.ID<DMax('ID','tblTest4')
  AND t2.ID Is Null
 
ORDER BY t1.ID

It's easy to see that this is derived from Fish's SQL ;)

[FishVal]

@NeoPa
Those are not bugs, those are features. :D
It was never supposed to do something else.

That reminds me a note on case with crocodile in pet shop:

" Dear customers! The crocodile is alive. His name is Gena. 4 years old. For sale! Eats mouses, meat, fish. Eyes are closed because he is sleeping. He will not jump since he is crocodile. He is not plastic and not marble. He moves when he wants to eat. Price: ..."

[FishVal]

Ok gentlemen.

Just a little fix to the query addressing problem outlined by OldBirdman in post #13. The table before selfjoining is unioned with a record enforcing "starting number existance".

Query: qry0

SELECT 0 AS ID FROM tbl
UNION
SELECT ID FROM tbl;
 

SELECT t1.ID+1 AS NumSeed
FROM qry0 AS t1, qry0 AS t2
WHERE t2.ID Is Null;
 

To determine numeration gaps bounds the following query sequence could be used.

Query: qry1

SELECT t1.ID AS GapBottom, 
(SELECT Min(ID) FROM qry0 AS t2 WHERE t1.ID<t2.ID) AS GapTop
FROM qry0 AS t1;
 

SELECT qry1.GapBottom+1 AS GapBottom, qry1.GapTop-1 AS GapTop
FROM qry1
WHERE [qry1].[GapBottom]+1<>[qry1].[GapTop];
 

Regards,
Fish

[MyWaterloo]

Wow. I haven't check this post since the 7th. Thanks for all the replies everyone. I just have one question... What was the answer to my post? =-) While I am glad this thread has started what equates to an Access philosophical discussion... I'm not quite sure what the final consensus was.

NeoPa: I was not asking the wrong question. I was asking a question from a real world standpoint, and then trying to make that work with Access.

It seems like I will have to choose: the efficiency and rigidity of an autonumber, or, the ease of manipulation of a number field that is indexed to prevent duplicates but deos not have the ability to auto insert the next sequential number.

[ChipR]

The discussion was of ways to get the first unused key number after a record is deleted, so it is assumed that you will not be using autonumber.

[MyWaterloo]

Right. I guess I'm having trouble figuring out what to run? Which post contained the correct code?

[ChipR]

I suggest you use DonRayner's function NumGen() in post #8, since it's the easiest to understand. But in between lines 5 and 6 you need to insert:

NumGen = 1
If NumGen < rs![IDField]  Then
    Exit Function
End If

Or else it will not return the correct number when records are deleted at the beginning.

[NeoPa]

@MyWaterloo
I can't agree I'm afraid. It's obviously your choice whether you agree with, or even consider, the point I made. Clearly I won't suffer from any issues that crop up later.

[ChipR]

@NeoPa
MyWaterloo did ask the correct question. You aparently misinterpreted it to be about counting. If you read his posts fully, you will see that isn't the case.

[Stewart Ross]

Chip, I reckon you may have missed NeoPa's point here in amidst the technical discussion. The problem as outlined by the poster is that the lab is using the autonumber to provide a count of the records - but that count is in error if a sample is deleted, unless that number is obtained and reused. Quote from post # 7:

At the end of the month/quarter/year the auto number sample ID numbers may indicate 500 samples, but in reality some of those samples may have been deleted/mistakes and we only have 492 samples.

Regardless of the technical measures outlined in this discussion, I tend towards NeoPa's view here. There are assumptions underlying this thread, some of which are explicit but some unstated:

* that sample IDs must be in sequence and contiguous
* that the sample ID is a count of the number of samples
* that there is some relationship between the sample ID and a time period, a relationship which is in some way to be preserved if samples are deleted

As NeoPa has indicated, there are other ways to achieve counts which would do away with the need for strict contiguity in sample ID numbers. In particular, assuming that sample dates are recorded it is always possible to use Count() or DCount() to return the number of samples in a particular time period.

The autonumber approach was always going to run into problems, simply because of the self-evident problem that autonumbers cannot be reset back to zero at the start of the next significant time period (for example, at the start of a new year). This begs the question of how sample are truly identified for uniqueness if they must count for a particular time period - is the year involved as part of a compound key, for instance?

Now that an alternative approach has been adopted all of this may well be academic, and I for one do not wish to keep alive an abstruse discussion on technicalities, but for what it's worth I think it is worth pointing out that the assumptions made appear to be about matching sample numbers to counts of samples. If this is essential - and like NeoPa I would suggest considering a different approach - there are other ways to do it. For example, it is possible to maintain a separate sample number table that lists every sample number issued along with the ID of the record concerned, and its current logical status - in use, deleted, whatever - so that sample numbers can be kept contiguous, reused etc as necessary. This would not be the primary key of the sample table, which could remain a simple autonumber as it would never be presented to users as a sample number.

Anyways, there are many solutions to problems, and sometimes they do involve asking questions about questions to ascertain whether or not the assumptions underlying them remain valid in the circumstances.

-Stewart

[ChipR]

Sorry, I did miss the point. Since the last record number can NEVER be used for a count, even if deleted records are replaced, because you can't gaurantee that all records have been replaced, I thought there was some other reason for filling in the gaps.

[NeoPa]

Thank you Stewart.

Sorry it was necessary. I thought I'd explained it clearly enough already. It seems not.

[MyWaterloo]

"ChipR__ Sorry, I missed the point."

Wow. I asked the question and I think I missed my point. :-) So if I understand correctly... I need a diff method for counting my samples than relying on the ID number to be an actual representation of the amount of samples taken? Is this correct? OK, so here is the problem. The lab I work for WANTS the sample ID number that is generated on the report to represent the current total of samples taken. Forget trying to sum the samples for month/quarter. Let's just say the sample ID's will go on forever. Is it then not possible to have my ID's equal the current total? If it is possible, you can see how critical it is to make sure the next sample entered is the correct ID number. Thanks.

There is no better forum on the web that I post on. It happens this is the only forum on the web I post on.... but it's still the best one.

[OldBirdman]

Sometimes the rigidity that is programming demonstrates that the initial design is in error. This needs to go all the way back to whomever is requesting the design. Assume Keys 1; 2; 3; and 4. But key 3 is a duplicate of key 2, entered in error. OK, delete it.
Stop!
Right here is the problem: Key 4 exists. What now? If you don't have a new sample ready to enter, do what? Change key 4 to key 3, even if work has been done as key 4?
Ask the requestor to answer this question is my answer to this issue.
By the way, there seems to be no audit control with the current design request.

[NeoPa]

Mine too. That's spot on OB. You illustrated the issue quite clearly.

@MyWaterloo
I suggest you explain to the requestor the flaw in their assumptions, and ask THEM to indicate how you should proceed.

If you have a soluble problem, we're glad to help. If the question makes no logical sense, there's little else we can do but point this out to you.

Good luck. It's always difficult being put in a position such as yours.

[MyWaterloo]

Thanks OBman. How so no audit control? *Sigh*
"ask requestor to answer this question..."
Most of the time when my superiors have "grand" ideas to make some change to the current record keeping design, I cringe. I am usually able (with my self learned limited knowledge of VB and Access) to make the changes they wish to their satisfaction. The latest idea was to begin with, good. We use to keep track of our samples by month and client. Example: JohnDoe01, JohnDoe02, etc. for the month. Then restart at the beginning of the next month. The new idea was to start at the beginning of the year and number from there with a number as the ID. I actually think this a wonderful idea that will eliminate some potential for confusion. We have never actually worried about knowing the current total of samples taken at any given time. However, this seems to be the latest "idea". "We want the sample to have an ID that represents what number sample it is." And so I am left to implement it. Whenever one of these grand schemes comes along I find myself back here looking for the sagacious advice of the bytes community.

A simple question, how do I total my records? Not using that number for the ID field, but just as a separate field. Any way to keep a current sum of records on my form I use for sample entry? Just knowing the sum even if it is not identical to the ID's may be enough to satisfy.

[topher23]

That's an easy one. Create a text box control on the form and set its control source to either the Count or DCount function. The Count function is appropriate if your data entry form is not bound to a custom recordset or data-entry only, while the DCount function can be used in any type of form since it pulls its count directly form the table/query that you tell it to rather than the form's Recordsource. Look them up in Access Help for usage info.

[OldBirdman]

How so no audit control?

Any time there is a gap in a sequence, the question is "Why"? One complaint about Access autonumber is that you can't fill the missing gaps, as you know.
An auditor might ask, why the gap? Many labs might have to account for samples received, perhaps for legal reasons. You're in NewYork, so you know about the current problems with peanut butter from a manufacturer. Apparently there are lab tests involved. If there were methods you proposed for eliminating gaps, some lab technician might be rich now, and there would be no way to recover any prior data. Any record of receipt of sample is gone - forever.
Were I in your place, I would propose that EVERY sample be given a number. This you could generate from the date and highest number used to date, i.e. 09-00013. After entry, this sample number, client, and entry date would be locked. Can't change, delete, overwrite, etc. Lost or missing samples would be recorded. Or a new record could be created, and the old saved, with the old data. Lab technicians would think they were overwriting data, but for critical datafields, a copy of the old data would be kept.
If technician wanted to delete a record, it wouldn't go away. It would be marked as deleted, but still be available to auditors and management.
The rest of your questions are design & implimentation questions. Start with 2 forms, bound to the same table. frmMeSee will be the data in the table you (and later auditors) want to see. frmTechSee will be the working form for the technicians. Identify controls that will be bound to a table, those that will be calculated from other controls, and those that are independent of a given record of the table, such as sample counts. Design the table to have all identified fields.
Most of the "How To..." stuff is already in this thread.
Good luck!

[MyWaterloo]

As always, thanks everyone for your "free" time that you spend answering my questions. Hopefully I'll have the experience someday to return the favor on some other wandering star in the Access universe.

topher23: Count/DCount that's what I thought. Wanted to make sure I'm not missing something else obvious.

OBman: I see what you mean about the questions that could arise with missing samples. I hate redesigning. =-\ Sometimes it's just the thing to do.