Impossible Query!!!

[FreddyBotz]

Good morning. My initial description of the problem was incorrect which brings me back again with my dilemma. I’m almost there, so I just have a few more questions. I have a database consisting of 50,000+ lines of data, which are broken down into regions. e.g. East, West, Southwest.... and so on. I run a query by region, which then breaks it down into subjects…e.g. Reading, Math, Science…. etc. Problem is this-there are certain account numbers that are not only duplicated but end in a letter.


Region Acct# Units Dollars
East
Reading AB12345a 100 500
AB12345b 200 500
AB12345c 300 500

AB2468a 400 600
AB2468b 500 600
AB2468c 600 600
-----------------------------------------------------
Math SM.2468390 100 500
SM.2468391 200 500
SM.2468392 300 500

Here's where it gets complicated. I need a total sum of the dollars column BUT I ONLY need those account numbers that end in an "a" or a "c" OR a “d” and an “f” in the Unit column to sum. Also, there are account numbers that end in a different letter or none at all accompanied by 9 and sometimes 10 characters. I would like the desired results to appear as follows:

Region Acct# Units Dollars

East

Reading AB12345 400 1500
AB2468 1000 1800
Total 1400 3300


Math SM.2468390 100 500
SM.2468391 200 500
SM.2468392 300 500
Total 600 1500

Here is a formula that Terry was kind enough to post. The problem is that I’m not quite familiar with placing code in Access. Could someone please break down the provided formula and instruct me how to implement into the query? I would greatly appreciate it!!!

Select Region, Left([Acct#],7) As AcctNo, Sum([dollars]) As TotDollars
From tbl
Where Right([Acct#],1) In ("a", "c&quot
Group By Region, Left([Acct#],7)



Regards

FB

 

[JoeMiller]

Insert a column in your query and put this expression in it:

LastLetter: Right([Acct#],1)

The LastLetter column will now give you the only the last letter of your account number. In the criteria for that column put in this:

"a" or "c" or "d" or "f"

now your query will only pull accounts that end in a,c,d or f.

HTH Joe Miller
joe.miller@flotech.net