WHERE -IIF ?

[Guest]

I have tried to post this as a WHERE and or IIF statement question. It should
be not this difficult a query. I took my main table NameAddr this file
contains all of our customers with assigned customer numbers. Each branch has
a range of usage between 1 and 9999. Branch 01 only uses 1680 that leaves
8319 number assignable. I created a 1-9999 table and right joined them, this
should give me all the numbers “USED†and all the numbers LEFT OVERâ€

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumberAssinged, CustomerN.CustomerNumbers

FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =
CustomerN.CustomerNumbers


WHERE (((NAMEADDR.[co-number])<>"Is Not Null" And
(NAMEADDR.[co-number])<>"02" And (NAMEADDR.[co-number])<>"06" And
(NAMEADDR.[co-number])<>"07" And (NAMEADDR.[co-number])<>"08" And
(NAMEADDR.[co-number])<>"14" And (NAMEADDR.[co-number])<>"21" And
(NAMEADDR.[co-number])<>"13" And (NAMEADDR.[co-number])<>"22" And
(NAMEADDR.[co-number])<>"03" And (NAMEADDR.[co-number])<>"04" And
(NAMEADDR.[co-number])<>"05" And (NAMEADDR.[co-number])<>"10" And
(NAMEADDR.[co-number])<>"11"))

ORDER BY [NAMEADDR].[co-number], [NAMEADDR].[cust-number];

 

[Marshall Barton]

I have tried to post this as a WHERE and or IIF statement question. It should
be not this difficult a query. I took my main table NameAddr this file
contains all of our customers with assigned customer numbers. Each branch has
a range of usage between 1 and 9999. Branch 01 only uses 1680 that leaves
8319 number assignable. I created a 1-9999 table and right joined them, this
should give me all the numbers “USED” and all the numbers LEFT OVER”

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumberAssinged, CustomerN.CustomerNumbers

FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =
CustomerN.CustomerNumbers


WHERE (((NAMEADDR.[co-number])<>"Is Not Null" And
(NAMEADDR.[co-number])<>"02" And (NAMEADDR.[co-number])<>"06" And
(NAMEADDR.[co-number])<>"07" And (NAMEADDR.[co-number])<>"08" And
(NAMEADDR.[co-number])<>"14" And (NAMEADDR.[co-number])<>"21" And
(NAMEADDR.[co-number])<>"13" And (NAMEADDR.[co-number])<>"22" And
(NAMEADDR.[co-number])<>"03" And (NAMEADDR.[co-number])<>"04" And
(NAMEADDR.[co-number])<>"05" And (NAMEADDR.[co-number])<>"10" And
(NAMEADDR.[co-number])<>"11"))

ORDER BY [NAMEADDR].[co-number], [NAMEADDR].[cust-number];

Your use of Is Not Null is incorrect and you could reduce
the list of excluded customers can be much shorter:

WHERE NAMEADDR.[co-number] Is Not Null And
NAMEADDR.[co-number] Not IN("02","06","07","08","14",...)

If the co-number field is a numeric type (not Text field),
then do not use quotes around the numbers.

However, that list of excluded customers kind of implies
something is odd in your table design. Maybe you should
have another table with the excluded customer numbers and
join that table in the query.

 

[Guest]

Marshall,
The NameAddr is the file that has all the numbers that are used or excluded.
However, even thought the range is between 1-9999 an assigned number might
only go as high as 6005. The reason I am bulding this DB is to keep track of
the numbers and not used. I built a table with the range of 1-9999 this is
the table I joined to the NameAddr table. I thought by right joining it would
give me in field1 all the numbers used and in field2 all the numbers not
used. The reason for such a Large list is I did not exclude all the branches
at first, it kinda keep going.......

I have tried to post this as a WHERE and or IIF statement question. It should
be not this difficult a query. I took my main table NameAddr this file
contains all of our customers with assigned customer numbers. Each branch has
a range of usage between 1 and 9999. Branch 01 only uses 1680 that leaves
8319 number assignable. I created a 1-9999 table and right joined them, this
should give me all the numbers “USED†and all the numbers LEFT OVERâ€

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumberAssinged, CustomerN.CustomerNumbers

FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =
CustomerN.CustomerNumbers


WHERE (((NAMEADDR.[co-number])<>"Is Not Null" And
(NAMEADDR.[co-number])<>"02" And (NAMEADDR.[co-number])<>"06" And
(NAMEADDR.[co-number])<>"07" And (NAMEADDR.[co-number])<>"08" And
(NAMEADDR.[co-number])<>"14" And (NAMEADDR.[co-number])<>"21" And
(NAMEADDR.[co-number])<>"13" And (NAMEADDR.[co-number])<>"22" And
(NAMEADDR.[co-number])<>"03" And (NAMEADDR.[co-number])<>"04" And
(NAMEADDR.[co-number])<>"05" And (NAMEADDR.[co-number])<>"10" And
(NAMEADDR.[co-number])<>"11"))

ORDER BY [NAMEADDR].[co-number], [NAMEADDR].[cust-number];

Your use of Is Not Null is incorrect and you could reduce
the list of excluded customers can be much shorter:

WHERE NAMEADDR.[co-number] Is Not Null And
NAMEADDR.[co-number] Not IN("02","06","07","08","14",...)

If the co-number field is a numeric type (not Text field),
then do not use quotes around the numbers.

However, that list of excluded customers kind of implies
something is odd in your table design. Maybe you should
have another table with the excluded customer numbers and
join that table in the query.

 

[Vincent Johns]

tmaxwell wrote:

I have tried to post this as a WHERE and or IIF statement question. It should
be not this difficult a query. I took my main table NameAddr this file
contains all of our customers with assigned customer numbers. Each branch has
a range of usage between 1 and 9999. Branch 01 only uses 1680 that leaves
8319 number assignable. I created a 1-9999 table and right joined them, this
should give me all the numbers “USED” and all the numbers LEFT OVER”

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumberAssinged, CustomerN.CustomerNumbers

FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =

CustomerN.CustomerNumbers


