ACCESS FEED: Re: [MS_AccessPros] Crosstab Queries

Senin, 29 Juli 2019

Re: [MS_AccessPros] Crosstab Queries

There is a dynamic crosstab report example in our group's files section under 1_Samples. Look for "CrosstabReport.zip". IMO, this is the best solution for creating reports from crosstabs when the values and number of columns is not known at run time.

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 29, 2019 2:03 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Crosstab Queries

Please figure out how to include the previous messages with your reply.

So there is a report involved in your question. Dynamic headings in reports can be an issue. Your crosstab uses the Column Headings property ( In ("CR65.A7","KU65.A7") ). This is why the column headings don't change when you change the filter.

If you remove the In () clause your report will break. There are solutions but they involve writing VBA code. I much prefer the method that creates an alias for each column heading value like "A", "B","C", "D",... The MdlCd values are appended to a table and given values of A through whatever. Your report is built from the A through whatever columns.

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of youssef2309@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 29, 2019 1:45 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Crosstab Queries

this is the SQL statement

PARAMETERS [Forms].[Frm_PartsMasterLots].[SelectedModel] Text ( 255 ), [Forms].[Frm_PartsMasterLots].[SelectedAssyItem] Text ( 255 ), [Forms].[Frm_PartsMasterLots].[SelectedPN] Text ( 255 );

TRANSFORM Sum(Qry_PageCrosstab_Xs_S2.QtyPerCar) AS SumOfQtyPerCar

SELECT Qry_PageCrosstab_Xs_S2.AssyPage, Qry_PageCrosstab_Xs_S2.PartNo, Qry_PageCrosstab_Xs_S2.Description, Qry_PageCrosstab_Xs_S2.DispoCode

FROM Qry_PageCrosstab_Xs_S2

WHERE (((Qry_PageCrosstab_Xs_S2.TheSelectedLot) Between [AssyItemStartLot] And [AssyItemEndLot]))

GROUP BY Qry_PageCrosstab_Xs_S2.AssyPage, Qry_PageCrosstab_Xs_S2.PartNo, Qry_PageCrosstab_Xs_S2.Description, Qry_PageCrosstab_Xs_S2.DispoCode, Qry_PageCrosstab_Xs_S2.AssyItemStartLot, Qry_PageCrosstab_Xs_S2.AssyItemEndLot

ORDER BY Qry_PageCrosstab_Xs_S2.AssyPage, Qry_PageCrosstab_Xs_S2.PartNo

PIVOT Qry_PageCrosstab_Xs_S2.MdlCd In ("CR65.A7","KU65.A7");

the a/m query is ready by a report so the column heading is fixed

so, how can these model code ("CR65.A7","KU65.A7") as a column heading automatically changed when i choose a different SERIES

thanks in advance

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>

Reply via web post

•

Reply to sender

•

Reply to group