 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005IIF
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]; 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.)  SignatureSteve 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 [quoted text clipped - 16 lines] > > ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number]; 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]; 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 [quoted text clipped - 33 lines] > > > > > > ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number]; >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 >>S. >>I did this in a query, right joined, but it just shows the numbers that [quoted text clipped - 13 lines] >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 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 [quoted text clipped - 41 lines] >> > >> > ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number]; >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 [quoted text clipped - 14 lines] > >ORDER BY NAMEADDR.[co-number], NAMEADDR.[cust-number]; You need a NOT EXISTS subquery |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.