WHERE (((NAMEADDR.[co-number])<>"Is Not Null" And
(NAMEADDR.[co-number])<>"02" And (NAMEADDR.[co-number])<>"06" And
(NAMEADDR.[co-number])<>"07" And (NAMEADDR.[co-number])<>"08" And
(NAMEADDR.[co-number])<>"14" And (NAMEADDR.[co-number])<>"21" And
(NAMEADDR.[co-number])<>"13" And (NAMEADDR.[co-number])<>"22" And
(NAMEADDR.[co-number])<>"03" And (NAMEADDR.[co-number])<>"04" And
(NAMEADDR.[co-number])<>"05" And (NAMEADDR.[co-number])<>"10" And
(NAMEADDR.[co-number])<>"11"))

ORDER BY [NAMEADDR].[co-number], [NAMEADDR].[cust-number];

Your use of Is Not Null is incorrect and you could reduce
the list of excluded customers can be much shorter:

WHERE NAMEADDR.[co-number] Is Not Null And
NAMEADDR.[co-number] Not IN("02","06","07","08","14",...)

If the co-number field is a numeric type (not Text field),
then do not use quotes around the numbers.

However, that list of excluded customers kind of implies
something is odd in your table design. Maybe you should
have another table with the excluded customer numbers and
join that table in the query.

I agree. I'm not sure that a long list ("02","06", ...) in your Query
is a terrific idea -- it's not easy to maintain. (What would you have
to do to change the list, such as adding another name?)

I put your list of branch numbers into a separate Table. Along with my
sample data, the Tables might look like this:

[CustomerN]
CustomerNumbers
---------------
100
132
160

[NAMEADDR]
co-number cust-number
--------- -----------
01 100
01 132
01 69
02 13

[UnusedBranches]
Branch
02
03
...
22

I modified your Query to include the new [UnusedBranches] Table, so it
looks like this:

[Q_Used] SQL:
SELECT NAMEADDR.[co-number] AS Branch,
NAMEADDR.[cust-number] AS CustomerNumberAssinged,
CustomerN.CustomerNumbers
FROM UnusedBranches RIGHT JOIN
(NAMEADDR INNER JOIN CustomerN
ON NAMEADDR.[cust-number] = CustomerN.CustomerNumbers)
ON UnusedBranches.Branch = NAMEADDR.[co-number]
WHERE (((NAMEADDR.[co-number]) Is Not Null)
AND ((UnusedBranches.Branch) Is Null))
ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number];

[Q_Used] Query Datasheet View:
Branch CustomerNumberAssinged CustomerNumbers
------ ---------------------- ---------------
01 100 100
01 132 132

I don't know if this is what you wanted, but it's the same list I got
with your Query.

I have a few comments...

Is there a good reason for having the current name of
[NAMEADDR].[co-number]? It's possible that calling it
[NAMEADDR].[Branch] would be more suggestive of its purpose, if it's
indeed the name of a branch office.

Assuming you meant, in your Query, to say, as Marshall Burton suggested,

... WHERE NAMEADDR.[co-number] Is Not Null And ...

then your

... RIGHT JOIN CustomerN ...

accomplishes nothing useful that I can perceive. I changed it to an
inner JOIN.

Although I created an [UnusedBranches] Table to house your list of
invalid branch names, I think it would make more sense to store the list
of actual branch names; you'd need to modify the SQL from

(UnusedBranches.Branch) Is Null

to

(UsedBranches.Branch) Is Not Null

, but a Table of valid names would also let you store other information,
such as phone number, about each branch.

Since [NAMEADDR].[cust-number] and [CustomerN].[CustomerNumbers] are
used as matching key fields, I suggest that you give them matching
names. Either of these would be a good name -- I might choose either
one -- but spelling them the same would make it more obvious that they
are supposed to match.

Last comment: Why does your Query display both [Branch
CustomerNumberAssinged] and [CustomerNumbers]? They are guaranteed to
match. Two copies of the same number just take up space.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Well, the company here decided that instead of Branch just use co-number,
which is why I changed it to SELECT NAMEADDR.[co-number] AS Branch. The
NAMEADDR is a CSV file I've link to. There are Branches 01-02-03-...22. Each
with a range capacity of 1-9999, but they used what ever number without any
consistency. I am trying to build this DB as a way to well be consistent. I
broke the queries (BRANCHES) up into separate queries. The CustomerN is a non
linked table I built to list the 1-9999 numbers. I thought by joining them
and then doing a comparison it would give me the used numbers and then th e
unused? Because the first table is linked I did not think to create 14
different tables. But co-number is really Branch number.

tmaxwell wrote:

I have tried to post this as a WHERE and or IIF statement question. It should
be not this difficult a query. I took my main table NameAddr this file
contains all of our customers with assigned customer numbers. Each branch has
a range of usage between 1 and 9999. Branch 01 only uses 1680 that leaves
8319 number assignable. I created a 1-9999 table and right joined them, this
should give me all the numbers “USED†and all the numbers LEFT OVERâ€

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumberAssinged, CustomerN.CustomerNumbers

FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =

CustomerN.CustomerNumbers


WHERE (((NAMEADDR.[co-number])<>"Is Not Null" And
(NAMEADDR.[co-number])<>"02" And (NAMEADDR.[co-number])<>"06" And
(NAMEADDR.[co-number])<>"07" And (NAMEADDR.[co-number])<>"08" And
(NAMEADDR.[co-number])<>"14" And (NAMEADDR.[co-number])<>"21" And
(NAMEADDR.[co-number])<>"13" And (NAMEADDR.[co-number])<>"22" And
(NAMEADDR.[co-number])<>"03" And (NAMEADDR.[co-number])<>"04" And
(NAMEADDR.[co-number])<>"05" And (NAMEADDR.[co-number])<>"10" And
(NAMEADDR.[co-number])<>"11"))

ORDER BY [NAMEADDR].[co-number], [NAMEADDR].[cust-number];

Your use of Is Not Null is incorrect and you could reduce
the list of excluded customers can be much shorter:

