range of numbers

[Russ]

I have a table that holds numbers (access codes) for members of a
gated community. The numbers are limited to 4 digits and I've
selected some of them for codes. I would like to be able to create a
report that lists all possible numbers plus the ones I've selected
highlighted somehow in the report. Should I create a "dummy" table to
hold all possible numbers and then manipulate the two tables somehow
with queries?

Russ

 

[mscertified]

Why do you need a report to show you all possible numbers? Since you say the
numbers must be 4 digits, the possible numbers are 0000 to 9999. If you need
to know which ones are allocated, just list them. It would be fairly simple
to write a routine to print the unallocated numbers as ranges, that would
save a lot of paper e.g.
0000-0050 Unallocated
0051 Allocated to Jim
0052-0065 Unallocated
0066 Allocated to Jane
etc. etc.

-Dorian

 

[Russ]

I like to keep the assigned codes separated somewhat and to avoid
unintentional keying of a valid code. "Hey look what I just stumbled
into... a valid code by missing my own code entry by a digit!"

I would rarely print them but just select a new one off the screen.

 

[Russ]

I will work on deciphering your suggestions this afternoon.

If I have the two tables, one with all possible codes and one with the
assigned codes, is there a way to differentiate the assigned codes on
an all possible codes report?

Russ

I have a table that holds numbers (access codes) for members of a
gated community. The numbers are limited to 4 digits and I've
selected some of them for codes. I would like to be able to create a
report that lists all possible numbers plus the ones I've selected
highlighted somehow in the report. Should I create a "dummy" table to
hold all possible numbers and then manipulate the two tables somehow
with queries?

A permanent auxiliary table of to hold all possible numbers is a good
idea. You could set up DRI ('Relationship with RI enforced') between
the tables e.g.

CREATE GateCodes (
gate_code CHAR(4) NOT NULL UNIQUE,
CHECK (gate_codes LIKE '[0-9][0-9][0-9][0-9]'
)
;
CREATE Members (
gate_code CHAR(4) NOT NULL UNIQUE
REFERENCES GateCodes (gate_code),
last_name VARCHAR(35) NOT NULL...
...
)
;

or you could just use it to identify unallocated codes e.g.

SELECT G1.gate_code,
IIF(M1.last_name IS NULL, '{{UNALLOCATED}}', M1.last_name) AS
code_holder
FROM GateCodes AS G1
LEFT JOIN Members AS M1
ON G1.gate_code = M1.G1.gate_code
;

BTW I think Dorian's idea for a report is good too.

Jamie.

 

[Russ]

I did try your code since I never fiddled around with SQL much and it
gave me several errors.

It does lock out after some invalid codes are tried, but if you look
at a list of numbers from 0001 to 9999 and only about 120 selected
from that list as valid, that leaves alot of space in between.