Build query from Defs

MacroAlan

New Member
Joined
Aug 30, 2004
Messages
31
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?

:confused:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
I'm not sure there's enough info here. What exactly is the new table you want to create?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Can't you just create a new table directly from the crosstab?
 
Upvote 0
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.
 
Last edited:
Upvote 0
The dates on my CrossTab vary from Year to year and the date in my tbl_RptDates change every month.

Code:
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.
 
Upvote 0
I would start with this:
Code:
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
 
Upvote 0
The "" AS Blank is because my manager wants an empty column in that position. It's not my lot to ask why.
 
Upvote 0
I'm not asking why either so you are safe on that score.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top