WHERE NAMEADDR.[co-number] Is Not Null And
NAMEADDR.[co-number] Not IN("02","06","07","08","14",...)

If the co-number field is a numeric type (not Text field),
then do not use quotes around the numbers.

However, that list of excluded customers kind of implies
something is odd in your table design. Maybe you should
have another table with the excluded customer numbers and
join that table in the query.

I agree. I'm not sure that a long list ("02","06", ...) in your Query
is a terrific idea -- it's not easy to maintain. (What would you have
to do to change the list, such as adding another name?)

I put your list of branch numbers into a separate Table. Along with my
sample data, the Tables might look like this:

[CustomerN]
CustomerNumbers
---------------
100
132
160

[NAMEADDR]
co-number cust-number
--------- -----------
01 100
01 132
01 69
02 13

[UnusedBranches]
Branch
02
03
...
22

I modified your Query to include the new [UnusedBranches] Table, so it
looks like this:

[Q_Used] SQL:
SELECT NAMEADDR.[co-number] AS Branch,
NAMEADDR.[cust-number] AS CustomerNumberAssinged,
CustomerN.CustomerNumbers
FROM UnusedBranches RIGHT JOIN
(NAMEADDR INNER JOIN CustomerN
ON NAMEADDR.[cust-number] = CustomerN.CustomerNumbers)
ON UnusedBranches.Branch = NAMEADDR.[co-number]
WHERE (((NAMEADDR.[co-number]) Is Not Null)
AND ((UnusedBranches.Branch) Is Null))
ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number];

[Q_Used] Query Datasheet View:
Branch CustomerNumberAssinged CustomerNumbers
------ ---------------------- ---------------
01 100 100
01 132 132

I don't know if this is what you wanted, but it's the same list I got
with your Query.

I have a few comments...

Is there a good reason for having the current name of
[NAMEADDR].[co-number]? It's possible that calling it
[NAMEADDR].[Branch] would be more suggestive of its purpose, if it's
indeed the name of a branch office.

Assuming you meant, in your Query, to say, as Marshall Burton suggested,

... WHERE NAMEADDR.[co-number] Is Not Null And ...

then your

... RIGHT JOIN CustomerN ...

accomplishes nothing useful that I can perceive. I changed it to an
inner JOIN.

Although I created an [UnusedBranches] Table to house your list of
invalid branch names, I think it would make more sense to store the list
of actual branch names; you'd need to modify the SQL from

(UnusedBranches.Branch) Is Null

to

(UsedBranches.Branch) Is Not Null

, but a Table of valid names would also let you store other information,
such as phone number, about each branch.

Since [NAMEADDR].[cust-number] and [CustomerN].[CustomerNumbers] are
used as matching key fields, I suggest that you give them matching
names. Either of these would be a good name -- I might choose either
one -- but spelling them the same would make it more obvious that they
are supposed to match.

Last comment: Why does your Query display both [Branch
CustomerNumberAssinged] and [CustomerNumbers]? They are guaranteed to
match. Two copies of the same number just take up space.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Well, the company here decided that instead of Branch just use co-number,
which is why I changed it to SELECT NAMEADDR.[co-number] AS Branch.

OK, that's a valid reason. Sometimes the choice of name is not up to you.

The
NAMEADDR is a CSV file I've link to. There are Branches 01-02-03-...22. Each
with a range capacity of 1-9999,

I assume you mean that each Branch may have customer numbers in the
range of 1-9999. For example, could Branch 02 have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer)?

Another interpretation of what you said is that the customer numbers are
in the range of 1-9999, but that no customer is assigned to more than
one Branch.

Another interpretation is that customer 15 could do business with both
Branch 02 and Branch 03 (but it's the same customer 15 in both cases).

Each of these possibilities implies a different structure to your database.

but they used what ever number without any
consistency. I am trying to build this DB as a way to well be consistent. I
broke the queries (BRANCHES) up into separate queries. The CustomerN is a non
linked table I built to list the 1-9999 numbers. I thought by joining them
and then doing a comparison it would give me the used numbers and then the
unused?

Yes, you can do this via JOIN operations, but maybe not the way you were
thinking. Do you, for example, want a combined list, showing a field
that could say either "Used" or "Available"? I think it would be more
useful to have separate Queries, such as one showing available names,
from which you could pick one.

Because the first table is linked I did not think to create 14
different tables.

That's good. 14 different Tables would be a mess to maintain. Access
will let you do that, but not everything that's possible makes sense.
(Definitely try to avoid setting up separate Tables with identical, or
very similar, contents.)

But co-number is really Branch number.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Vincent,

Yes, you are correct each Branch will have customer numbers in the
range of 1-9999. Branch 02 will have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer). Branch 02, Customer number15 is Cosco and in Branch 03
it's Kroger. Hence the reason for "trying to be consistent". I can only
correct what mess is left.

This is a perfect example of want I need, a combined list, showing a field
that could say either "Used" or "Available"? The NAMEADRR does have 5 more
fields, address, state, phone, I just don't need them included. I just need
to see in any of the Branches, what numbers that are Available and
Un-Available. The reason I created the CustomerN table is because even though
each Branch has a 1-9999 range, they may not of used 9979, so I figured I
would need this table to do the comparison. I hope this makes sense? Again, I
wrote a query for each individual Branch, it just seemed that the right
joined would of worked....

Well, the company here decided that instead of Branch just use co-number,
which is why I changed it to SELECT NAMEADDR.[co-number] AS Branch.

OK, that's a valid reason. Sometimes the choice of name is not up to you.

The
NAMEADDR is a CSV file I've link to. There are Branches 01-02-03-...22. Each
with a range capacity of 1-9999,

I assume you mean that each Branch may have customer numbers in the
range of 1-9999. For example, could Branch 02 have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer)?

Another interpretation of what you said is that the customer numbers are
in the range of 1-9999, but that no customer is assigned to more than
one Branch.

