!No Duplicates!

[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]

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.

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]

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.

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]

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

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.