sum of rate for each account ?

[vinhtang]

Hi all,
What I need is to get the total rate for each account and write it to a new table with that distinct account# (strap) & its total rate.

table: parcel_tax

strap tax_dist
12345 001
12345 002
12345 003
12345 004
01111 001
01111 007
01111 003

table: tax_dist

cd curr
001 1.1
002 0.5
003 2.0
004 0.1

Below is what I have so far. The problem is I suspect this way takes a very long time since I have more than 10 records in table parcel_tax. There must be a better way to do this just that I do not know. Any help is greatly appreciated.



fetch next from cRoll into @acct

while (@@fetch_status <> -1) begin
set @tax_rt = 0.0

select
tax_rt = sum(curr)
from tax_dist
where cd in
(select tax_dist from parcel_tax where strap = @acct)

fetch next from cRoll into @acct
end
close cRoll
deallocate cRoll

set nocount off

 

[SkipVought]

Hi,

Why not use MS Query, via Data/Get External Data/New Database Query -- Excel Files -- YOUR WORKBOOK...

Join your two tables on the tax district code and SUM the current rate.

Should take less than three minutes, start to finish.

Skip,

_[red][/red]

 

[vinhtang]

Thanks for your advice. I've tried this code when i export the data but I get NULL value for totalRate. Could you please take a look to see what I did wrong ? Please!

SELECT dbo.parcel_tax.strap, SUM(dbo.lu_tax_dist.curr) AS TotalRate
FROM dbo.parcel_tax LEFT OUTER JOIN
dbo.lu_tax_dist ON dbo.parcel_tax.tax_dist = dbo.lu_tax_dist.cd
GROUP BY dbo.parcel_tax.strap

 

[SkipVought]

Does the CODE column have leading or trailing spaces?

You may need to TRIM in the From clause

FROM         dbo.parcel_tax LEFT OUTER JOIN
                      dbo.lu_tax_dist 
  ON TRIM(dbo.parcel_tax.tax_dist) = TRIM(dbo.lu_tax_dist.cd)

Skip,

_[red][/red]

 

[vinhtang]

There's no space. It must be something wrong with my SQL statement.

 

[SkipVought]

I took your data example and coded a solution that worked.

Your SQL is similar to mine

[i]yours[/i]
SELECT     PT.strap, SUM(TD.curr) AS TotalRate

FROM              dbo.parcel_tax   PT
LEFT OUTER JOIN   dbo.lu_tax_dist  TD
  ON PT.tax_dist = TD.cd

GROUP BY PT.strap

===================================================
[i]mine in MS Query[/i]
SELECT    PT.strap, Sum(TD.curr) AS 'TotRate'

FROM {oj        `D:\My Documents\vba\dbTax`.`parcel_tax$` PT
LEFT OUTER JOIN `D:\My Documents\vba\dbTax`.`tax_dist$`   TD
  ON PT.tax_dist = TD.`cd `}

GROUP BY PT.strap

Skip,

_[red][/red]

 

[SkipVought]

My resultset
[tt]
strap 'TotRate'
01111 3.1
12345 3.7

[/tt]


Skip,

_[red][/red]

 

[SkipVought]

Check the data type on BOTH your join codes.

They should BOTH be TEXT - 3 in length.

You probably have one as a NUMBER of some sort.

Skip,

_[red][/red]

 

[SkipVought]

FYI,

Part Numbers, Invoice Numbers, SSNs, strap, parcel ids, etc...

are IDENTIFIERS, not NUMBERS, even if there are comprised entirely of numeric characters.

You will NEVER do a calculation on an IDENTIFIER.

As such, IDENTIFIERS should ALWAYS be saved as CHARACTER.

Leading ZEROS are extremely significant for IDENTIFIERS. For NUMBES they are not.

Skip,

_[red][/red]

 

[vinhtang]

Thank you so much for your help. It was very stupid of me. I used the wrong table. I should use last year table for rate cuz we do not have any data yet for current year. I apologize for wasting so much of your time.

 

[SkipVought]

No problem. Glad you got it worked out!

Skip,

_[red][/red]