Attn: Joe_Miller

[FreddyBotz]

Joe, you have an enourmous amount of patience. Thank you! Below you'll find my initial inquiry. I hope I explained it well. If you had any idea how long it takes me to acquire the desired results, you'd understand why I'm in desperate need of assistance. Here it goes:

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

 

[dawnd3]

Freddy, when you are in query design view, look to the leftmost button on the toolbar (the "veiw" button) Instead of clicking on it, click on the dropdown arrow on the right side, and you will see "SQL Veiw" as a choice. Click on that, and in this window is where you would paste the code you were given for your query. Dawn

 

[JoeMiller]

Check your inbox on AOL, I made a sample DB for ya.

Joe Miller
joe.miller@flotech.net

 

[FreddyBotz]

In place of the existing code or underneath it? Joe would you mind if I sent you an unlinked database that's about 1/4 the size of the original?

Thanks again

FB

 

[JoeMiller]

Look at my sample database, forget about everything you've done so far and look at the qrytbl to see how I did it. Please do not send me anything. Joe Miller
joe.miller@flotech.net

 

[FreddyBotz]

Thanks Joe for the help. The formla you've created is definitely what I've been looking for. Though, if you don't mind, I would need one more minor adjustment which will finally bring this matter to a close.

On the query that you've sent, how would you alter the formula to allow AB12345 400 1000 to read
AB12345 400 1500

The intention is to have only the selected units units sum (which you did), and to have all of the corresponding dollars add up.

I really do appreciate your time and patience.

Thanks Again

FB

 

[JoeMiller]

Just add the appropriate or clause in the AcctNo field:
[tt]
AcctNo: IIf(Right([Acct#],1)="A" Or Right([Acct#]),1)="B" Or Right([Acct#],1)="C" Or Right([Acct#],1)="D" Or Right([Acct#],1)="E" Or Right([Acct#],1)="F",Left([Acct#],Len([Acct#])-1),[Acct#])
[/tt]
I only tested for A, C, and F in my sample because you said up above only accounts that end in A, C, D, or F (I missed the D, my bad). Up above I changed the code to test for A,B,C,D,E or F, all the stuff in RED is new.

HTH Joe Miller
joe.miller@flotech.net

 

[FreddyBotz]

I tried the revised formual and a message pops up stating that this funtion contains the wrong number of arguments. Any suggestions?

Will the new code allow only the selected units to sum while at the same time sum up each of the account #'s dollar amount.

Thanks again

FB

 

[FreddyBotz]

I tried the revised formula and a message pops up stating that this function contains the wrong number of arguments. Any suggestions?

Will the new code allow only the selected units to sum while at the same time sum up each of the account #'s dollar amount.

Thanks again

FB

 

[FreddyBotz]

Disregard the above message Joe. It appears that I just needed to remove a bracket from the formula. The query works exactly the way I want it to!!! I owe you one. If you're ever in NY, drinks are on me!

Regards

FB

 

[JoeMiller]

I'm in NY all the time, I live in CT. Hail the mighty liquor gods!



Joe Miller
joe.miller@flotech.net