|
|
Posted by Ed Murphy on 11/28/32 11:56
On 25 Aug 2006 13:06:29 -0700, "brm6546545"
<russell.allbritain@gmail.com> wrote:
>brm6546545 wrote:
>> I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal,
>> NonTaxableTotal, TaxCollected.
>>
>> Sample data
>>
>> 1,county,10.00,0.00,0.40
>> 1,city,10.00,0.00,0.10
>> 2,state,0.00,15.00,0.15
>>
>> When totaling invoice 1 should have totals of 10.00,0.00,0.50 because
>> the 10.00 is the total for the invoice, but 0.50 is the total tax
>> collected. I nee these totals in a report. In crystal reports i could
>> just do a total on change of invoice number for the Taxable and
>> nonTaxable Totals. but i have to this on an Access adp. I was hoping i
>> could get a query to return something like
>>
>> inv,auth,Taxable,nonTaxable,Collected,TaxableTot,NonTaxableTot,CollectedTot
>> 1,county,10.00,0.00,0.40,10.00,0.00,0.50
>> 1,city,10.00,0.00,0.10,10.00,0.00,0.50
>> 2,state,0.00,15.00,0.15,0.00,15.00,0.15
>>
>> I'm not sure how to do a group by that would allow this, although im
>> sure its possible.
>Edit:
>
>i just realized that the resulting recordset would not help me in doing
>grand totals, just invoice totals, would there also be a way to return
>
>inv,auth,Taxable,nonTaxable,Collected,TaxableTot,NonTaxableTot,CollectedTot,grandTaxable,grandNonTaxable,GrandCollected
>1,county,10.00,0.00,0.40,10.00,0.00,0.50,10.00,15,00,1.15
>1,city,10.00,0.00,0.10,10.00,0.00,0.50,10.00,15,00,1.15
>2,state,0.00,15.00,0.15,0.00,15.00,0.15,10.00,15,00,1.15
(Please don't top-post. Fixed.)
create view vTaxInvoice as
select
InvoiceNum,
sum(TaxableTotal) TaxableTotalInvoice,
sum(NonTaxableTotal) NonTaxableTotalInvoice,
sum(TaxCollected) TaxCollectedInvoice
from tblTax
group by InvoiceNum
create view vTaxGrand as
select
sum(TaxableTotal) TaxableTotalGrand,
sum(NonTaxableTotal) NonTaxableTotalGrand,
sum(TaxCollected) TaxCollectedGrand
from tblTax
select
t.InvoiceNum,
t.TaxAuthority,
t.TaxableTotal,
t.NonTaxableTotal,
t.TaxCollected,
i.TaxableTotalInvoice,
i.NonTaxableTotalInvoice,
i.TaxCollectedInvoice,
g.TaxableTotalGrand,
g.NonTaxableTotalGrand,
g.TaxCollectedGrand
from tblTax t
join vTaxInvoice i on i.InvoiceNum = t.InvoiceNum
cross join vTaxGrand g
Navigation:
[Reply to this message]
|