Good morning. I have a database co

[FreddyBotz]

Good morning. 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, copy the results to excel and provide subtotals. Problem is this-there are certain account numbers that are not only duplicated but end in a letter.

Acct# Units Dollars

AB12345a 100 500
AB12345b 200 500
AB12345C 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" to sum. If anyone can assist me in creating a code to assist me, it would be greatly appreciated.

Thank you

FB

 

[DougP]

First use Access in its entirety, leave Excel out of it.
then you can specify more criteria in Access and create a report that totals too.

If you create "make table" queries you can do it in steps.
you can use your existing query and just change it to a "Make table" by clicking the "Query" menu item then "Make Table". Give it a meaningful name.
Create a Temporary table that breaks it down in East West etc.
Then run another query on that new table with the second set of criteria and so on.
This is not the most efficient way, but it gets you there quicker than making one complicated query.
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[FreddyBotz]

Thanks for the advice Doug, but how can I eliminate the problem with the duplicate account numbers?

FB

 

[tlbroadbent]

Is this what you are looking for?

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) Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[FreddyBotz]

Thanks for the response Terry! However, I believe I mis-stated my true intention. I still want the Dollars column to sum. As far as the Units column is concerned, I only want those account #'s to sum that end in an "a" or an "r". Any other account number that doesn't end in a letter, can simply carry over the "unit" corresponding with that account number. My original message indicated that I wanted the account numbers to sum. Thanks Again!

FB