Difficult VB code for 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]

I already answered this question for you.. was there something wrong with the response? Did you get some kind of error?

Impossible Query!!!
thread181-105431 Joe Miller
joe.miller@flotech.net

 

[FreddyBotz]

Hey Joe! Yes I am getting an error message upon running the query. Here it is!

"The specified field '[Acct#]' could refer to more than one table listed the the FROM clause of your SQL statement."

Any ideas?

I posted it over here because I didn't want to keep bothering you....!

Thanks again

FB

 

[TTThio]

It means you have more than one field named [Acct#] in the query window. Try to write tbl1.[acct#], tbl2.[acct#].

 

[JoeMiller]

TTThio has it right, you have two or more tables that have Acct# as a field name. You'll need to add the table predicate ("[tblName].&quot in front of every Acct# field in the SQL expression.



Joe Miller
joe.miller@flotech.net

 

[FreddyBotz]

Great, I fixed the problem. However I don't understand how I should implement the rest of this code:

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)


If I'm appearing lost, it's because I am!!!!

Any further advice would be great!

Thanks again guys

FB

 

[JoeMiller]

Can you please send me a copy of your db, then i can set the query up for ya?

Joe Miller
joe.miller@flotech.net