CROSSTAB build headers from 2nd table

[easyit]

Hi,

I've a (lookup) table with 10 names/records. Each day in a second (history) table I add values for each of the 10 names. Sometimes however, there is no data for one of these names, so no record is added to the historytable. When creating a crosstabquery, sometimes this means that records that have no values, will not be listed as headers. To overcome this, I've used the IN statement in the crosstab, which lists the names.

My question is:

Can this be done dynamically? Users have the ability to add names to the lookup table and now I have to check and change the query manually.

I'll put the sql in here, but don't get confused; my dutch Access makes it a bit harsh to read :

TRANSFORM Sum(tbl_hist_daily_totals.[Aantal EAN]) AS [SomVanAantal EAN]
SELECT tbl_hist_daily_totals.Datum, tbl_month.key, tbl_month.maand, tbl_hist_daily_totals.jaar, IIf([tbl_hist_daily_totals]![missing]=True,"Missing","Available") AS Status, tbl_meter_opname_methode.regio, Sum(tbl_hist_daily_totals.[Aantal EAN]) AS Subtotaal, Sum(IIf([tbl_meter_opname_methode]![regio]='dco',[tbl_hist_daily_totals]![Aantal EAN],0)) AS SubtotaalDCO, Sum(IIf([tbl_meter_opname_methode]![regio]='dcw',[tbl_hist_daily_totals]![Aantal EAN],0)) AS SubtotaalDCW
FROM tbl_meter_opname_methode INNER JOIN ((tbl_hist_daily_totals INNER JOIN tbl_month ON tbl_hist_daily_totals.maand = tbl_month.key) INNER JOIN qry_maxdate_this_month ON tbl_hist_daily_totals.Datum = qry_maxdate_this_month.MaxVanDatum) ON tbl_meter_opname_methode.key = tbl_hist_daily_totals.FK_Beoogd
GROUP BY tbl_hist_daily_totals.Datum, tbl_month.key, tbl_month.maand, tbl_hist_daily_totals.jaar, IIf([tbl_hist_daily_totals]![missing]=True,"Missing","Available"), tbl_meter_opname_methode.regio, tbl_hist_daily_totals.missing
ORDER BY tbl_month.key
PIVOT tbl_meter_opname_methode.Type In ("C2000","Extern","HF","Profiel","SMS", "SMS Automatisch", "TM", "Som", "Onbekend");

As said, the last line contains the names that are in the records of a lookup table.

 

[JonFer]

One way to do what you want is to create a query that contains the unique combinations of all "RowHeading" fields in your crosstab excluding "Type" AND then drop in the table with the full list of Types without any join criteria (cross join).

Then use this query in your crosstab with a Left Join on each field including Type. Then you won't have to modify the "In" list at all.


John

http://www.interautomateconsulting.com

 

[easyit]

Hi John,

Thanx, that works partly. The headers are OK now, but I get a cartesian product this way. For each record in the headerquery the calculations are performed, so I need to find a way to divide teh calculated totals by the number of records in the lookuptable.

Maarten

 

[JonFer]

Please post your SQL. The header query should start with Select Distinct (i.e. UniqueValues=Yes) so you only get one record per combination of "key" values. The header query should be joined to the other tables in the crosstab.

John

http://www.interautomateconsulting.com

 

[easyit]

HEADERS query:

SELECT tbl_meter_opname_methode.Type
FROM tbl_meter_opname_methode;

CROSSTAB query

TRANSFORM Sum(tbl_hist_daily_totals.[Aantal EAN]) AS [SomVanAantal EAN]
SELECT tbl_hist_daily_totals.Datum, tbl_month.key, tbl_month.maand, tbl_hist_daily_totals.jaar, IIf([tbl_hist_daily_totals]![missing]=True,"Missing","Available") AS Status, tbl_meter_opname_methode.regio, Sum(tbl_hist_daily_totals.[Aantal EAN]) AS Subtotaal, Sum(IIf([tbl_meter_opname_methode]![regio]='dco',[tbl_hist_daily_totals]![Aantal EAN],0)) AS SubtotaalDCO, Sum(IIf([tbl_meter_opname_methode]![regio]='dcw',[tbl_hist_daily_totals]![Aantal EAN],0)) AS SubtotaalDCW
FROM qry_headers, tbl_meter_opname_methode INNER JOIN ((tbl_hist_daily_totals INNER JOIN tbl_month ON tbl_hist_daily_totals.maand = tbl_month.key) INNER JOIN qry_maxdate_this_month ON tbl_hist_daily_totals.Datum = qry_maxdate_this_month.MaxVanDatum) ON tbl_meter_opname_methode.key = tbl_hist_daily_totals.FK_Beoogd
GROUP BY tbl_hist_daily_totals.Datum, tbl_month.key, tbl_month.maand, tbl_hist_daily_totals.jaar, IIf([tbl_hist_daily_totals]![missing]=True,"Missing","Available"), tbl_meter_opname_methode.regio, tbl_hist_daily_totals.missing
ORDER BY tbl_month.key
PIVOT tbl_meter_opname_methode.Type;

 

[MichaelRed]

The select of the 'header' column (Names?) should include ONLY that column and should use the distinct predicate. The 'results set' of this query should have exactly the list of unique names and nothing else. Use this as the join and it should be the piviot caluse reference.





MichaelRed

 

[JonFer]

Exactly - you have to link in the header column query with a LEFT JOIN.

John

http://www.interautomateconsulting.com

 

[easyit]

John, Michael,


It works now. I was a bit confused about what you meant, but worked it out.

Thank you very much!

Maarten