Another interpretation is that customer 15 could do business with both
Branch 02 and Branch 03 (but it's the same customer 15 in both cases).

Each of these possibilities implies a different structure to your database.

but they used what ever number without any
consistency. I am trying to build this DB as a way to well be consistent. I
broke the queries (BRANCHES) up into separate queries. The CustomerN is a non
linked table I built to list the 1-9999 numbers. I thought by joining them
and then doing a comparison it would give me the used numbers and then the
unused?

Yes, you can do this via JOIN operations, but maybe not the way you were
thinking. Do you, for example, want a combined list, showing a field
that could say either "Used" or "Available"? I think it would be more
useful to have separate Queries, such as one showing available names,
from which you could pick one.

Because the first table is linked I did not think to create 14
different tables.

That's good. 14 different Tables would be a mess to maintain. Access
will let you do that, but not everything that's possible makes sense.
(Definitely try to avoid setting up separate Tables with identical, or
very similar, contents.)

But co-number is really Branch number.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

This works but it only gives me a match of the numbers "USED". I believe need
"Not Used" would be fine. MAybe include the customer name field and then do
a NULL on it?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-number]
FROM NAMEADDR RIGHT JOIN CustomerN ON NAMEADDR.[cust-number] =
CustomerN.CustomerNumbers

WHERE (((NAMEADDR.[co-number]) Not In
("02","06","07","08","11","14","04","03","21","22","10","05","13")) AND
((NAMEADDR.[cust-number]));

Someone answered with this below. It works just the same. Someone even
suggested a subquery.

WHERE (((NAMEADDR.[co-number]) Is Not Null And (NAMEADDR.[co-number]) Not In
("02","06","07","08","11","14","04","03","21","22","10","05","13")) AND
((NAMEADDR.[cust-number]) Between "00000000000001" And "00000000009999"));

Vincent,

Yes, you are correct each Branch will have customer numbers in the
range of 1-9999. Branch 02 will have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer). Branch 02, Customer number15 is Cosco and in Branch 03
it's Kroger. Hence the reason for "trying to be consistent". I can only
correct what mess is left.

This is a perfect example of want I need, a combined list, showing a field
that could say either "Used" or "Available"? The NAMEADRR does have 5 more
fields, address, state, phone, I just don't need them included. I just need
to see in any of the Branches, what numbers that are Available and
Un-Available. The reason I created the CustomerN table is because even though
each Branch has a 1-9999 range, they may not of used 9979, so I figured I
would need this table to do the comparison. I hope this makes sense? Again, I
wrote a query for each individual Branch, it just seemed that the right
joined would of worked....

Well, the company here decided that instead of Branch just use co-number,
which is why I changed it to SELECT NAMEADDR.[co-number] AS Branch.

OK, that's a valid reason. Sometimes the choice of name is not up to you.

The
NAMEADDR is a CSV file I've link to. There are Branches 01-02-03-...22. Each
with a range capacity of 1-9999,

I assume you mean that each Branch may have customer numbers in the
range of 1-9999. For example, could Branch 02 have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer)?

Another interpretation of what you said is that the customer numbers are
in the range of 1-9999, but that no customer is assigned to more than
one Branch.

Another interpretation is that customer 15 could do business with both
Branch 02 and Branch 03 (but it's the same customer 15 in both cases).

Each of these possibilities implies a different structure to your database.

but they used what ever number without any
consistency. I am trying to build this DB as a way to well be consistent. I
broke the queries (BRANCHES) up into separate queries. The CustomerN is a non
linked table I built to list the 1-9999 numbers. I thought by joining them
and then doing a comparison it would give me the used numbers and then the
unused?

Yes, you can do this via JOIN operations, but maybe not the way you were
thinking. Do you, for example, want a combined list, showing a field
that could say either "Used" or "Available"? I think it would be more
useful to have separate Queries, such as one showing available names,
from which you could pick one.

Because the first table is linked I did not think to create 14
different tables.

That's good. 14 different Tables would be a mess to maintain. Access
will let you do that, but not everything that's possible makes sense.
(Definitely try to avoid setting up separate Tables with identical, or
very similar, contents.)

But co-number is really Branch number.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Vincent,

Yes, you are correct each Branch will have customer numbers in the
range of 1-9999. Branch 02 will have a customer number of
15 and Branch 03 also have a customer number of 15 (but meaning a
different customer). Branch 02, Customer number15 is Cosco and in Branch 03
it's Kroger. Hence the reason for "trying to be consistent". I can only
correct what mess is left.

OK, it doesn't seem too bad.

Now, I'll assume that you need to set up your Tables only once, so it
will take a bit of work, but you don't need to develop some automated
tool (such as a Query or Macro) to do it over and over.

For a 1-time Table setup, I suggest you use Excel to create the lists,
then edit them and (when you're happy with the edited version) import
the results into Access.

In Excel:

A B C
5 co-number cust-number Exists?
--------- ----------- -------
6 01 1
7 01 2
8 01 3
9 01 4
10 01 5
...
1685 01 1680
1686 O2 1
1687 02 2
...

where I included all possible numbers, including those that match
existing accounts, since you'll delete some of these later. Most of the
column B values are formulas, such as in B8,

=B7+1

Make as many of these as you need, such as 1680 rows for Branch "01".
After setting up the first row, you can just click & drag to make the
others. If you get carried away and go a bit too far, just erase the
extras. Repeat this process for Branch "02" and the rest.

Then import this Excel table (including all the Branches, if you can)
into a new Table in Access.

Next step depends on what you want to do. You could consider this Table
to be a master list of accounts (many of which are empty but available
for use), and you could add account information fields to it. One of
the fields (I'd recommend a Yes/No type) could indicate that the number
is in use as a valid account number, and maybe another field could
indicate that it's no longer valid and not usable for a new account.

Or, you could delete (via a Delete Query) from it the numbers that you
know are in use or otherwise unavailable, and use it as a source of new
numbers. What I don't like about this choice is that any time you use
one of these numbers for a new account, you'll have to delete its record
from the list of available numbers when you add it to the Table of
current accounts, which I think is extra hassle. But it should work, as
long as you can be sure to delete the records consistently (in other
words, via some automated process instead of depending on someone to
remember to do it).

If you want details of how to do either of these, just ask.

 

[Guest]

Vincent,

I thought of doing it this way to start, but with 14 Branches x 9999 it
would be 139000 rows (Branches 01-14). Do you mean 01 in one set of columns,
then 02 in the next set, then 03...... or just create two Excel sheets and
split the branches up?

 

[Vincent Johns]

Vincent,

I thought of doing it this way to start, but with 14 Branches x 9999 it
would be 139000 rows (Branches 01-14). Do you mean 01 in one set of columns,
then 02 in the next set, then 03...... or just create two Excel sheets and
split the branches up?

Well, you did say that you don't have a full 9999 customers in each
branch, so I thought it would be fewer (such as 1680 in Branch "01").

Actually, for now, you probably won't use all 9999 numbers. Why don't
you try a more modest limit for now, such as 1999 customers per Branch,
or 300 more than are currently in use at each Branch, or something
similar? If you run out of numbers in some branch, you can always add
more (up to 9999).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Marshall Barton]

I thought of doing it this way to start, but with 14 Branches x 9999 it
would be 139000 rows (Branches 01-14). Do you mean 01 in one set of columns,
then 02 in the next set, then 03...... or just create two Excel sheets and
split the branches up?

Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job. I am
completely lost as to the table and field names you are
actually using, but, if you can decipher my guesses as to
the names, the query you want should look something like:

SELECT BranchNumbers.BranchNum,
BranchNumbers.[number],
IIf(NameAddr.[co-number] Is Null, "Available",
"Assigned") As X
FROM BranchNumbers LEFT JOIN NameAddr
ON BranchNumbers.BranchNum = NameAddr.branch
BranchNumbers.[number] = NameAddr.[co-number]
ORDER BY BranchNumbers.BranchNum,
BranchNumbers.[number]

 

[Vincent Johns]

Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job.

[...]

This looks like a cool idea -- but I've not been able to get it to work.
(It would be easy via VBA -- just add, under program control, a Table
to the TableDefs collection, populate it, use it, and delete it.)

If I try to use the Query as you expressed it here,
"tbl 1-9999.number" is just a parameter to the Query (and not useful),
and the number of records depends on how many records happen to be in
[NAMEADDR], which might not equal 9999. For example, with 3 records in
[NAMEADDR] when you run the Query, you'd get 42 records (3 records for
each of 14 branches) returned, and all the [number] values would be the
same -- whatever you entered for the parameter.

If I define a Table with the name [tbl 1-9999], and populate it with a
bunch of numbers, it works, but then I again have a real Table
cluttering the namespace, and it gives me more records than I want,
unless [NAMEADDR] contains only one record. (And that real Table
contains no actual data -- just a bunch of possible account numbers that
could be used by multiple branches.)

So how did you define [tbl 1-9999]? I can think of times when this
would be a handy feature to use, without having to write code in a Module.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Marshall Barton]

Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job.

