Ceffel
asked on
Trouble getting information out of a query. Need SQL or Expression Help
Hi Experts:
I have a table of 49k rows. It shows all order lines with Part id, qty (per line item), the fiscal year for each line item and total order value for that line (qty*unit price).
I need to find a way to show by fiscal year, the part id (one time) its total fy07 qty sold, total fy order volume and then beside that show the following fiscal year information and see if it decreased in unit price or increased.
(the data is by line items)
Fiscal Year Product Code Part ID Order Qty Order Value
2007 200 ABC 5 23.50
2007 200
2008 300
2007 100
2008 100
2007 300
2008 200 ABC 5 25.00
Overall what I need to show off to the right side of all of this information is a summary of Part ID like this
FY07 Order Qty FY07 Order Volume Fy07 Avg Price FY08 Order Qty FY08 Order Volume FY08 Avg Price Increase/Decrease. Essentially above it would've shown that the Fy07 price was 4.70 and the Fy08 price was 5.00 and increased by 6%. The thing is not to let the part id repeat itself 200 times because its line items from orders and it needs to be grouped by part id and also that part id qty sold per year and overall order volume per that part id for that year and then compared.
Any suggestions or did I make sense?
I have a table of 49k rows. It shows all order lines with Part id, qty (per line item), the fiscal year for each line item and total order value for that line (qty*unit price).
I need to find a way to show by fiscal year, the part id (one time) its total fy07 qty sold, total fy order volume and then beside that show the following fiscal year information and see if it decreased in unit price or increased.
(the data is by line items)
Fiscal Year Product Code Part ID Order Qty Order Value
2007 200 ABC 5 23.50
2007 200
2008 300
2007 100
2008 100
2007 300
2008 200 ABC 5 25.00
Overall what I need to show off to the right side of all of this information is a summary of Part ID like this
FY07 Order Qty FY07 Order Volume Fy07 Avg Price FY08 Order Qty FY08 Order Volume FY08 Avg Price Increase/Decrease. Essentially above it would've shown that the Fy07 price was 4.70 and the Fy08 price was 5.00 and increased by 6%. The thing is not to let the part id repeat itself 200 times because its line items from orders and it needs to be grouped by part id and also that part id qty sold per year and overall order volume per that part id for that year and then compared.
Any suggestions or did I make sense?
ASKER
When I select group by, should I change under fiscal year and product to anything from the drop down or just select the 'Totals' icon within the query that brings in the group by options??
If I could just get the fiscal year part id annual sales and total qty sold, I can pull into Excel and manually drag down formulas to get the avg price and then year over year increase or decrease if I had too.
If I could just get the fiscal year part id annual sales and total qty sold, I can pull into Excel and manually drag down formulas to get the avg price and then year over year increase or decrease if I had too.
Hi,
Do you have the SQL that access is producing?
Andy
Do you have the SQL that access is producing?
Andy
ASKER
Yes but of course what I'm producing right now is not working even when I select the Group By, but that may be because I'm not applying the proper Group by selection under the specific categories (FY and Part ID).
SELECT [FY07 and FY08 PARTS].FY, [FY07 and FY08 PARTS].PRODUCT_CODE, [FY07 and FY08 PARTS].PART_ID, [FY07 and FY08 PARTS].ORDER_QTY, [FY07 and FY08 PARTS].[Order Value]
FROM [FY07 and FY08 PARTS];
Hi,
Do you need Product Code? What is this field? If you remove Product Code you can then Group By FY and PART_ID.
Do you need Product Code? What is this field? If you remove Product Code you can then Group By FY and PART_ID.
SELECT [FY07 and FY08 PARTS].FY, [FY07 and FY08 PARTS].PART_ID, SUM([FY07 and FY08 PARTS].ORDER_QTY) AS SUM_ORDER_QTY, SUM([FY07 and FY08 PARTS].[Order Value]) AS SUM_ORDER_VALUE
FROM [FY07 and FY08 PARTS]
GROUP BY [FY07 and FY08 PARTS].FY, [FY07 and FY08 PARTS].PART_ID;
ASKER
I do need Product Code in the details of which the part id's fall into. It is a category by part placement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you do a GROUP BY on the Year and Product you can then bring back the averages. If you want to summarise the data even more, ie. % increase on previous year I would suggest populating a temporary table and then summarising the data in there.
If this is what you want to achieve let me know. I'm not aware of a way to efficently summarise the data any other way based on the number of records you are talking about.
Andy