dynamic crosstab problems

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

I have a report based on Duane Hookom’s dynamic cross tab. It is generally
working fine in that my column alias is working, as are the different levels;
however, there are nuances that are snagging my overall progress in
completing the report. First, I am trying to get the report to look like
this:
Sample Date Location 1 Location 2 Location 3 Location 4 Location 5
Parameter 1 value value value value value
Parameter 2 value value value value value
Parameter 2 value value value value value
Parameter 2 value value value value value
etc.
etc.
Total: sum sum sum sum sum
The report is grouped on Sample Date and since there is only one date it
works fine. I tried to group on Level but then a weird data came up at each
level.

The two nuances that I am having trouble with are:
(1) each location has the same 20 parameters, so I get something that looks
like this:
Sample Date Location 1 Location 2 Location 3 Location 4 Location 5
Parameter 1 value
Parameter 1 value
Parameter 1 value
Parameter 1 value
Parameter 1 value
etc.
Total: sum
and so on. I tried grouping on the parameter but that does not work because
then it totals by parameter and I want a total of all the parameters per
location.

(2) The total is not totaling in each column. As shown above, there is only
one total per level!?

I am not sure what you all might need to help me – SQL, etc. Please let me
know and I will post. Thanks for looking at this.
 
I'm not sure why you can't group on Parameter. You should then be able to
also total by SampleDate.

It might help if you described your base table structure.
 
Duane & Karl,

Here is the SQL:
PARAMETERS [Forms]![frmIDreports]![cboparam] Text ( 255 ); TRANSFORM
First(tblHardage.RESULT) AS FirstOfRESULT SELECT tblHardage.PARAMETER,
tblHardage.SAMPLE_DATE, tblIDAlias.Level, tblHardagePAR.F1_GRPORDR FROM
tblIDAlias INNER JOIN (tblHardagePAR INNER JOIN (tblHardage INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardagePAR.PARAMETER =
tblHardage.PARAMETER) ON (tblIDAlias.STATID =
tblHardageSiteIdentification.STATID) AND (tblIDAlias.SAMPLE_DATE =
tblHardage.SAMPLE_DATE) AND (tblIDAlias.STATION_ID = tblHardage.STATION_ID)
WHERE (((tblHardagePAR.F1_GROUP)=[Forms]![frmIDreports]![cboparam])) GROUP BY
tblHardage.PARAMETER, tblHardage.SAMPLE_DATE, tblIDAlias.Level,
tblHardagePAR.F1_GRPORDR, tblIDAlias.STATION_ID PIVOT tblIDAlias.ColumnAlias
In ("A","B","C","D","E");
 
I'm not sure why your GROUP BY includes tblIDAlias.STATION_ID.

--
Duane Hookom
Microsoft Access MVP


javablood said:
Duane & Karl,

Here is the SQL:
PARAMETERS [Forms]![frmIDreports]![cboparam] Text ( 255 ); TRANSFORM
First(tblHardage.RESULT) AS FirstOfRESULT SELECT tblHardage.PARAMETER,
tblHardage.SAMPLE_DATE, tblIDAlias.Level, tblHardagePAR.F1_GRPORDR FROM
tblIDAlias INNER JOIN (tblHardagePAR INNER JOIN (tblHardage INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardagePAR.PARAMETER =
tblHardage.PARAMETER) ON (tblIDAlias.STATID =
tblHardageSiteIdentification.STATID) AND (tblIDAlias.SAMPLE_DATE =
tblHardage.SAMPLE_DATE) AND (tblIDAlias.STATION_ID = tblHardage.STATION_ID)
WHERE (((tblHardagePAR.F1_GROUP)=[Forms]![frmIDreports]![cboparam])) GROUP BY
tblHardage.PARAMETER, tblHardage.SAMPLE_DATE, tblIDAlias.Level,
tblHardagePAR.F1_GRPORDR, tblIDAlias.STATION_ID PIVOT tblIDAlias.ColumnAlias
In ("A","B","C","D","E");
--
javablood


KARL DEWEY said:
Post the SQL of your crosstab query.
Click to expand...
Click to expand...
 
Duane,

Because sometimes the obvious eludes me! Taking that out solved the problem.

Thanks yet again for your hlep.
--
javablood


Duane Hookom said:
I'm not sure why your GROUP BY includes tblIDAlias.STATION_ID.

--
Duane Hookom
Microsoft Access MVP


javablood said:
Duane & Karl,

Here is the SQL:
PARAMETERS [Forms]![frmIDreports]![cboparam] Text ( 255 ); TRANSFORM
First(tblHardage.RESULT) AS FirstOfRESULT SELECT tblHardage.PARAMETER,
tblHardage.SAMPLE_DATE, tblIDAlias.Level, tblHardagePAR.F1_GRPORDR FROM
tblIDAlias INNER JOIN (tblHardagePAR INNER JOIN (tblHardage INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardagePAR.PARAMETER =
tblHardage.PARAMETER) ON (tblIDAlias.STATID =
tblHardageSiteIdentification.STATID) AND (tblIDAlias.SAMPLE_DATE =
tblHardage.SAMPLE_DATE) AND (tblIDAlias.STATION_ID = tblHardage.STATION_ID)
WHERE (((tblHardagePAR.F1_GROUP)=[Forms]![frmIDreports]![cboparam])) GROUP BY
tblHardage.PARAMETER, tblHardage.SAMPLE_DATE, tblIDAlias.Level,
tblHardagePAR.F1_GRPORDR, tblIDAlias.STATION_ID PIVOT tblIDAlias.ColumnAlias
In ("A","B","C","D","E");
--
javablood


KARL DEWEY said:
Post the SQL of your crosstab query.
--
Build a little, test a little.


:

I have a report based on Duane Hookom’s dynamic cross tab. It is generally
working fine in that my column alias is working, as are the different levels;
however, there are nuances that are snagging my overall progress in
completing the report. First, I am trying to get the report to look like
this:
Sample Date Location 1 Location 2 Location 3 Location 4 Location 5
Parameter 1 value value value value value
Parameter 2 value value value value value
Parameter 2 value value value value value
Parameter 2 value value value value value
etc.
etc.
Total: sum sum sum sum sum
The report is grouped on Sample Date and since there is only one date it
works fine. I tried to group on Level but then a weird data came up at each
level.

The two nuances that I am having trouble with are:
(1) each location has the same 20 parameters, so I get something that looks
like this:
Sample Date Location 1 Location 2 Location 3 Location 4 Location 5
Parameter 1 value
Parameter 1 value
Parameter 1 value
Parameter 1 value
Parameter 1 value
etc.
Total: sum
and so on. I tried grouping on the parameter but that does not work because
then it totals by parameter and I want a total of all the parameters per
location.

(2) The total is not totaling in each column. As shown above, there is only
one total per level!?

I am not sure what you all might need to help me – SQL, etc. Please let me
know and I will post. Thanks for looking at this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top