[...]

This looks like a cool idea -- but I've not been able to get it to work.
(It would be easy via VBA -- just add, under program control, a Table
to the TableDefs collection, populate it, use it, and delete it.)

If I try to use the Query as you expressed it here,
"tbl 1-9999.number" is just a parameter to the Query (and not useful),
and the number of records depends on how many records happen to be in
[NAMEADDR], which might not equal 9999. For example, with 3 records in
[NAMEADDR] when you run the Query, you'd get 42 records (3 records for
each of 14 branches) returned, and all the [number] values would be the
same -- whatever you entered for the parameter.

If I define a Table with the name [tbl 1-9999], and populate it with a
bunch of numbers, it works, but then I again have a real Table
cluttering the namespace, and it gives me more records than I want,
unless [NAMEADDR] contains only one record. (And that real Table
contains no actual data -- just a bunch of possible account numbers that
could be used by multiple branches.)

So how did you define [tbl 1-9999]? I can think of times when this
would be a handy feature to use, without having to write code in a Module.

Maybe I misread one of your earlier posts, but I thought
that table (with a name I couldn't determine) was something
you had recommended.

The way I defined table [tbl 1-9999] was as a single column
named "number" and 9999 rows containing 1,2,3, ..., 9999
The cross product join with 14 branches would produce a
dataset of ~140,000 combinations. Each combination Left
Joined to the actual data will yield either a Null or the
matching value in the main table.

This kind of table is fairly useful in many contexts and I
include one in every application I create so I don't
consider it clutter. If the field is a Long, this table is
only about 40K bytes so its overhead is no great burden. If
you want to minimize it you can keep a permanent table of
only 10 rows with rows 0 - 9, then crossproduct that table
with itself four times to get a ten thousand row virtual
table for this particular application.

I hope I didn't misunderstand the problem, when I read that
tmaxwell wanted a complete list of every assigned and
available number at every branch.

 

[Guest]

Vincent,
Because I need the range to be 1-9999 I just created two columns, Branches
and Cust-numbers. Then starting with 01, I put 1-9999, then with 02...14. If
I can exclude any numbers used, I will be happy. Of the 19000 customers we
have, and you are correct some are spread accross the different Branches. I
thought that if I just wrote a query to ..say.. any customer number with a
name in the NameAddr tbl exclude. I can get the query to match all the
numbers used, just not the opposite. This should be easy. I've written some
complex queries before with my other DB's. I tried to write this one so many
different ways I can hardly think. But I really do appreciate your help.

Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job.

[...]

This looks like a cool idea -- but I've not been able to get it to work.
(It would be easy via VBA -- just add, under program control, a Table
to the TableDefs collection, populate it, use it, and delete it.)

If I try to use the Query as you expressed it here,
"tbl 1-9999.number" is just a parameter to the Query (and not useful),
and the number of records depends on how many records happen to be in
[NAMEADDR], which might not equal 9999. For example, with 3 records in
[NAMEADDR] when you run the Query, you'd get 42 records (3 records for
each of 14 branches) returned, and all the [number] values would be the
same -- whatever you entered for the parameter.

If I define a Table with the name [tbl 1-9999], and populate it with a
bunch of numbers, it works, but then I again have a real Table
cluttering the namespace, and it gives me more records than I want,
unless [NAMEADDR] contains only one record. (And that real Table
contains no actual data -- just a bunch of possible account numbers that
could be used by multiple branches.)

