Finding missing No

[sbaud2003]

I want to find all the missing IDs in a table starting from 1 to the end of the Table. How can i do that and display the missing IDs
IDs are Primary and serially numbered. however one missed the seriality in entering.

 

[bob fitz]

Are the ID's of "Auto-Number" type

 

[sbaud2003]

Are the ID's of "Auto-Number" type

yes

 

[ebs17]

Something can only be missing if you have an idea of completeness. Therefore you would have to compare your table against a complete table.

SELECT N.Num
FROM NumberTable AS N LEFT JOIN YourTable AS Y
ON N.Num = Y.ID
WHERE Y.ID Is Null

 

[bob fitz]

yes

Then why would you need to know which are missing. They should have no meaning to a user.

 

[jdraw]

Here is some info about autonumbers from UtterAccess.

What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers.
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table.
12. Indicative of or related to any TimeStamp field that may also be in the table row.

What they are:
1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access.
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row

 

[arnelgp]

however one missed the seriality in entering.

Autonumber can easily be "missed", when a users enters a New record and start typing and eventually bail out and discontinue adding the record.
it is not similar to a Cheque number where a missing 1 is very impotant.
what matters is that our teeth should not be gapped!

 

[xavier.batlle]

I want to find all the missing IDs in a table starting from 1 to the end of the Table. How can i do that and display the missing IDs
IDs are Primary and serially numbered. however one missed the seriality in entering.

A simple way to do it would be:
Create a table with all the Id possibles (for example in Excel and attach that table)
Create a query with not matching records as @ebs17 has posted.

 

[GPGeorge]

Piling on a bit, I know.

If gaps in a sequence of AutoNumbers is a problem, you've been doing it wrong.

I love the analogy to teeth, btw. Just a couple of weeks ago, I had to have some teeth extracted. The temporary gaps are a hassle, but I know exactly where they are.

There are multiple gaps in AutoNumbers (or Identity values in SQL Server tables) in every database I have, but I have no idea where the gaps are and what values have been lost. And I don't even care!

 

[gemma-the-husky]

Just write a quick VBA function to iterate the numbers and report the gaps.

You shouldn't expect an intact sequence with an autonumber. If you want an an intact sequence you need to manage the numbering yourself.

 

[Pat Hartman]

If you need to avoid gaps, you need to start by preventing deletes. Then you need to generate your own sequence numbers. Assign them as the last line of code in the form's BeforeUpdate event and use a loop to capture duplicates in case another user is trying to insert records at the same time and you have a conflict.

 

[sbaud2003]

Thanks to all for your advise

 

[CJ_London]

Provide some example data to illustrate the issue

 

[CJ_London]

that's a start, tables - no data

yes you can have multiple tables in one query, but at the moment I am unable to determine what you want the query to return to say that in this case it can be done (other than by using a union query) - mock something up in Excel. I presume this is for a report so the query does not need to be editable.

Based on the sql you provided I would say you want to return all collar records for 2024 where there is not a record in alterations - but when you extend the query to include other tables do you want to return all collar records where there is not a record in alterations AND not one in Lithology AND...etc ? or where there is not a record in alterations OR not one in Lithology OR...etc ?

 

[Mark_]

Please let me know if this is what you are really trying to do.
"I have a table that has 4 child tables. For each record in the parent table, I need to identity if they are missing a child record in each of the child tables".

If yes, are you looking for a query that returns "Parent table / Missing child table" or a query that returns "Parent table / Missing table 1 / Missing table 2 / Missing table 3 / Missing table 4".

 

[mike60smart]

Ok, I have 3 tables... well 5 actually(Sorry!)
Collar
Alteration
Lithology
Mineralization
Structure

Collar has all the Unique IDs (this is the main table)
others have different datas, but the main thing that is common is the ID

I Filter ALTERATIONs to show only the IDs that is not in COLLAR; like this

SELECT Collar.HoleID, Collar.DH_Year, Collar.DH_Geologist
FROM Collar LEFT JOIN Alteration ON Collar.HoleID = Alteration.HoleID
WHERE (((Collar.DH_Year)>=2024) AND ((Alteration.HoleID) Is Null));

I do this code to all the others. So as you can see i Have so many repetitive Queries
My goal is to make 1 single Query with all the Alteration, Mineralization, Lithology, Structure in it.
that is why I asked if the code on the previous post is possible if you have more than 2 tables

Can you show us a Screen Shot of your Table Relationships?

 

[Mark_]

So from your description, you are going to want to make a query where you use left joins to your parent table.

I would also start by making a query for EACH child table that will return either 0 or 1 record (select top 1 from a sort order such as date) to give you what you have above. As you have year in there I am guessing you are trying to ONLY get results from 2024 and you are not worried about results from 2023?

After you have the query from each that only returns the record you want, you can then create your single query that is based on your parent table but uses left joins to link in the child records.

This would be something like
SELECT TblHole.HoleID, TblHole.DrillDate, TblCollar.HoleIDFK (Specifying the parent ID saved as a foreign key in the Collar table), TblCollar.Geologist,
FROM TblHole
LEFT JOIN TblCollar
ON TblHole.HoleID = TblCollar.HoleIDFK;

I would also make sure that the HoleID and HoleIDFKs are indexed. HoleID should be an autonumber (unlike the sample data you posted) and HoleIDFK in each child should be a copy of the parent's HoleID.

Get it built one table as a time, adding in the next until you have the data you need.

Now for normalization, it looks like your FOUR tables are really one, but you need to have an additional field for if they are "Alteration, Lithoogy, Mineralization, or Structure". Normalizing your data will help a lot. I'd also have those four values in a separate table and used as a look up. Then if you need to add more, you just add a record to your lookup table instead of creating a new table with an identical structure to an existing one.

 

[plog]

I do this code to all the others. So as you can see i Have so many repetitive Queries
My goal is to make 1 single Query with all the Alteration, Mineralization, Lithology, Structure in it.

You need to not make a single query, but a single table with those values.

Why not partition your tables by geologist as well? Alteration_RMC, Alteration_GJP, Lithology_JDM, Mineralization_KAR, etc etc.? Why not partition your tables by year as well? Alteration_RMC_2023, Alteration_RMC_2024, Lithology_JDM_2025, Mineralization_KAR, etc etc.?

So, why did you partition your tables by Category (Alteration, Lithology, Minerlaization, Structure? Why does those become their own tables when other data gets thrown into 1 field and seperated by their values?

Those 4 tables need to become 1 table, with an additional field, [Category] in which you store the values which you are now using as table names. I think you do that, you either avoid whatever issue you were having that inititated this post, or it becomes trivial to achieve.

 

[CJ_London]

I would also suggest you do not need year in the combined table since that is a value in your collar table