IIF

[tmaxwell]

I originally posted this as "Help with the WHERE clause", were I was using
two different tables. I really can pull the info needed on our current
customer base with just the one.
I am going to try and explain this as best possible. My database is for
Accounts Receivables. We assign new customers a company number, we have 7
Branches, each Branch has a number range between 00001 thru 9999. At the
moment they are assigned randomly. I need to track numbers used, and numbers
not used. The query I have now does track what is used, but does not include
the full range. I have been trying this with the WHERE clause, but I think
what I'm looking for is an IIF Statement. How can I track what is used
between the 0001 thru 9999 range per BRANCH?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-name] AS CustomerName


FROM NAMEADDR

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

 

[[MVP] S.Clark]

IIF is never the "right" answer. It may 'work', but it doesn't make it
"right".

You can create a table, and populate it with all of the numbers from 0001 to
9999. Single column, that is.

Link this new table to the AR table, and change the Join Properties to show
ALL data from the new table. (Select either 2 or 3 from the Dialog box.)

 

[tmaxwell]

S.
I did this in a query, right joined, but it just shows the numbers that
match up between the 2 tables. I need to know , say in Branch 01 what numbers
that are used and what are left, or what number that are not used period.
Either way would work. Of the 7 Branches there is a possible of 69993 numbers
available, we have assigned 19063, I was trying to do this by each Branch
seperately, still could not get it to work.........

IIF is never the "right" answer. It may 'work', but it doesn't make it
"right".

You can create a table, and populate it with all of the numbers from 0001 to
9999. Single column, that is.

Link this new table to the AR table, and change the Join Properties to show
ALL data from the new table. (Select either 2 or 3 from the Dialog box.)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I originally posted this as "Help with the WHERE clause", were I was using
two different tables. I really can pull the info needed on our current
customer base with just the one.
I am going to try and explain this as best possible. My database is for
Accounts Receivables. We assign new customers a company number, we have 7
Branches, each Branch has a number range between 00001 thru 9999. At the
moment they are assigned randomly. I need to track numbers used, and
numbers
not used. The query I have now does track what is used, but does not
include
the full range. I have been trying this with the WHERE clause, but I think
what I'm looking for is an IIF Statement. How can I track what is used
between the 0001 thru 9999 range per BRANCH?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-name] AS CustomerName


FROM NAMEADDR

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

 

[KARL DEWEY]

Well one way is to create a table of numbers - you can have a field named USED.
Then left join numbers table to branch number and have criteria for branch
number as null.

S.
I did this in a query, right joined, but it just shows the numbers that
match up between the 2 tables. I need to know , say in Branch 01 what numbers
that are used and what are left, or what number that are not used period.
Either way would work. Of the 7 Branches there is a possible of 69993 numbers
available, we have assigned 19063, I was trying to do this by each Branch
seperately, still could not get it to work.........

IIF is never the "right" answer. It may 'work', but it doesn't make it
"right".

You can create a table, and populate it with all of the numbers from 0001 to
9999. Single column, that is.

Link this new table to the AR table, and change the Join Properties to show
ALL data from the new table. (Select either 2 or 3 from the Dialog box.)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I originally posted this as "Help with the WHERE clause", were I was using
two different tables. I really can pull the info needed on our current
customer base with just the one.
I am going to try and explain this as best possible. My database is for
Accounts Receivables. We assign new customers a company number, we have 7
Branches, each Branch has a number range between 00001 thru 9999. At the
moment they are assigned randomly. I need to track numbers used, and
numbers
not used. The query I have now does track what is used, but does not
include
the full range. I have been trying this with the WHERE clause, but I think
what I'm looking for is an IIF Statement. How can I track what is used
between the 0001 thru 9999 range per BRANCH?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-name] AS CustomerName


FROM NAMEADDR

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

 

[Adam Turner via AccessMonster.com]

