Re: SQL Pivot in Access

Amanda Kelly wrote:
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



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.

--
HTH,
Bob Barrows


.

Relevant Pages

  • Re: Select query numbering results
    ... The problem with using a crosstab query for a report is that you must ensure it always returns the same number of columns. ... Your main report would be based on a query that only returned one row for each RO Number, RO Date, VIN Number. ... If I had the sequence number I could write the data to a table and then construct a cross tab query where the across was on the sequence number and the part numbers would simply be data. ...
    (microsoft.public.access.gettingstarted)
  • Re: Select query numbering results
    ... My use of the term report ... If you really feel that you must use a ranking query to ... only returned one row for each RO Number, RO Date, VIN Number. ... Another option is to use a query that uses Duane Hookom's Concatenate ...
    (microsoft.public.access.gettingstarted)
  • Re: Append query adds way too many entries into table, instead of just
    ... Try the alternative syntax for an insert query. ... INSERT INTO T_PlateHistory (Plate, Vin, Assigned) ... I have an append query that pulls a VIN, a Plate number, and today's date and enters it into a history table. ...
    (microsoft.public.access.queries)
  • Re: Query sorting
    ... the vins with empty ones also. ... vin option ... I need to merge none blank field into a field (in a query). ... Change all the "qryVINS" with the name of the query and the proper field ...
    (microsoft.public.access.queries)
  • SQL Pivot in Access
    ... I have a table in access that is laid out like the following: ... I want a query to display results that produces the following: ... averages Sold Price and calls it avg_sold_amt ...
    (microsoft.public.access.queries)
Quantcast