Need crosstab/transform query which also slightly "re-arranges" table output

[skydivetom]

Experts:

Good morning -- I need some assistance with a, e.g., Crosstab/Transform query which transposed AND slightly re-arranges a table automatically.

Attached DB contains 2 tables:
1. Table [01_tbl_SampleRecords] -- this is my source table that needs to be transposed/re-arranged.
2. Table [02_tbl_SampleRecords_Transposed] -- this is merely a depiction as to how the query's output should look like upon execution.

Process:
- To better illustrated the transpose/re-arrange process, I have included a spreadsheet which summarizes the "3-step" process.

Note:
- As indicated in the XLSX, please keep in mind that field [ID] is stored as data type = "text" (vs. number).

Does anyone have recommendations for creating a, e.g., crosstab query which automatically outputs what is depicted in step 3 (aka see JPG & 2nd table in the DB)?



Thank you,
Tom

P.S. In my actual table, I do have approximately fifty (50) fields that will have to be turned from fields into records. Same process applies though (instead of turning {CITY, STATE, ZIP} into rows, it will be those 3 + another ~47 fields.
P.S.S. If a crosstab won't be able to accomplish it, I'd be ok using VBA (if necessary).

[orange]

Tom,

Not exactly sure if this is what you're looking for.

Created a table T2j







And a module.

I have returned your zip file with the new table and module.

[skydivetom]

Jack:

PERFECT!!!! That's exactly what I was looking for. Kinda "ironic" that you chose VBA. I was about to close down the "query question" and post a new thread under the "Programming" forum. I didn't have to... and hopefully it would not have been in violation. Anyhow, I love your VBA function.

Now, I changed the sample data table w/ a sample table containing *actual* data and the 50+ fields. Given that some fields have NULL values though, I'm running into an error.

How can the VBA be modified so that everything works the same (just like in the City/State/Zip example) but I can process the 50 fields where some contain NULL values?

Thank you... this is HUGE already!!!

Cheers,
Tom

[orange]

If a field is null, what value do you want to output?

These are empty text fields (ZLS); I changed to variant with CVar and then used NZ to put in a replacement value.

If you change line 70, put whatever you want where I have "-----N/A" in BLUE

sLegVal = Nz(CVar(r1.Fields(i).Value), "-----N/A")

Sample output

[skydivetom]

Jack:

Sorry for the delayed response... yes, using the "-----N/A" works great!!

Using it on the full data set (i.e., hundreds of records * 57 fields) w/o any issues. BRILLIANT!!!

Many thanks... I love this solution!

Cheers,
Tom

[orange]

Happy to Help.

[June7]

A UNION query can also produce this output. Example with your original dummy data:

SELECT ID, 1 AS Seq, "ID" AS FieldName, ID AS Legacy_Value FROM 01_tbl_SampleRecords
UNION SELECT ID, 2, "City", City FROM 01_tbl_SampleRecords
UNION SELECT ID, 3, "State", State FROM 01_tbl_SampleRecords
UNION SELECT ID, 4, "ZIP", ZIP FROM 01_tbl_SampleRecords;

If you're happy with VBA solution, probably want to stick with it. Building a 50-line UNION can be tedious. Also, limit is 50 SELECT lines.

Why do you need the ID rows?

If you need to repeat this process and don't want to append to existing records but always start from empty table, add a DELETE action to beginning of code.

CurrentDb.Execute "DELETE FROM TJ2"

[skydivetom]

Hi June7:

thank you for the additional recommendation. At some point, I actually did look into the UNION SELECT. Once I realized that I only can concatenate up to 50 lines, it was no longer an option (given I need to transform 50+ fields).

As you noticed, the VBA routine is very dynamic and does NOT have that limitation.

Jack's VBA works 100% as envisioned... couldn't ask for a better/more flexible solution. Thanks for chiming in though. 'Much appreciated.

[ArviLaanemets]

At some point, I actually did look into the UNION SELECT. Once I realized that I only can concatenate up to 50 lines, it was no longer an option (given I need to transform 50+ fields).

Another reason to use SQL Server as DB part (back-end)! A couple of weeks ago I had to run a query with (26 000 +) UNION SELECT's Had to use Excel to compose query text rows, and then pasted the result into SQL Server query editor.