Query Problem

[marty]

I have a table that looks like this:

Lon Lat Chan RSSI BSIC

82.1 40.1 333 -80 2
82.1 40.1 334 -96 43
83.2 40.1 333 -88 6
83.2 40.1 334 -72 43
84.4 40.1 333 -88 2
84.4 40.1 334 -64 50
84.4 40.2 333 -90 2
84.4 40.2 334 -67 50

I need a query that will take the above table and output it to this: The
group by would be on the Lon Lat fields

Lon Lat CH_333 BSIC_333 CH_334
BSIC_334
82.1 40.1 -80 2 -96
43
83.2 40.1 -88 6 -72
43
84.4 40.1 -88 2 -64
50
84.4 40.2 -90 2 -67
50

I already have a table with the fields mentioned above. To be technical I
have a table with 36 different channel and 36 bsic fields. Is this possible?
I'm totally stumped.

 

[Douglas J. Steele]

You should be able to use a Crosstab query to do this. Create a new query,
and select the Crosstab Query Wizard.

 

[marty]

I just tried a crosstab query but I guess I do not know how to use it because
it definatly did not work. I need a unique field for every unique Chan.
There are 36 of them. I just put two in the example for simplicity. My
example also shows that I need a group by on Lon Lat. I just do not know how
to do this in a crosstab query. Do you know where I might be able to learn
more about crosstab queries on the web? Or maybe another route to achieving
the desired output?

Marty

 

[Douglas J. Steele]

Sorry, not sure of resources to learn more about crosstabs.

And I may have mislead you slightly: I don't think it's possible to
consolidate both the RSSI and BSIC in a single query.

The SQL to get what you want for RSSI only would be:

TRANSFORM Sum(RSSI) AS [The Value]
SELECT Lon, Lat
FROM NewsgroupData
GROUP BY Lon, Lat
PIVOT "RSSI-" & [Chan];

For BSIC only,

TRANSFORM Sum(BSIC) AS [The Value]
SELECT Lon, Lat
FROM NewsgroupData
GROUP BY Lon, Lat
PIVOT "BSIC-" & [Chan];