Crosstab - what if I don't want my column headings grouped?

[jan.mazing]

Hi - I have a table of date based data that I wish to view as a cross
tab with my dates as column headings. For example, for a particular
location say I have 3 values for Arsenic, all on the same day. How do
I show all 3 values in a cross tab...access wants to group by date.
Any ideas?

thx!

 

[KARL DEWEY]

One way is to include time with sample date collection.
Try this query --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
PIVOT Format([SampleDate],"Short Date");

 

[jan.mazing]

Thanks for the suggestion (I did try it). Unfortunately there is no
time element to the data. I believe what I am going to have to do is
introduce another way to distinguish dates so I can bypass the
grouping.

One way is to include time with sample date collection.
Try this query --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
PIVOT Format([SampleDate],"Short Date");

--
KARL DEWEY
Build a little - Test a little

Hi - I have a table of date based data that I wish to view as a cross
tab with my dates as column headings. For example, for a particular
location say I have 3 values for Arsenic, all on the same day. How do
I show all 3 values in a cross tab...access wants to group by date.
Any ideas?

thx!

 

[KARL DEWEY]

Here is anothe way --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.QTY
PIVOT Format([SampleDate],"Short Date");

--
KARL DEWEY
Build a little - Test a little

Thanks for the suggestion (I did try it). Unfortunately there is no
time element to the data. I believe what I am going to have to do is
introduce another way to distinguish dates so I can bypass the
grouping.

One way is to include time with sample date collection.
Try this query --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
PIVOT Format([SampleDate],"Short Date");

--
KARL DEWEY
Build a little - Test a little

Hi - I have a table of date based data that I wish to view as a cross
tab with my dates as column headings. For example, for a particular
location say I have 3 values for Arsenic, all on the same day. How do
I show all 3 values in a cross tab...access wants to group by date.
Any ideas?

thx!

 

[jan.mazing]

Thx again - this doesn't work because if there are 2 values the same
(AND on the same date), the query won't let you know there were
actually 2 underlying records. Also, in my situation there is actually
more than one compound that wish to include at the same time, the
results is a spreadsheet with lots of holes, because essentially you
have on record per row. I've just spent a couple of hours on this so I
guess my advanced crosstab skills are coming along. In the end, I'm
going to introduce a second, temporary, table which will give my the
control I need. This new table will essentially provided me a proper
field for grouping the data and ensuring nothing gets 'grouped
away'...thx again!

Here is anothe way --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.QTY
PIVOT Format([SampleDate],"Short Date");

--
KARL DEWEY
Build a little - Test a little

Thanks for the suggestion (I did try it). Unfortunately there is no
time element to the data. I believe what I am going to have to do is
introduce another way to distinguish dates so I can bypass the
grouping.

One way is to include time with sample date collection.
Try this query --
TRANSFORM Sum(jan_mazing.QTY) AS SumOfQTY
SELECT jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
FROM jan_mazing
GROUP BY jan_mazing.Location, jan_mazing.Compound, jan_mazing.SampleDate
PIVOT Format([SampleDate],"Short Date");
--
KARL DEWEY
Build a little - Test a little
:
Hi - I have a table of date based data that I wish to view as a cross
tab with my dates as column headings. For example, for a particular
location say I have 3 values for Arsenic, all on the same day. How do
I show all 3 values in a cross tab...access wants to group by date.
Any ideas?
thx!