Adding columns to a query built off a cross tab query

[accessaspire219]

Hi Guys,
I have a cross tab query in which the column headings are months. As data gets added for the next month, a column will be added to the cross tab query for the additional month.
Now I have another query which is built off this cross tab query (basically a join between two cross tab queries)
Every time a column is added to the cross tab query for a new month, the column will not get added to the query that is built off the cross tab query since I manually selected the columns for this query.
I was wondering if anyone has a VBCode or some other way to automatically add the extra columns that come up in the cross tab query to the query built off the cross tab query.
Thanks.

 

[namliam]

No need for code...

Remove your months from the 2nd query, now go into SQL and add:
, YourCrossTabQuery.*

Any new columns will automagicaly be added

 

[accessaspire219]

easy peazy!!But what about the row headings and values in the cross tab query? Is there a comma before the cross tab query name?
Thanks

 

[namliam]

Your old query looks something like:

Select field1, field2, field3, field4, month1, month2

You simply make it
Select field1, field2, field3, field4, YourCrossTabQuery.*

Your done

 

[accessaspire219]

Thanks - I am confused.This is how my crosstab query looks. PERIOD1 is the column heading - would it be
PIVOT Calc_buyer_MIOH_by_period.*; ?
If I put the asterisk there is no mention of the column that contains the column headings - how will access know which column to PIVOT?

TRANSFORM Sum(Calc_buyer_MIOH_by_period.METRIC) AS SumOfMETRIC
SELECT Calc_buyer_MIOH_by_period.ABUYR, Calc_buyer_MIOH_by_period.TYPE
FROM Calc_buyer_MIOH_by_period
GROUP BY Calc_buyer_MIOH_by_period.ABUYR, Calc_buyer_MIOH_by_period.TYPE
PIVOT Calc_buyer_MIOH_by_period.PERIOD1;

 

[namliam]

No not your pivot query... The query you have ON TOP of your povit query...

 

[accessaspire219]

This is the query I have on top of the cross tab query. Incidently I am defining the period by concatenating two columns from a query that is on top of this query. It wont be possible to concatenate in the query on top of this query because other queries use the non-concatenated data.
Any way around this?

SELECT Buyer_MIOH_Period_Data_Table.ABUYR, [PERIOD] & "/" & [YEAR] AS PERIOD1, IIf(Nz([SumOfINVDOLR])=0,0,IIf(Nz([SumOfCONSDOLR])=0,0,Round((Buyer_MIOH_Period_Data_Table!SumOfINVDOLR/Buyer_MIOH_Period_Data_Table!SumOfCONSDOLR),2))) AS METRIC, Buyer_MIOH_Period_Data_Table.TYPE
FROM Buyer_MIOH_Period_Data_Table;

 

[namliam]

I which case you will have to use code to compile your non-Crosstab query... It is pretty easy to read the Crosstab query and find out what columns it has.

Then build fresh query to be saved on top of that and execute that query.

 

[accessaspire219]

Namliam, could you dummy it a little more for me please? I did not understand what you are saying..

 

[accessaspire219]

I think I got it. I was taking the "query on top" as the query BEFORE the crosstab and not the one AFTER the crosstab I think it works. except there are some columns such as buyer name etc which I want only once so technically I only want the months/quarter/year from each of these 3 queries and want the buyer name only once (I have 3 CT queries merging into 1 query) Need to figure out how to do that...
Thanks anyways!!

 

[accessaspire219]

In other words, if I do a CrossTabQuery.* can I specify which columns to exclude? I want to select all column except the ones I exclude.

 

[namliam]

CrossTabQuery.* returns all columns, no way to exclude any columns...

If you want specific columns you are going to build the SQL in code.
You can open the queries in code to find all the columns it returns...
A quick sample

Sub test()
    Dim rs As DAO.Recordset
    Dim x As Object
    Set rs = CurrentDb.OpenRecordset("select * from query1")
    For Each x In rs.Fields
        Debug.Print x.Name
    Next x
End Sub

Or the way I usually do it, as Crosstabs can be rather hard on running...
Make a seperate Group by query to "simulate" the columns beeing build in the Crosstab and retrieve the data from there. Building the SQL.

Sorry dont have a specific sample on hand though