Help forming a query please (1 Viewer)

wrweaver · Mar 14, 2013

The last thing I want to do for the DB I'm making is create a report that shows me the products, quantity of each product, and cost of materials I used through the whole year. I attached the query view with the tables I'm using. To calculate the year, I figured I could sort the aggregate query with the DateCompleted field, but instead it just separated the products. The workorderT contains the materialT which gets the data from the productT. I've tried every combination of things I could think of to get the right data to show up and I really just want to bang my head on my desk. I really appreciate any help.

CJ_London · Mar 14, 2013

please can you post the sql you are using

plog · Mar 14, 2013

Assuming UnitPrice in MaterialT is the cost of the materials, then this should be the SELECT clause of your query:

Code:

SELECT Year(WorkOrderT.DateCompleted) AS YearUsed, ProductID.Description, SUM(MaterialT.Quantity) AS TotalQuantity, SUM(MaterialT.UnitPrice*MaterialT.Quantity) AS TotalPrice

And this would be the GROUP BY clause:

Code:

GROUP BY Year(WorkOrderT.DateCompleted), ProductID.Description

If that doesn't give you what you want, post some sample data from your tables along with what the result should be based on that sample data.

CJ_London · Mar 14, 2013

Hi - This will include all records so there are a couple of solutions depending on your requrements

1. Swap the Year and product columns around and sort both ascending

This will give you

Code:

Product    Year  Quant    TtlPrice
item1       2010  3         14.00
item1       2011  4         20.00
item1       2012  5         23.00
item2       2010  6         7.00
item2       2011  7         14.00
item2       2012  2         2.00

If you only need the current year then the sql needs to be slightly different -

for the year, change Group By to WHERE (this will change show to No.
in the criteria line put [Enter Year] in square brackets so it will prompt you for the year when you run the query. Alternatively put in 2012 if this is fixed

It should produce the following code

Code:

SELECT ProductID.Description, SUM(MaterialT.Quantity) AS TotalQuantity, SUM(MaterialT.UnitPrice*MaterialT.Quantity) AS TotalPrice[COLOR=red] WHERE Year(WorkOrderT.DateCompleted) = [Enter Date] [/COLOR]
GROUP BY ProductID.Description

wrweaver · Mar 15, 2013

What about if the date isn't just a year? It's a whole date like 1/1/2013. So when I try to run the query it says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." If you want me to post a sample of the database and queries I'm using, I will gladly.

plog · Mar 15, 2013

If your talking to CJ, I will step out of the conversation. If you are talking to me, my query addresses that issue.

CJ_London · Mar 15, 2013

It would be helpful if you could indicate where/who your question applies. To do this, mention the responder and/or copy the bit of text you are asking about and surround with quotes (use the advanced reply)

What about if the date isn't just a year?

Assuming you are referring to me

I thought were asking for a solution based on a year. If you want a solution based on a date then the solution is slightly different

Code:

SELECT ProductID.Description, SUM(MaterialT.Quantity) AS TotalQuantity, SUM(MaterialT.UnitPrice*MaterialT.Quantity) AS TotalPrice [COLOR=red]WHERE WorkOrderT.DateCompleted = [Enter Date] 
[/COLOR]GROUP BY ProductID.Description

wrweaver · Mar 15, 2013

Sorry about that Plog. I tried yours but it keeps telling me to enter a parameter value for ProductID.Description.

wrweaver · Mar 15, 2013

I attached a sample here.

wrweaver · Mar 15, 2013

Sorry didn't attach last one.

plog · Mar 15, 2013

This would be the complete SQL:

Code:

SELECT Year(WorkOrderT.DateCompleted) AS YearUsed, ProductT.Description, Sum(MaterialT.Quantity) AS TotalQuantity, Sum(MaterialT.UnitPrice*MaterialT.Quantity) AS TotalPrice
FROM (MaterialT INNER JOIN ProductT ON MaterialT.ProductID = ProductT.ProductID) INNER JOIN WorkOrderT ON MaterialT.WorkOrderID = WorkOrderT.WorkOrderID
GROUP BY Year(WorkOrderT.DateCompleted), ProductT.Description;

wrweaver · Mar 15, 2013

Thank you SO much Plog and CJ!!!! Finally, this thing is done! I can't thank you guys enough! Thank you!

Help forming a query please (1 Viewer)

wrweaver

Registered User.

Attachments

CJ_London

Super Moderator

plog

Banishment Pending

CJ_London

Super Moderator

wrweaver

Registered User.

plog

Banishment Pending

CJ_London

Super Moderator

wrweaver

Registered User.

wrweaver

Registered User.

wrweaver

Registered User.

Attachments

plog

Banishment Pending

wrweaver

Registered User.

Similar threads

Users who are viewing this thread