Build query from Defs

[MacroAlan]

I think I've seen what I'm looking for but cannot find it again!

I have this huge Crosstab query that shows columns for each month from Year minus 1 through Year plus 2; i.e. 01/01/2017 thru 12/1/2020.

I want to gather all the field names into a new query that writes to a new table. I believe that is done with a TableDefs VBA but I don't know how to get started. My data slides forward a year every January.

Anybody got a sample to get me started?

 

[xenou]

I'm not sure there's enough info here. What exactly is the new table you want to create?

 

[Micron]

Also state if you're trying to make this table resemble the output of a crosstab. This would be an un-normalized method of table structure IMO. Data should be recorded in rows, not columns.

 

[MacroAlan]

Sorry I was not totally forthcoming.

I already have our Crosstab query. I need to gather all the fields on that result and make a table that will be sent several places including Excel and text.

I wrote something at a former client that I think used QueryDefs to discover all my field names into a new query.

 

[Norie]

Can't you just create a new table directly from the crosstab?

 

[xenou]

What Norie said is simplest - not entirely sure what your end goal is but you can use the crosstab as a create table query. Possibly give some details - what is the exact table definition you want to create.

 

[MacroAlan]

The dates on my CrossTab vary from Year to year and the date in my tbl_RptDates change every month.

