Problem with Select Multiplication

[jasonstewart]

I have a database that keeps different line items on an invoice with the same 'ServiceRecordID' then uses 'Category' to seperate labor from parts (L and P respectively) I need to total the amount of labor and the amount of parts from each invoice. How might I do this with a SQL statement? Thank you for any help!

 

[IFELSETHEN]

Hi, you can use a sub-select statement like this.

SELECT (SELECT SUM(amount) FROM invoice WHERE ServiceRecID = '12345' AND Category = 'L') AS LABOR, (SELECT SUM(amount) FROM invoice WHERE ServiceRecID = '12345' AND Category = 'P') AS PARTS

FROM invoice
ORDER BY ServiceRecID

John

 

[jasonstewart]

Where would I place this statement? Behind the text box I wanted filled? - Sorry, new to ACCESS

 

[IFELSETHEN]

Sorry, didn't know your skill level. Sorry I can't give you more help. I would write a function that would return the value based on the given pramaters. John

 

[zerosandones]

The SQL is simple. The choice is really one of presentation. The following SQL will SUM all amounts for a given ServiceRecordID broken down by labor and parts.

TRANSFORM Sum(tblInvoice.Amount) AS InvoiceAmount
SELECT tblInvoice.ServiceRecordID
FROM tblInvoice
GROUP BY tblInvoice.ServiceRecordID
PIVOT tblInvoice.Category;

This SQL can be saved and run as a query or included as the record source for a report.

Hope this helps,

00001111s

 

[jasonstewart]

I have tried this but I can't seem to get it to work, it just keeps telling me that I have an Invalid Context maybe entered an operand without an operator. I am using the Table: Items
I made the SQL look like this:
TRANSFORM Sum(Items.Amount) AS InvoiceAmount
SELECT Items.ServiceRecordID
FROM Items
GROUP BY Items.ServiceRecordID
PIVOT Items.Category

If there is any more info you need just let me know.

Thank you so much for your help

 

[zerosandones]

Here are a couple of questions I have that might help solve this one:

1) Are you running this SQL as a saved Query ? If so, can you view the query in design view as well as SQL view ?

2) Is Items a local Access table and, if not, is it a linked Access table or a linked ODBC datasource e.g. and ORACLE or SQL Server table?

3) Can you provide a "table describe" of the Items table including all field names, data types (i.e. Text, Number etc.), and what the Primary key is if any ?

Thanks.

00001111s

 

[jasonstewart]

1) It is not in query, it is in expression builder
2) It is a local access table
3) Field Names: in this order:
OID - Automatic number that is created by sync manager
ServiceRecordID: 4 digit number
Item: Name or Part Sold
Description: Description of work performed
Comments: Another Text field
Unit: HR-Hour or EA-Each depending on Labor or Part
Category: L, P, S - (L)abor, (P)art, or (S)ervice
SerialNumber: Numeric
CurrentPrice: (Not Used)
Qty: Quantity of specific Part or Labor in Hours
Total: Extended UnitPrice and Quantity

Thank you for your help