Dynamic Query Based on anthoer crosstab

[vipersmind]

Hi,
I wish to use the results of a crosstab query in another query which will make table. The trouble is the field names are not always the same in the crosstab.
The results are coming from a normalised table so if there are no records for a particular field then that field will no appear in the crosstab.

I need to make my 'make table query' (qryTabletImport_CollarMakeStaging) account for the missing fields names when it tries to make the new table. Since I have made the make table query with all availble field names I get this error when some are not present.

<the jet engine doesnot recognise 'qryTabletImport_CollarConvert.Hours' as a field name or expression>

(because the field Hours doesn't have any data in the normalised table)

make table query - qryTabletImport_CollarMakeStaging
Crosstab query - qryTabletImport_CollarConvert
normalised table - tblTablet_HOLEDETAILS

Hope this makes some sense to someone.
I am ok with vb so am willing to go that way but getting my head around which way start is another issue

Cheers

 

[vipersmind]

Anyone please help me?

 

[john471]

Sounds like you need static column headings....

In the crosstab query, in design view, right click in the top half and choose Properties....

Under Column Headings, fill in the required headings. See on-line help on "ColumnHeadings Property" for more info.

Hope this helps.

Regards

John.

 

[vipersmind]

Thank you that is exactly what I was after.
NOw that you have pointed it out I have seen it on another post but not understood it related to my situation.

DOH!! *smacks forehead*

Cheers

 

[vipersmind]

This link was very helpful as well. You can code it all and make it much more dynamic

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart05/html/sa05d1.asp