So how did you define [tbl 1-9999]? I can think of times when this
would be a handy feature to use, without having to write code in a Module.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Marshall,
The main table that is a linked table is NAMEADDR. The table 1-9999 is for
Branch and Customer numbers in the fll range 1-9999. Because not all the
Branches use numbers all the way to 9999, some may stop at 7999, but I still
need the range to be 1-9999. What would I be pulling from the NAMEADDR table?

query BranchNumbers:
SELECT [tbl 1-9999].[number], Branches.BranchNum
FROM NAMEADDR, Branches

Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job.

[...]

This looks like a cool idea -- but I've not been able to get it to work.
(It would be easy via VBA -- just add, under program control, a Table
to the TableDefs collection, populate it, use it, and delete it.)

If I try to use the Query as you expressed it here,
"tbl 1-9999.number" is just a parameter to the Query (and not useful),
and the number of records depends on how many records happen to be in
[NAMEADDR], which might not equal 9999. For example, with 3 records in
[NAMEADDR] when you run the Query, you'd get 42 records (3 records for
each of 14 branches) returned, and all the [number] values would be the
same -- whatever you entered for the parameter.

If I define a Table with the name [tbl 1-9999], and populate it with a
bunch of numbers, it works, but then I again have a real Table
cluttering the namespace, and it gives me more records than I want,
unless [NAMEADDR] contains only one record. (And that real Table
contains no actual data -- just a bunch of possible account numbers that
could be used by multiple branches.)

So how did you define [tbl 1-9999]? I can think of times when this
would be a handy feature to use, without having to write code in a Module.

Maybe I misread one of your earlier posts, but I thought
that table (with a name I couldn't determine) was something
you had recommended.

The way I defined table [tbl 1-9999] was as a single column
named "number" and 9999 rows containing 1,2,3, ..., 9999
The cross product join with 14 branches would produce a
dataset of ~140,000 combinations. Each combination Left
Joined to the actual data will yield either a Null or the
matching value in the main table.

This kind of table is fairly useful in many contexts and I
include one in every application I create so I don't
consider it clutter. If the field is a Long, this table is
only about 40K bytes so its overhead is no great burden. If
you want to minimize it you can keep a permanent table of
only 10 rows with rows 0 - 9, then crossproduct that table
with itself four times to get a ten thousand row virtual
table for this particular application.

I hope I didn't misunderstand the problem, when I read that
tmaxwell wanted a complete list of every assigned and
available number at every branch.

 

[Vincent Johns]

OK, maybe this is what you want...

Piggybacking on Marshall's clever idea of using the Cartesian product of
a small Table with itself, I define a Table containing only 10 numbers:

[tbl 0-9] Table Datasheet View:

number
------
0
1
2
3
4
5
6
7
8
9

I also have a Table that specifies the maximum number of accounts at
each Branch. (I changed Branch "01" from 1680 to 998 to make the
example work better. You should set them to whatever is correct.)

[Branches] Table Datasheet View:

Branch NumberOfAccounts
------ ----------------
01 998
02 997
03 500
04 74
...

And we have the Table of existing, and thus unavailable, account numbers
at each Branch. I have included only two Branches, to make the example
more concise:

[NAMEADDR] Table Datasheet View:

co-number cust-number
--------- -----------
01 993
01 995
01 996
02 991
02 995

Now we create a Query to list all 9999 possible customer numbers for any
Branch, including those that may be in use:

[Q_9999] SQL:

SELECT [1000].number*1000+[100].number*100+[10].number*10
+[1].number AS [Number]
FROM [tbl 0-9] AS 1000, [tbl 0-9] AS 100, [tbl 0-9] AS 10,
[tbl 0-9] AS 1
WHERE ((([1000].[number]*1000+[100].[number]*100+[10].[number]*10
+[1].[number])<>0))
ORDER BY [1000].number*1000+[100].number*100
+[10].number*10+[1].number;

(I expect that this occupies considerably less space than a
10,000-record Table.) As you might expect, the contents are kind of
boring, and I've omitted most of them here:

[Q_9999] Query Datasheet View:

Number
------
1
2
3
4
5
...
9997
9998
9999

Now I define a Query that lists all of the possible account numbers for
each Branch, up to the limit specified in [Branches].[NumberOfAccounts]:

[Q_Combo] SQL:

SELECT Branches.Branch, Q_9999.Number
FROM Branches, Q_9999
WHERE (
((Branches.Branch)<="02") AND
((Q_9999.Number)<=[Branches]![NumberOfAccounts]
And (Q_9999.Number)>=990
))
ORDER BY Branches.Branch, Q_9999.Number;

The lines "((Branches.Branch)<="02") AND "
and "And (Q_9999.Number)>=990"
are included ONLY to make the example shorter. You need to erase both
lines from this SQL. This Query lists account numbers up to 998 for
Branch "01" and up to 997 for Branch "02", omitting the other numbers
and Branches. The complete list, now, looks like this:

[Q_Combo] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 993
01 994
01 995
01 996
01 997
01 998
02 990
02 991
02 992
02 993
02 994
02 995
02 996
02 997

Now we're ready to list the unused ones. We define one more Query:

[Q_Unused] SQL:

SELECT Q_Combo.Branch, Q_Combo.number
FROM Q_Combo LEFT JOIN NAMEADDR
ON (Q_Combo.Branch = NAMEADDR.[co-number])
AND (Q_Combo.number = NAMEADDR.[cust-number])
WHERE (((NAMEADDR.[co-number]) Is Null))
ORDER BY Q_Combo.Branch, Q_Combo.number;

and -- voilá -- we have what I think you asked for, a list of all the
account numbers (up to the maximum allowed for each Branch) that are not
already listed in [NAMEADDR]:

[Q_Unused] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 994
01 997
01 998
02 990
02 992
02 993
02 994
02 996
02 997

Notice that account 995 is not listed for either Branch, but 993 is
listed for Branch "02".

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Marshall Barton]

