SQL Pivot in Access

A

Amanda Kelly

I have a table in access that is laid out like the following:
Mo Year Sold Price Mdl_line Mdl_yr Orig_resl_amt Gap VIN
(unique)
aug 2007 13400 PTH 050 17033.3 -5633.3
AlphaNum
aug 2007 13300 PTH 050 19818.1 -8518.1
" "
aug 2007 12000 PTH 050 16669.2 -5669.2
" "
sep 2007 14600 ALT 060 13310.4 1589.6
" "



I want a query to display results that produces the following:

- groups by Mdl_line and Mdl_year
- Mdl_year to read 2005 instead of 050 and 2006 instead of 060
- averages orig_resl_amt and calls it avg_res_amt
- averages Sold Price and calls it avg_sold_amt
- averages Gap and calls it avg_gap
- counts the number of VINs based on the grouping and month/year
- combines Mo and Year and puts them as column headings

Mdl_line Mdl_year Data Aug-07 Sep-07 Oct-07
PTH 2005 avg_res_amt 17840 12345 ...and so on...
--->
PTH 2005 avg_sold amt 12900 23456 ...and so on...
--->
PTH 2005 avg_gap -6607 -7706 ...and so
on... --->
PTH 2005 volume 235 335 ...and so
on... --->
ALT 2006 avg_res_amt 15000 13310
ALT 2006 avg_sold amt 17000 14600
ALT 2006 avg_gap -2000 1590
ALT 2006 volume 15000 15500
*this actually is from my excel pivot...i just want it in access so the
reporting will be easier.

Any help would be greatly appreciated.

Thank you,
 
A

Amanda Kelly

I tried this sql code in access, but it tells me that there is error in my
FROM clause...

SELECT tbl_sales_data_analysis.MDL_LINE,
tbl_sales_data_analysis.FULL_MDL_YR3,
avg(tbl_sales_data_analysis!ORIG_RESL_AMT) as [avg_res_amt],
avg(tbl_sales_data_analysis!SoldPrice) as [avg_sold_amt],
avg(tbl_sales_data_analysis!gap) as [avg_gap],
count(tbl_sales_data_analysis!VIN) as [volume]
FROM tbl_sales_data_analysis
GROUP BY tbl_sales_data_analysis.MDL_LINE,
tbl_sales_data_analysis.FULL_MDL_YR3
PIVOT tbl_sales_data_analysis.repmo;

**repmo = "month - year" from below


What am I doing wrong...someone please help me.
 
B

Bob Barrows

Amanda said:
I have a table in access that is laid out like the following:
Mo Year Sold Price Mdl_line Mdl_yr Orig_resl_amt Gap
VIN (unique)
aug 2007 13400 PTH 050 17033.3
-5633.3 AlphaNum
aug 2007 13300 PTH 050 19818.1
-8518.1 " "
aug 2007 12000 PTH 050 16669.2
-5669.2 " "
sep 2007 14600 ALT 060 13310.4
1589.6 " "



I want a query to display results that produces the following:

- groups by Mdl_line and Mdl_year
- Mdl_year to read 2005 instead of 050 and 2006 instead of 060
- averages orig_resl_amt and calls it avg_res_amt
- averages Sold Price and calls it avg_sold_amt
- averages Gap and calls it avg_gap
- counts the number of VINs based on the grouping and month/year
- combines Mo and Year and puts them as column headings
Click to expand...
Start by using the Crosstab Query Wizard in Access to build your initial
crosstab. look at its sql to see what it's supposed to look like. Get
back to us if you have further questions.
 
Top