Finding numbers not used in sequential list

[THWatson]

I have a database which has a field called "Envelope Number." There are 5 different sequences - from 1 to 300 series, a 700 series, a 900 series, a 3000 series and a 5000 series.

From time to time a person who has a number in one series is reassigned a number from another series, leaving their old number available to given to someone else. (e.g. Fred Smith has 294 but his number is changed to 3008, leaving 294 open to be given to someone else.)

Is there a system (query, lookup box, whatever) that will quickly provide the numbers that are free to be assigned in each of the sequences?

Tom

 

[WildHare]

Yes. Build a table, and populate it with just the numerical sequences you have, eg 1 to 300, 701 to 799, etc etc...

Then, start a new query. Bring both tables in to it. Drag a connector from the NUMBER field in your second table, to your number field in the main table. Set the JOIN PROPERTY to #2, so you get all records in Table2 and any matching records in Table1.

Then drag your table1 key, name, number and other info you want into the grid. Set the CRITERIA for the NUMBER field in TABLE1 to IS NULL. Drag the NUMBER field from the new table2 in to the grid too. Run the query.

You'll get a list of the records in table 2 for which there is NO MATCH in Table 1, in other words, all the numbers that aren't in use. Right Outer Join.. very handy...

"Remember, you're unique - just like everyone else"
Feel free to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[BillMc]

What I would do is create a query that will be the souce for a combo box. In that query you will create an expression that looks something like this:

IIF([Name]=" ",1,0) I would then use "1" as my sort criteria. Therefore only those records with no text would appear on the datasheet. Hope this is helpful. Bill

 

[THWatson]

Thanks, Jim and Bill, for your replies.

Jim... I can't get your Query to run if I include the "IsNull" in the criteria. What it generates is a Data Type Mismatch in the criteria error. If I run the Query without the "IsNull" I get two lists, one having all the numbers and where there is a missing number in the primary table there appears a blank.

Bill... I can't get your method to work either. My Query won't accept the expression "IIf([Env #]=" ",1,0) and there is no place in the Sort for "1", only not sorted or ascending or descending. Perhaps I am misunderstanding something.

Tom

 

[WildHare]

Thomas - are both of your "number" guys NUMBER data types? Or is One a TEXT guy? That's what would generate the Data Type Mismatch. I set this up in a test database and it worked like a charm, so long as both "Number" guys were NUMBER types...

"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[WildHare]

Just noticed something - did you use IS NULL in your criteria, or "ISNULL(something)"... you want IS NULL (two words)

The DTM would have come up as soon as you tried the join, so that's not it. DUH...getting late here...

"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[THWatson]

Jim...you got it...late or not. Both data types were numbers. But, you were absolutely correct in that I was using "IsNull" rather than "Is Null" (two words). Soon as I changed to that, it worked perfectly.

Thanks.

It's getting late here too, past 1 a.m., but this was bugging me, so I'm glad I stayed up long enough to get your solution.
Tom

 

[WildHare]

Glad it worked - you had planted the seeds of doubt in my somewhat fertile imagination.

Once you figure out how they work, outer joins can be the among the most useful relational operations, as you've seen.

"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[LMRollins]

The unmatch query works great for me also but I need to through a curve in it. Let's say I have a table with page numbers 1 through 600. This way when I compare it to my other table it tells me if a page is missing. Here's the curve: Each page can only have 26 lines or entries. How can I get it to also check to see if there are any lines missing per page?