Sorry, there's a typo in that query. It should be:

SELECT [tbl 1-9999].[number], Branches.BranchNum
FROM [tbl 1-9999], Branches

Where [tbl 1-9999] is a table with one column (Long) named
[number] and rows 1,2,3,...,9999, which I thought you had
already created. If not, you can use a ten row table and a
query as Vincent explains in another post.
--
Marsh
MVP [MS Access]

The main table that is a linked table is NAMEADDR. The table 1-9999 is for
Branch and Customer numbers in the fll range 1-9999. Because not all the
Branches use numbers all the way to 9999, some may stop at 7999, but I still
need the range to be 1-9999. What would I be pulling from the NAMEADDR table?

query BranchNumbers:
SELECT [tbl 1-9999].[number], Branches.BranchNum
FROM NAMEADDR, Branches

Marshall Barton wrote:
Ok, I think I am getting a clearer picture of what we're
trying to do here. First, Vincent's idea of having a table
of all combinations of branches and possible numbers is a
good idea, BUT you do not need a real table to do this. You
can create a query to represent a virtual table. Assuming
you have a table with the 14(?) branch numbers:

query BranchNumbers:
SELECT [tbl 1-9999].[number],
Branches.BranchNum
FROM NAMEADDR, Branches

Now you can use that virtual table to do the job.
[...]

This looks like a cool idea -- but I've not been able to get it to work.
(It would be easy via VBA -- just add, under program control, a Table
to the TableDefs collection, populate it, use it, and delete it.)

If I try to use the Query as you expressed it here,
"tbl 1-9999.number" is just a parameter to the Query (and not useful),
and the number of records depends on how many records happen to be in
[NAMEADDR], which might not equal 9999. For example, with 3 records in
[NAMEADDR] when you run the Query, you'd get 42 records (3 records for
each of 14 branches) returned, and all the [number] values would be the
same -- whatever you entered for the parameter.

If I define a Table with the name [tbl 1-9999], and populate it with a
bunch of numbers, it works, but then I again have a real Table
cluttering the namespace, and it gives me more records than I want,
unless [NAMEADDR] contains only one record. (And that real Table
contains no actual data -- just a bunch of possible account numbers that
could be used by multiple branches.)

So how did you define [tbl 1-9999]? I can think of times when this
would be a handy feature to use, without having to write code in a Module.