I originally posted this as "Help with the WHERE clause", were I was using
two different tables. I really can pull the info needed on our current
customer base with just the one.
I am going to try and explain this as best possible. My database is for
Accounts Receivables. We assign new customers a company number, we have 7
Branches, each Branch has a number range between 00001 thru 9999. At the
moment they are assigned randomly. I need to track numbers used, and numbers
not used. The query I have now does track what is used, but does not include
the full range. I have been trying this with the WHERE clause, but I think
what I'm looking for is an IIF Statement. How can I track what is used
between the 0001 thru 9999 range per BRANCH?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-name] AS CustomerName

FROM NAMEADDR

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

You need a NOT EXISTS subquery

 

[Adam Turner via AccessMonster.com]

S.
I did this in a query, right joined, but it just shows the numbers that
match up between the 2 tables. I need to know , say in Branch 01 what numbers
that are used and what are left, or what number that are not used period.
Either way would work. Of the 7 Branches there is a possible of 69993 numbers
available, we have assigned 19063, I was trying to do this by each Branch
seperately, still could not get it to work.........

IIF is never the "right" answer. It may 'work', but it doesn't make it
"right".

[quoted text clipped - 25 lines]

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

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-name] AS CustomerName
FROM NAMEADDR
WHERE NAMEADDR.[cust-number] NOT IN (SELECT NAMEADDR.[co-number]

FROM NAMEADDR

WHERE NAMEADDR.[cust-number] BETWEEN 1 AND 9999)
ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number];

The above query should give you all unused customer numbers and the
associated branch

 

[Adam Turner via AccessMonster.com]

S.
I did this in a query, right joined, but it just shows the numbers that

[quoted text clipped - 9 lines]

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

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-name] AS CustomerName
FROM NAMEADDR
WHERE NAMEADDR.[cust-number] NOT IN (SELECT NAMEADDR.[co-number]

FROM NAMEADDR

WHERE NAMEADDR.[cust-number] BETWEEN 1 AND 9999)
ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number];

The above query should give you all unused customer numbers and the
associated branch

Let me repost this:

Select Branch, CustomerNumber, CustomerName
FROM NameADDR
WHERE CustomerNumber NOT IN (SELECT CustomerNumber
FROM NameADDR
WHERE CustomerNumber BETWEEN 1 AND 9999)

You can remove the "NOT" to see all the used CustomerNumbers for each branch.
Just replace my Aliases and replace them with yours

 

[[MVP] S.Clark]

You didn't post the SQL, but I'll guess that you have criteria in the query,
which kinda turns it back to an inner join.

S.
I did this in a query, right joined, but it just shows the numbers that
match up between the 2 tables. I need to know , say in Branch 01 what
numbers
that are used and what are left, or what number that are not used period.
Either way would work. Of the 7 Branches there is a possible of 69993
numbers
available, we have assigned 19063, I was trying to do this by each Branch
seperately, still could not get it to work.........

IIF is never the "right" answer. It may 'work', but it doesn't make it
"right".

You can create a table, and populate it with all of the numbers from 0001
to
9999. Single column, that is.

Link this new table to the AR table, and change the Join Properties to
show
ALL data from the new table. (Select either 2 or 3 from the Dialog box.)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I originally posted this as "Help with the WHERE clause", were I was
using
two different tables. I really can pull the info needed on our current
customer base with just the one.
I am going to try and explain this as best possible. My database is for
Accounts Receivables. We assign new customers a company number, we have
7
Branches, each Branch has a number range between 00001 thru 9999. At
the
moment they are assigned randomly. I need to track numbers used, and
numbers
not used. The query I have now does track what is used, but does not
include
the full range. I have been trying this with the WHERE clause, but I
think
what I'm looking for is an IIF Statement. How can I track what is used
between the 0001 thru 9999 range per BRANCH?

SELECT NAMEADDR.[co-number] AS Branch, NAMEADDR.[cust-number] AS
CustomerNumber, NAMEADDR.[cust-name] AS CustomerName


FROM NAMEADDR

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