dynamic crosstab problems

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.
 
D

Duane Hookom

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.
 
J

javablood

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");
 
D

Duane Hookom

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...
 
J

javablood

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding Network Trusted Location on MS O365 Word per Computer Registry Setting 1
Creating a Chart on form from selected data in query. 0
Finding the right style field in VBA 0
Need some PWA help 0
V Lookup multiple entires 1
parameter prompt problem 2
SynNeed to account for null values in subreports 2
Can't calculate Grand Total of several other expressions 1

Top