troubleshooting Question

Trouble getting information out of a query. Need SQL or Expression Help

Avatar of Ceffel
Ceffel asked on
Microsoft AccessSQL
7 Comments1 Solution196 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
andygarratt

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros