Query error - Missing numbers - Trying to modify

A

AHopper

In Table "OneGPackData" there is a field
for "UniqueLabel". When all entries are made these labels
should be consecutive and have now labels numbers missing.
I am trying to build a query that will check for missing
labels. In March, 04 Tom Ellison (Microsoft Access MVP)
sent me a query that does identify the missing labels.

Original query:
SELECT UniqueLabel + 1 AS BeginSeries, (SELECT MIN
(UniqueLabel) - 1 FROM OneGPackData T1 WHERE
T1.UniqueLabel > T.UniqueLabel) AS EndSeries
FROM [SELECT DISTINCT UniqueLabel FROM OneGPackData]. AS T
WHERE NOT EXISTS (SELECT * FROM OneGPackData T1 WHERE
T1.UniqueLabel = T.UniqueLabel + 1) AND UniqueLabel <
(SELECT MAX(UniqueLabel) FROM OneGPackData);

However, the query searches all jobs and records (43,654
records)in the table "OneGPackData".The query takes 3 to 4
minutes to run and contains so much information that when
I try to move to a different page (Part) of the query my
computer freezes.

I now have a form "frmdOneMissingUniqueReport" which has a
combo boxes for "JobNumber", "StartUniqueNumber"
and "EndUniqueNumber". (At the end of this post are the
modifications I have tried to make.) I am trying to limit
the search to one "JobNumber" and see if there are
missing "UniqueLabel" numbers in a defined range
between "StartUniqueNumber" and "EndUniqueNumber" selected
by the user on form "frmdOneMissingUniqueReport". When I
try to run the query I get the following message:
"The Microsoft Jet database engine cannot find the input
table or query 'UniqueLabel'. Make sure it exists and that
its name is spelled correctly."

There should not be a table or query named 'UniqueLabel'.
The table is "OneGPackData".
The query is "QryMissingUniqueOnePosition"
The form is "frmdOneMissingUniqueReport"

Modified query:
SELECT UniqueLabel+1 AS BeginSeries, (SELECT MIN
(UniqueLabel) - 1 FROM OneGPackData T1
WHERE T1.UniqueLabel > T.UniqueLabel) AS EndSeries
FROM [SELECT DISTINCT UniqueLabel, JobNumber FROM
OneGPackData
WHERE UniqueLabel BETWEEN Forms!
frmdOneMissingUniqueReport!StartUniqueNumber AND Forms!
frmdOneMissingUniqueReport!EndUniqueNumber AND JobNumber =
Forms!frmdOneMissingUniqueReport!JobNumber]. AS T
WHERE NOT EXISTS (SELECT * FROM UniqueLabel T1
WHERE T1.UniqueLabel = T.UniqueLabel + 1) AND
UniqueLabel <(SELECT MAX(UniqueLabel) FROM OneGPackData)
AND UniqueLabel<=Forms!frmdOneMissingUniqueReport!
EndUniqueNumber;

Thank you in advance for your time and help.

Allan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top