SELECT qry_Inventory_Data_Crosstab.BU, qry_Inventory_Data_Crosstab.Plant, qry_Inventory_Data_Crosstab.Inv_Type, qry_Inventory_Data_Crosstab.Inv_SubType, qry_Inventory_Data_Crosstab.ValType, qry_Inventory_Data_Crosstab.RptMonthNo, qry_Inventory_Data_Crosstab.RptYear, "" AS Blank, qry_Inventory_Data_Crosstab.[1/1/2017], qry_Inventory_Data_Crosstab.[2/1/2017], qry_Inventory_Data_Crosstab.[3/1/2017], qry_Inventory_Data_Crosstab.[4/1/2017], qry_Inventory_Data_Crosstab.[5/1/2017], qry_Inventory_Data_Crosstab.[6/1/2017], qry_Inventory_Data_Crosstab.[7/1/2017], qry_Inventory_Data_Crosstab.[8/1/2017], qry_Inventory_Data_Crosstab.[9/1/2017], qry_Inventory_Data_Crosstab.[10/1/2017], qry_Inventory_Data_Crosstab.[11/1/2017], qry_Inventory_Data_Crosstab.[12/1/2017], qry_Inventory_Data_Crosstab.[1/1/2018], qry_Inventory_Data_Crosstab.[2/1/2018], qry_Inventory_Data_Crosstab.[3/1/2018], qry_Inventory_Data_Crosstab.[4/1/2018], qry_Inventory_Data_Crosstab.[5/1/2018], qry_Inventory_Data_Crosstab.[6/1/2018], qry_Inventory_Data_Crosstab.[7/1/2018], qry_Inventory_Data_Crosstab.[8/1/2018], qry_Inventory_Data_Crosstab.[9/1/2018], qry_Inventory_Data_Crosstab.[10/1/2018], qry_Inventory_Data_Crosstab.[11/1/2018], qry_Inventory_Data_Crosstab.[12/1/2018], qry_Inventory_Data_Crosstab.[1/1/2019], qry_Inventory_Data_Crosstab.[2/1/2019], qry_Inventory_Data_Crosstab.[3/1/2019], qry_Inventory_Data_Crosstab.[4/1/2019], qry_Inventory_Data_Crosstab.[5/1/2019], qry_Inventory_Data_Crosstab.[6/1/2019], qry_Inventory_Data_Crosstab.[7/1/2019], qry_Inventory_Data_Crosstab.[8/1/2019], qry_Inventory_Data_Crosstab.[9/1/2019], qry_Inventory_Data_Crosstab.[10/1/2019], qry_Inventory_Data_Crosstab.[11/1/2019], qry_Inventory_Data_Crosstab.[12/1/2019], qry_Inventory_Data_Crosstab.[1/1/2020], qry_Inventory_Data_Crosstab.[2/1/2020], qry_Inventory_Data_Crosstab.[3/1/2020], qry_Inventory_Data_Crosstab.[4/1/2020], qry_Inventory_Data_Crosstab.[5/1/2020], qry_Inventory_Data_Crosstab.[6/1/2020], qry_Inventory_Data_Crosstab.[7/1/2020], qry_Inventory_Data_Crosstab.[8/1/2020], qry_Inventory_Data_Crosstab.[9/1/2020], qry_Inventory_Data_Crosstab.[10/1/2020], qry_Inventory_Data_Crosstab.[11/1/2020], qry_Inventory_Data_Crosstab.[12/1/2020] INTO tbl_Inventory_Raw_Data_by_Month
FROM qry_Inventory_Data_Crosstab, tbl_RptDates
GROUP BY qry_Inventory_Data_Crosstab.BU, qry_Inventory_Data_Crosstab.Plant, qry_Inventory_Data_Crosstab.Inv_Type, qry_Inventory_Data_Crosstab.Inv_SubType, qry_Inventory_Data_Crosstab.ValType, qry_Inventory_Data_Crosstab.RptMonthNo, qry_Inventory_Data_Crosstab.RptYear, "", qry_Inventory_Data_Crosstab.[1/1/2017], qry_Inventory_Data_Crosstab.[2/1/2017], qry_Inventory_Data_Crosstab.[3/1/2017], qry_Inventory_Data_Crosstab.[4/1/2017], qry_Inventory_Data_Crosstab.[5/1/2017], qry_Inventory_Data_Crosstab.[6/1/2017], qry_Inventory_Data_Crosstab.[7/1/2017], qry_Inventory_Data_Crosstab.[8/1/2017], qry_Inventory_Data_Crosstab.[9/1/2017], qry_Inventory_Data_Crosstab.[10/1/2017], qry_Inventory_Data_Crosstab.[11/1/2017], qry_Inventory_Data_Crosstab.[12/1/2017], qry_Inventory_Data_Crosstab.[1/1/2018], qry_Inventory_Data_Crosstab.[2/1/2018], qry_Inventory_Data_Crosstab.[3/1/2018], qry_Inventory_Data_Crosstab.[4/1/2018], qry_Inventory_Data_Crosstab.[5/1/2018], qry_Inventory_Data_Crosstab.[6/1/2018], qry_Inventory_Data_Crosstab.[7/1/2018], qry_Inventory_Data_Crosstab.[8/1/2018], qry_Inventory_Data_Crosstab.[9/1/2018], qry_Inventory_Data_Crosstab.[10/1/2018], qry_Inventory_Data_Crosstab.[11/1/2018], qry_Inventory_Data_Crosstab.[12/1/2018], qry_Inventory_Data_Crosstab.[1/1/2019], qry_Inventory_Data_Crosstab.[2/1/2019], qry_Inventory_Data_Crosstab.[3/1/2019], qry_Inventory_Data_Crosstab.[4/1/2019], qry_Inventory_Data_Crosstab.[5/1/2019], qry_Inventory_Data_Crosstab.[6/1/2019], qry_Inventory_Data_Crosstab.[7/1/2019], qry_Inventory_Data_Crosstab.[8/1/2019], qry_Inventory_Data_Crosstab.[9/1/2019], qry_Inventory_Data_Crosstab.[10/1/2019], qry_Inventory_Data_Crosstab.[11/1/2019], qry_Inventory_Data_Crosstab.[12/1/2019], qry_Inventory_Data_Crosstab.[1/1/2020], qry_Inventory_Data_Crosstab.[2/1/2020], qry_Inventory_Data_Crosstab.[3/1/2020], qry_Inventory_Data_Crosstab.[4/1/2020], qry_Inventory_Data_Crosstab.[5/1/2020], qry_Inventory_Data_Crosstab.[6/1/2020], qry_Inventory_Data_Crosstab.[7/1/2020], qry_Inventory_Data_Crosstab.[8/1/2020], qry_Inventory_Data_Crosstab.[9/1/2020], qry_Inventory_Data_Crosstab.[10/1/2020], qry_Inventory_Data_Crosstab.[11/1/2020], qry_Inventory_Data_Crosstab.[12/1/2020]
ORDER BY qry_Inventory_Data_Crosstab.BU, qry_Inventory_Data_Crosstab.Plant, qry_Inventory_Data_Crosstab.Inv_Type, qry_Inventory_Data_Crosstab.Inv_SubType, qry_Inventory_Data_Crosstab.ValType, qry_Inventory_Data_Crosstab.RptMonthNo, qry_Inventory_Data_Crosstab.RptYear;

I want to rebuild the dates from the crosstab in this query on the fly so the next person does not have to. Right now I’m stuck with the mess my predecessor left.

 

[xenou]

I would start with this:

SELECT * INTO tbl_Inventory_Raw_Data_by_Month from qry_Inventory_Data_Crosstab

Then see if you can massage that into what you need.
Questions would be what to do about the blank column ("" AS Blank) and whatever that cross join on tbl_RptDates is for

 

[MacroAlan]

The "" AS Blank is because my manager wants an empty column in that position. It's not my lot to ask why.

 

[xenou]

I'm not asking why either so you are safe on that score.