Re: SQL to group data in columns

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

What are typical values in your table? Did you really want the "Var" of the
value or do you want Avg or Min or Max or First?

--
Duane Hookom
MS Access MVP
--

"Ron H" <rharshbarger@xxxxxxx> wrote in message
news:42cd25e9$1_19@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Lynn,
> Thanks for the tip. I used the wizard and generated the folowing SQL
> staement. It returns a table with the correct number of rows and columns
> and
> with the correct row headings but no column headings OR data! What did I
> miss?
>
> TRANSFORM Var(ScanData.Data) AS VarOfData
> SELECT ScanData.Point
> FROM ScanData
> GROUP BY ScanData.Point
> PIVOT ScanData.Scan;
>
> Ron H.
>
>
>
> "Lynn Trapp" <ltrapp_NOSPAM@xxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:OzuNvcXgFHA.2152@xxxxxxxxxxxxxxxxxxxxxxx
>> You probably need to use a Crosstab query for this.
>>
>> --
>> Lynn Trapp
>> MS Access MVP
>> www.ltcomputerdesigns.com
>> Access Security: www.ltcomputerdesigns.com/Security.htm
>> Jeff Conrad's Access Junkie List:
>> http://home.bendbroadband.com/conradsystems/accessjunkie.html
>>
>>
>>
>> "Ron H" <rharshbarger@xxxxxxx> wrote in message
>> news:42ca9854$1_7@xxxxxxxxxxxxxxxxxxxxxxxxx
>> >I have a table with test, scan, point, and data fields.
>> >
>> > for each test there are many scans and for each scan there are a many
>> > predefined points. (always the same number of points). There is 1 piece
> of
>> > data for each predefined point in each scan in each test.
>> >
>> > PROBLEM:
>> >
>> > I need to write a query that returns a column of data for each scan
> sorted
>> > by point.
>> >
>> > Point Scan 1 Scan 2 Scan n
>> > 1 29.0 28.7 30.0
>> > 2 30.1 29.5 30.1
>> > 3 28.7 29.4 29.9
>> > .
>> > .
>> > .
>> > .
>> > 240 26.9 28.6 30.3
>> >
>> >
>> > If I use subqueries to define columns, they only return a single
>> > row....
>> > How
>> > can I do this?
>> >
>> >
>> > Ron H.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > x-- 100 Proof News - http://www.100ProofNews.com
>> > x-- 30+ Days Binary Retention with High Completion
>> > x-- Access to over 1.9 Terabytes per Day - $8.95/Month
>> > x-- UNLIMITED DOWNLOAD
>> >
>>
>>
>
>
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 30+ Days Binary Retention with High Completion
> x-- Access to over 1.9 Terabytes per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>


.

Relevant Pages

  • Re: SQL to group data in columns
    ... with the correct row headings but no column headings OR data! ... > You probably need to use a Crosstab query for this. ... > Lynn Trapp ... >> Ron H. ...
    (microsoft.public.access.queries)
  • Re: Months in column report
    ... > Also, the original query that was used to start the report, and still used ... >> of the crosstab query. ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: CROSSTAB QUERY: Combining text across columns
    ... >>Duane Hookom ... >>MS Access MVP ... >>> Subject: CROSSTAB QUERY: Combining text across ... >>> columns into one text string. ...
    (microsoft.public.access.reports)
  • Re: Flatten Normalized data
    ... A crosstab query will do this. ... Duane Hookom ... MS Access MVP ... > every user, there can be 2-5 recs, each rec may or may not have values for ...
    (microsoft.public.access.queries)
  • Re: Formatting columns into report
    ... Are you Dee or are you jumping into the thread? ... Are your column headings ... MS Access MVP ... >>What you're asking for looks a lot like a Crosstab query. ...
    (microsoft.public.access.reports)