Help with advanced SQL statement

[jasonstewart]

I have a MS Access 2000 database that keeps track of multiple invoices and within each invoice there are multiple line items with the same ServiceRecordID (PrimaryKey.) What I need to do is total the Parts + Tax and Labor I have an update table that totals all the labor and all the parts from a particular ServiceRecordID and places an L or a P in a forward column ie. The column headings are ServiceRecordID – Category – Total Price and the data is as follows 3000 – L – 255.00 then next row 3000 – P – 156.75 and I need to pull the parts out and add tax and place the Total Parts Excluding tax in another table with the same primary key and then the total labor in the same other table as Total Parts and place a tax amount just in the column next to the Total Parts. I have managed to use another Query to place the total for the invoice and a total tax for everything labor and parts.

I know that is a lengthy description and I hope that it is thorough enough to help you help me!! Thank you for the help and any suggestions.

If you need any additional information please just ask.

 

[JeremyNYC]

Jason,

I read through this, but I didn't understand what your question is. Probably best if you post the relevant fields of your tables and the sql of relevant queries and then make it clear what you need help on.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[zerosandones]

This one's fairly simple ... I've added a date-effective tax rate table too in case you find it useful.

1) Parent invoice table: tblInvoice (ServiceRecordID, InvoiceDate) - Primary key: ServiceRecordID

Sample data:

ServiceRecordId InvoiceDate
=========== ===========
1 1/1/02
2 2/10/02
3 6/5/02

2) Invoice detail table: tblInvoiceDetail (ServiceRecordDetailId, ServiceRecordId, Category, TotalPrice) - Primary key: ServiceRecordDetailId Foreign key: ServiceRecordId

Sample data:

ServiceRecordId ServiceRecordId Category TotalPrice
========== =========== ====== ==========
1 1 P 10.00
2 1 P 25.00
3 1 L 125.00
4 2 P 50.00
5 2 L 100.00
6 3 P 12.00
7 3 P 75.00
8 3 P 11.00
9 4 L 175.00

3) Tax rate table (with date effectivity): tblTaxRate (TaxRateEeffective, TaxRate) - Primary key: TaxRateEffective

Sample data:

TaxRateEffective TaxRate
=========== =======
1/1/02 .07 i.e. 7%
1/5/02 .075 i.e. 7.5%

So, with all this sample data and table structure in place, the SQL to make it all happen is as follows:

SELECT tblInvoice.ServiceRecordID, Sum(Switch([Category]="L",[TotalPrice],True,0)) AS [Total Labor], Sum(Switch([Category]="P",[TotalPrice],True,0)) AS [Total Parts Excl Tax], Sum([TotalPrice])*[TaxRate] AS [Total Tax], tblTaxRate.TaxRate
FROM tblTaxRate, tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.ServiceRecordID = tblInvoiceDetail.ServiceRecordID
WHERE (((tblTaxRate.TaxRateEffective)=(SELECT Max(TaxRateEffective) FROM tblTaxRate WHERE TaxRateEffective <= tblInvoice.InvoiceDate)))
GROUP BY tblInvoice.ServiceRecordID, tblTaxRate.TaxRate;

If you examine it closely, you'll see that it correctly determines the maximum TaxRateEffective date from the Tax table having a date less than or equal to the InvoiceDate for the invoice being tabulated and then applies that tax rate to the equation.

Hope this does it for you.

00001111s

 

[jasonstewart]

Okay, I do have them sorting 5 different parts and 3 different labors to the same ServiceRecordID so I have a table called Items and that has data in it for example.

SourceRecordID Category Rate Qty
3000 P 135.00 2
3000 P 23.56 4
3000 L 65.00 5
3000 P 12.00 2
3001 L 65.00 8

I have the ability to turn that information into another table that is then layed out like this

SourceRecordID Category InvoiceTotal
3000 P 388.24
3000 L 325.00
3001 L 520.00

I need to add a fixed sales tax(0.07616) to the parts column and add the total to another table, which already exists, that will look like this

SourceRecordID TotalParts TotalLabor
3000 417.81 325.00

That is what I need the finished table to look like