Maybe I misread one of your earlier posts, but I thought
that table (with a name I couldn't determine) was something
you had recommended.

The way I defined table [tbl 1-9999] was as a single column
named "number" and 9999 rows containing 1,2,3, ..., 9999
The cross product join with 14 branches would produce a
dataset of ~140,000 combinations. Each combination Left
Joined to the actual data will yield either a Null or the
matching value in the main table.

This kind of table is fairly useful in many contexts and I
include one in every application I create so I don't
consider it clutter. If the field is a Long, this table is
only about 40K bytes so its overhead is no great burden. If
you want to minimize it you can keep a permanent table of
only 10 rows with rows 0 - 9, then crossproduct that table
with itself four times to get a ten thousand row virtual
table for this particular application.

I hope I didn't misunderstand the problem, when I read that
tmaxwell wanted a complete list of every assigned and
available number at every branch.

 

[Guest]

Vincent,
When I build th query Q_9999 from tbl 0-9 it still lists 10 of each Numbers
and the same for Q_combo, When I doQ_Unused it has a problem with the
expression. It's close....

OK, maybe this is what you want...

Piggybacking on Marshall's clever idea of using the Cartesian product of
a small Table with itself, I define a Table containing only 10 numbers:

[tbl 0-9] Table Datasheet View:

number
------
0
1
2
3
4
5
6
7
8
9

I also have a Table that specifies the maximum number of accounts at
each Branch. (I changed Branch "01" from 1680 to 998 to make the
example work better. You should set them to whatever is correct.)

[Branches] Table Datasheet View:

Branch NumberOfAccounts
------ ----------------
01 998
02 997
03 500
04 74
...

And we have the Table of existing, and thus unavailable, account numbers
at each Branch. I have included only two Branches, to make the example
more concise:

[NAMEADDR] Table Datasheet View:

co-number cust-number
--------- -----------
01 993
01 995
01 996
02 991
02 995

Now we create a Query to list all 9999 possible customer numbers for any
Branch, including those that may be in use:

[Q_9999] SQL:

SELECT [1000].number*1000+[100].number*100+[10].number*10
+[1].number AS [Number]
FROM [tbl 0-9] AS 1000, [tbl 0-9] AS 100, [tbl 0-9] AS 10,
[tbl 0-9] AS 1
WHERE ((([1000].[number]*1000+[100].[number]*100+[10].[number]*10
+[1].[number])<>0))
ORDER BY [1000].number*1000+[100].number*100
+[10].number*10+[1].number;

(I expect that this occupies considerably less space than a
10,000-record Table.) As you might expect, the contents are kind of
boring, and I've omitted most of them here:

[Q_9999] Query Datasheet View:

Number
------
1
2
3
4
5
...
9997
9998
9999

Now I define a Query that lists all of the possible account numbers for
each Branch, up to the limit specified in [Branches].[NumberOfAccounts]:

[Q_Combo] SQL:

SELECT Branches.Branch, Q_9999.Number
FROM Branches, Q_9999
WHERE (
((Branches.Branch)<="02") AND
((Q_9999.Number)<=[Branches]![NumberOfAccounts]
And (Q_9999.Number)>=990
))
ORDER BY Branches.Branch, Q_9999.Number;

The lines "((Branches.Branch)<="02") AND "
and "And (Q_9999.Number)>=990"
are included ONLY to make the example shorter. You need to erase both
lines from this SQL. This Query lists account numbers up to 998 for
Branch "01" and up to 997 for Branch "02", omitting the other numbers
and Branches. The complete list, now, looks like this:

[Q_Combo] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 993
01 994
01 995
01 996
01 997
01 998
02 990
02 991
02 992
02 993
02 994
02 995
02 996
02 997

Now we're ready to list the unused ones. We define one more Query:

[Q_Unused] SQL:

SELECT Q_Combo.Branch, Q_Combo.number
FROM Q_Combo LEFT JOIN NAMEADDR
ON (Q_Combo.Branch = NAMEADDR.[co-number])
AND (Q_Combo.number = NAMEADDR.[cust-number])
WHERE (((NAMEADDR.[co-number]) Is Null))
ORDER BY Q_Combo.Branch, Q_Combo.number;

and -- voilá -- we have what I think you asked for, a list of all the
account numbers (up to the maximum allowed for each Branch) that are not
already listed in [NAMEADDR]:

[Q_Unused] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 994
01 997
01 998
02 990
02 992
02 993
02 994
02 996
02 997

Notice that account 995 is not listed for either Branch, but 993 is
listed for Branch "02".

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Vincent,
Because I need the range to be 1-9999 I just created two columns, Branches
and Cust-numbers. Then starting with 01, I put 1-9999, then with 02...14. If
I can exclude any numbers used, I will be happy. Of the 19000 customers we
have, and you are correct some are spread accross the different Branches. I
thought that if I just wrote a query to ..say.. any customer number with a
name in the NameAddr tbl exclude. I can get the query to match all the
numbers used, just not the opposite. This should be easy. I've written some
complex queries before with my other DB's. I tried to write this one so many
different ways I can hardly think. But I really do appreciate your help.

 

[Guest]

Vincent,
I fixed the first 2 problems, but I am still getting an "type mismatched
expression" would this be from Q_combo? It works fine.

SELECT Q_Combo.Branch, Q_Combo.number
FROM Q_Combo LEFT JOIN NAMEADDR on (Q_Combo.Branch =
NAMEADDR.[co-number]) AND (Q_Combo.number = NAMEADDR.[cust-number])
WHERE (((NAMEADDR.[co-number]) Is Null))
ORDER BY Q_Combo.Branch, Q_Combo.number;

Any thoughts?
Todd

OK, maybe this is what you want...

Piggybacking on Marshall's clever idea of using the Cartesian product of
a small Table with itself, I define a Table containing only 10 numbers:

[tbl 0-9] Table Datasheet View:

number
------
0
1
2
3
4
5
6
7
8
9

I also have a Table that specifies the maximum number of accounts at
each Branch. (I changed Branch "01" from 1680 to 998 to make the
example work better. You should set them to whatever is correct.)

[Branches] Table Datasheet View:

Branch NumberOfAccounts
------ ----------------
01 998
02 997
03 500
04 74
...

And we have the Table of existing, and thus unavailable, account numbers
at each Branch. I have included only two Branches, to make the example
more concise:

[NAMEADDR] Table Datasheet View:

co-number cust-number
--------- -----------
01 993
01 995
01 996
02 991
02 995

Now we create a Query to list all 9999 possible customer numbers for any
Branch, including those that may be in use:

[Q_9999] SQL:

SELECT [1000].number*1000+[100].number*100+[10].number*10
+[1].number AS [Number]
FROM [tbl 0-9] AS 1000, [tbl 0-9] AS 100, [tbl 0-9] AS 10,
[tbl 0-9] AS 1
WHERE ((([1000].[number]*1000+[100].[number]*100+[10].[number]*10
+[1].[number])<>0))
ORDER BY [1000].number*1000+[100].number*100
+[10].number*10+[1].number;

(I expect that this occupies considerably less space than a
10,000-record Table.) As you might expect, the contents are kind of
boring, and I've omitted most of them here:

[Q_9999] Query Datasheet View:

Number
------
1
2
3
4
5
...
9997
9998
9999

Now I define a Query that lists all of the possible account numbers for
each Branch, up to the limit specified in [Branches].[NumberOfAccounts]:

[Q_Combo] SQL:

SELECT Branches.Branch, Q_9999.Number
FROM Branches, Q_9999
WHERE (
((Branches.Branch)<="02") AND
((Q_9999.Number)<=[Branches]![NumberOfAccounts]
And (Q_9999.Number)>=990
))
ORDER BY Branches.Branch, Q_9999.Number;

The lines "((Branches.Branch)<="02") AND "
and "And (Q_9999.Number)>=990"
are included ONLY to make the example shorter. You need to erase both
lines from this SQL. This Query lists account numbers up to 998 for
Branch "01" and up to 997 for Branch "02", omitting the other numbers
and Branches. The complete list, now, looks like this:

[Q_Combo] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 993
01 994
01 995
01 996
01 997
01 998
02 990
02 991
02 992
02 993
02 994
02 995
02 996
02 997

Now we're ready to list the unused ones. We define one more Query:

[Q_Unused] SQL:

SELECT Q_Combo.Branch, Q_Combo.number
FROM Q_Combo LEFT JOIN NAMEADDR
ON (Q_Combo.Branch = NAMEADDR.[co-number])
AND (Q_Combo.number = NAMEADDR.[cust-number])
WHERE (((NAMEADDR.[co-number]) Is Null))
ORDER BY Q_Combo.Branch, Q_Combo.number;

and -- voilá -- we have what I think you asked for, a list of all the
account numbers (up to the maximum allowed for each Branch) that are not
already listed in [NAMEADDR]:

[Q_Unused] Query Datasheet View:

Branch number
------ ------
01 990
01 991
01 992
01 994
01 997
01 998
02 990
02 992
02 993
02 994
02 996
02 997

Notice that account 995 is not listed for either Branch, but 993 is
listed for Branch "02".

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Vincent,
Because I need the range to be 1-9999 I just created two columns, Branches
and Cust-numbers. Then starting with 01, I put 1-9999, then with 02...14. If
I can exclude any numbers used, I will be happy. Of the 19000 customers we
have, and you are correct some are spread accross the different Branches. I
thought that if I just wrote a query to ..say.. any customer number with a
name in the NameAddr tbl exclude. I can get the query to match all the
numbers used, just not the opposite. This should be easy. I've written some
complex queries before with my other DB's. I tried to write this one so many
different ways I can hardly think. But I really do appreciate your help.