Query Enhancement Request

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again,

Yesterday I got some great help on my problem with my query and I would like
to enhance on that a little with your help.

here is the current SQL I'm using...
SELECT tbl_main.cbID, Mid([CBnumRemarks],7,4) AS sort1
FROM tbl_main
GROUP BY tbl_main.cbID, Mid([CBnumRemarks],7,4)
HAVING (((Mid([CBnumRemarks],7,4))>=1))
ORDER BY Mid([CBnumRemarks],7,4);

This takes "CB-01-1101-09-07", breaks it down to just "1101" and sorts it
for me. Which is great, but is there a way to have this "Show" me all the
Missing or out of place data? The data should include all of the nubers from
0001 through ???? and I'm wanting to find out which numbers are missing?
 
Append the data from:

SELECT tbl_main.cbID AS id, Val(Mid([CBnumRemarks],7,4)) AS sort1
FROM tbl_main
WHERE Mid([CBnumRemarks],7,4) >=1
GROUP BY tbl_main.cbID, Mid([CBnumRemarks],7,4)
ORDER BY tbl_main.cbID, Val(Mid([CBnumRemarks],7,4))

into a new temporary table with 3 fields, an autonumber field, plus the two
fields of the query. Assuming that new table is called temp, use:


SELECT id, MIN(sort1), MAX(sort1)
FROM temp
GROUP BY id, sort1-auto


and that will list the number that are USED, such as,


id001, 1, 123
id001, 125, 166
id001, 187, 187
id002, 1, 16

would mean that for id=id001, numbers 1 to 123 are used, 125 to 166 are
used, and 187 is also used. For id=id002, numbers from 1 to 16 are used.




Vanderghast, Access MVP
 
If you mean you want to see the entire field, then include it in the query.

SELECT tbl_main.cbID, Mid([CBnumRemarks],7,4) AS sort1, CBNumRemarks
FROM tbl_main
GROUP BY tbl_main.cbID, Mid([CBnumRemarks],7,4), CbNumRemarks
HAVING (((Mid([CBnumRemarks],7,4))>=1))
ORDER BY Mid([CBnumRemarks],7,4);

If you mean something else, then perhaps you can try to be more specific on
what results you are looking for.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Create a table of numbers 0001 through ????. The easy way is to use Excel
autofill and copy/paste or import int to the table with text field (leading
zeros).
Left join the number table to your query Sort1. You might use a criteria
either a prompt for the top number or DLookup the max.
 
Let me see if I can explain this better... (John I like the code you put that
shows the whole field...)

I have a list of numbers...

CB-02-1100-08-07
CB-01-1101-09-07
CB-04-1102-09-07
CB-06-1103-09-07
CB-02-1104-09-07
etc...

There are CB-??-0001-??-?? through CB-??-1511-??-?? in my DB so far...
I need to know if any of the numbers between 0001 and 1511 are missing?

Some of the data in the DB does NOT have a CB number at all, so ID200 will
not be CB-??-0200-??-??
 
IF all your CbNumRemarks fields start with "CB-02-", then you can try the
following. It should return the missing numbers at the start of the gaps.
For instance if 1011, 1012, 1013, and 1042 are missing, it should return
1011 and 1042.

SELECT M1.cbID
, Format(Val(Mid(M1.CbNumRemarks,7)) +1, "0000") as StartGap
FROM tbl_main as M1 LEFT JOIN tbl_Main as M2
ON Val(Mid(M1.CbNumRemarks,7)) = Val(Mid(M2.CbNumRemarks,7)) + 1
WHERE M2.cbNumRemarks is Null
ORDER BY Mid([CBnumRemarks],7,4);

If you have multiple strings that start the number, I would try to tackle
this with two queries. The first to break the string down into component
parts. The second uses the first as its source.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dispatcher Scott said:
Let me see if I can explain this better... (John I like the code you put
that
shows the whole field...)

I have a list of numbers...

CB-02-1100-08-07
CB-01-1101-09-07
CB-04-1102-09-07
CB-06-1103-09-07
CB-02-1104-09-07
etc...

There are CB-??-0001-??-?? through CB-??-1511-??-?? in my DB so far...
I need to know if any of the numbers between 0001 and 1511 are missing?

Some of the data in the DB does NOT have a CB number at all, so ID200 will
not be CB-??-0200-??-??
--
You want it done by when? *laughs*


Dispatcher Scott said:
Hello again,

Yesterday I got some great help on my problem with my query and I would
like
to enhance on that a little with your help.

here is the current SQL I'm using...
SELECT tbl_main.cbID, Mid([CBnumRemarks],7,4) AS sort1
FROM tbl_main
GROUP BY tbl_main.cbID, Mid([CBnumRemarks],7,4)
HAVING (((Mid([CBnumRemarks],7,4))>=1))
ORDER BY Mid([CBnumRemarks],7,4);

This takes "CB-01-1101-09-07", breaks it down to just "1101" and sorts it
for me. Which is great, but is there a way to have this "Show" me all
the
Missing or out of place data? The data should include all of the nubers
from
0001 through ???? and I'm wanting to find out which numbers are missing?
Click to expand...
Click to expand...
 
Back
Top