Senin, 29 Juli 2019

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 Start a New Topic Messages in this topic (6)

SPONSORED LINKS
.

__,_._,___

Tidak ada komentar:

Posting Komentar