Solved Transform Columns in multiple rows

[LeoM]

Hello everyone, i have the following problematic and i would like to know if someone can propose a solution either with a query or even with vba.
I have the following table:

and what i need as output is the following?

In few words i should have a new table (query) which return for each Key (SubSystem) all the possible Start Date and End Date.
Hope it is clear, appreciate your usual and kindly support.

Cheers.

 

[CJ_London]

you can use two crosstab queries, one for starting date, the other for ending date. Then join the two together. You would need to calculate the header, probably based on dcount for simplicity, but not performance.

Code might be be something this for the start date

TRANSFORM First(myTable.startdate) AS FirstOfstartdate
SELECT myTable.subsystem
FROM myTable
GROUP BY myTable.subsystem
PIVOT "StartDate " & DCount("*","myTable","StartDate<=#" & Format([startDate],"yyyy-mm-dd") & "# AND subsystem='" & [subsystem] & "'");

similar for the end date

When you combine the two in a third query, reorder the columns so you get startdate, enddate, startdate, enddate, etc

This all assumes your data is truly representative - i.e. that within any subsystem , there is no overlap of dates

 

[LeoM]

Thanks a lot, first of all.
Ok I build the Q1 and Q2 and is clear and work fine. Now, the combined query should be dynamic, not manually because records may change (i.e. more start/end date) so how to write the combined query which will work dynamically in case of record change? Not sure i'm clear.
Cheers

 

[CJ_London]

perhaps build the third query in vba

not at my computer right now but something like

dim rs as dao.recordset
dim i as integer
dim sqlStr as string

set rs= currentdb.openrecordset("Q1")
sqlStr="SELECT Q1.subsystem"

for i=1 to rs.fields.count
    sqlstr=sqlstr & ", StartDate" & i & ", EndDate" & i
next i

sqlstr=sqlstr & "FROM Q1 INNER JOIN Q2 ON Q1.subsystem = Q2.subsystem"

debug.print sqlstr
'copy/paste the print to a new query to check it works

depends what you are doing with this after for any other code

 

[Mark_]

Two questions;
1) HOW do you plan to use this data?
2) HOW will you limit the number of columns?

Please look to boundary issues, such as "Most of our subsystems will only have two or three pairs, but THAT one has about 400...

I've been bit by more than one "Good Idea", and I'm hoping to avoid you suffering the same.

 

[June7]

Even a CROSSTAB query is limited to 255 fields in output. If you could have more than that, will need a more creative and elaborate procedure in Access or possibly export data to Excel.

 

[DHookom]

You can create a crosstab with pairs of values (Start and End) by creating a headings table [tblCrosstabHeadings] and adding to values

I use a ranking/sequence query [qseqSubststmes] to generate the 1,2,3,...

SELECT tblLeoM.Subsystem, tblLeoM.STARTING_DATE, tblLeoM.ENDING_DATE,
Count(tblLeoM_1.Subsystem) AS Seq
FROM tblLeoM INNER JOIN tblLeoM AS tblLeoM_1 ON tblLeoM.Subsystem = tblLeoM_1.Subsystem
WHERE (((tblLeoM_1.STARTING_DATE)<=[tblLeoM].[STARTING_DATE]))
GROUP BY tblLeoM.Subsystem, tblLeoM.STARTING_DATE, tblLeoM.ENDING_DATE
ORDER BY tblLeoM.Subsystem;

Then the crosstab with the sequence query and the crosstab heading query

TRANSFORM First(IIf([CrosstabHeading]="Start Date",[STARTING_DATE],[ENDING_DATE])) AS Expr2
SELECT qseqSubsystems.Subsystem
FROM qseqSubsystems, tblCrosstabHeading
GROUP BY qseqSubsystems.Subsystem
PIVOT [CrosstabHeading] & [Seq] In ("Start Date1","End Date1","Start Date2","End Date2","Start Date3","End Date3");

You can use the Column Headings property of the crosstab to ensure a full list of columns headings based on the maximum number you need.

It is possible to display a query as a subform like:

 

[LeoM]

Thank you all, really appreciate your kindly support. I will give answer to all of you below:

To CJ_London : The query, output of the code you provide, it works, and it is exactly what I need in term of query. Then, since my final scope is to have a "physical" query, I will modify the code adding the query creation steps. Thanks a lot.

To Mark: Answering to your queries:
1. Someone came to me with an excel file asking if this way to represent the start/end date for a subsystem was possible. I answer probably yes and i believe what they need is just a query to be represented probably in PowerBi.
2. Honestly didn't think to limit the records but i don't think will have many possible columns. In this example are max 3, i will verify but i don't think it will be many.
Thanks for suggestion.

To June7: fully agree, I will verify it but, as mentioned above to Mark, we should not have this case. Thanks.

To DHookom : Thanks for your detailed example which i test and work but it is not clear to me the last query in the PIVOT sentence since the header are manually filled (PIVOT [CrosstabHeading] & [Seq] In ("Start Date1","End Date1","Start Date2","End Date2","Start Date3","End Date3"); My main problem is to have it automatically because if in the future i will have more (or less) dates for each system. the crosstab query will not work i believe. Or i don't understand maybe. Anyway, thanks a lot for the example.

Cheers

 

[DHookom]

You can get rid of the Column Headings property values. It was there as a hard limit and also sets the column order. I think the subform will display any number of columns up to Access limits.

 

[LeoM]

you can use two crosstab queries, one for starting date, the other for ending date. Then join the two together. You would need to calculate the header, probably based on dcount for simplicity, but not performance.

Code might be be something this for the start date

TRANSFORM First(myTable.startdate) AS FirstOfstartdate
SELECT myTable.subsystem
FROM myTable
GROUP BY myTable.subsystem
PIVOT "StartDate " & DCount("*","myTable","StartDate<=#" & Format([startDate],"yyyy-mm-dd") & "# AND subsystem='" & [subsystem] & "'");

similar for the end date

When you combine the two in a third query, reorder the columns so you get startdate, enddate, startdate, enddate, etc

This all assumes your data is truly representative - i.e. that within any subsystem , there is no overlap of dates

Hello, they added now another column, the Owner. So each StartDate and EndDate is refer to a owner (see below):

And the result should be the follow:

I tried to update the PIVOT sentence in the Transform queries, but it does not work (maybe I'm not able enough) could you kindly suggest me how i should modify the Transform query and also the last code (to combine the 2 Transform queries) accordingly? Thank you, Cheers

 

[DHookom]

You could take my solution and add "Owner" as the third record.

Then make sure the OWNER field is in qselSubSystems.

Change the SQL of the crosstab to:

TRANSFORM Min(Switch([CrosstabHeading]="Start Date",[STARTING_DATE],[CrosstabHeading]="End Date",[ENDING_DATE],1,[OWNER])) AS Expr2
SELECT qseqSubsystems.Subsystem
FROM qseqSubsystems, tblCrosstabHeading
GROUP BY qseqSubsystems.Subsystem
PIVOT [CrosstabHeading] & [Seq];

The new columns will automatically display in the subform I described.

If you want a particular order to the columns, I would use a little VBA to modify the SQL of the crosstab to set the crosstab headings based on the max seq field from the qseqSubsystems query.

 

[Mark_]

I answer probably yes and i believe what they need is just a query to be represented probably in PowerBi.

AND

Hello, they added now another column, the Owner.

I'd suggest talking to them to see exactly what they are using it for. They may think "Hey, this is easy THIS WAY" and the person doing the display may be going "This would be so much easier if they just gave me one line per start/end pair". Would much rather you find this out now before you delve too deeply in. Were I you, I'd also get them to write out the specification and send it to you by email so you can track it!

 

[DHookom]

If you use my recommended solution, you can change the crosstab column expression to: Expr1: [Seq] & "-" & [CrosstabHeading] which will group the dates and owner columns together for each "event". To make it even better, change the value of Start Date in the crosstab heading table to Begin Date. If you have more than 9 column groups, you will need to format the Seq column with leading 0s.

 

[LeoM]

You could take my solution and add "Owner" as the third record.
View attachment 119205

Then make sure the OWNER field is in qselSubSystems.
View attachment 119204

Change the SQL of the crosstab to:

TRANSFORM Min(Switch([CrosstabHeading]="Start Date",[STARTING_DATE],[CrosstabHeading]="End Date",[ENDING_DATE],1,[OWNER])) AS Expr2
SELECT qseqSubsystems.Subsystem
FROM qseqSubsystems, tblCrosstabHeading
GROUP BY qseqSubsystems.Subsystem
PIVOT [CrosstabHeading] & [Seq];

The new columns will automatically display in the subform I described.
View attachment 119206

If you want a particular order to the columns, I would use a little VBA to modify the SQL of the crosstab to set the crosstab headings based on the max seq field from the qseqSubsystems query.

Yes, this is perfect, thank you.
Yes, I need the order of the header as below so I would appreciate If you can show me the little VBA you are talking about to obtain it:

Cheers.

 

[LeoM]

Yes, this is perfect, thank you.
Yes, I need the order of the header as below so I would appreciate If you can show me the little VBA you are talking about to obtain it:

View attachment 119225

Cheers.

No need, thanks anyway, i tried to build VBA myself using the SQL you suggested as result (i named "FinalResult"). This is the VBA which will create the final query (FinalResult_rc) I need with values in correct header order:

Cheers.

 

[June7]

Why not use DHookum's suggestion as expanded in post 13? Although, I would use underscore instead of hyphen. Also, prefix Owner with something that would alphabetically precede Begin (1_AOwner) or allow it to follow the date columns.

If you need more guidance on CROSSTAB, review http://allenbrowne.com/ser-67.html#MultipleValues

If this data is in Excel (you say that's how you received it and this effort is to help someone), Excel PowerQuery can pivot data.

 

[DHookom]

I agree with June7. I started writing the code on the form to modify the SQL and then realized it wasn't necessary if the expressions and a data value were changed. LeoM, your VBA hard codes in the values of Owner, StartDate, and EndDate while also locking in the number or heading groups to 3. My code would have read the number of possible groups from the query as I described.

I like to keep my objects self maintaining with little or no hard-coded values.

 

[LeoM]

As i specified in my post, I exactly followed what DHookom suggested (post 11). In the same post (before post 13) it was mentioned that "If you want a particular order to the columns, I would use a little VBA to modify the SQL of the crosstab to set the crosstab headings based on the max seq field from the qseqSubsystems query." and this is what I tried to develop because it is exactly what i need, my columns sequence in the final query should be OWNER-STARTDATE-ENDDATE. Then I tried to use the suggestions as at the Post 13 but, sorry for my ignorance and incompetence, it doesn't work. As indicated, I change the column header and the crosstab but not works to me, i don't have the column sequence i need. I agree should be used the best and easy way avoiding useless VBA Code but not clear to me how to change the crosstab, maybe you can give me an example for my case.
Cheers

 

[June7]

Really should provide sample data as text tables or attached file, not image. Can't copy/paste data from image.

Records in tblCrosstabHeading:

CrosstabHeading
BeginDate
EndDate
AOwner

SELECT tblLeoM.Subsystem, tblLeoM.Owner, tblLeoM.STARTING_DATE, tblLeoM.ENDING_DATE, Count(tblLeoM_1.Subsystem) AS Seq
FROM tblLeoM INNER JOIN tblLeoM AS tblLeoM_1 ON tblLeoM.Subsystem = tblLeoM_1.Subsystem
WHERE (((tblLeoM_1.STARTING_DATE)<=[tblLeoM].[STARTING_DATE]))
GROUP BY tblLeoM.Subsystem, tblLeoM.Owner, tblLeoM.STARTING_DATE, tblLeoM.ENDING_DATE
ORDER BY tblLeoM.Subsystem;

TRANSFORM Min(Switch([CrosstabHeading]="BeginDate",[Starting_Date],[CrosstabHeading]="EndDate",[Ending_Date],1,[Owner])) AS Expr2
SELECT qseqSubsystems.Subsystem
FROM qseqSubsystems, tblCrosstabHeading
GROUP BY qseqSubsystems.Subsystem
PIVOT [Seq] & "_" & [CrosstabHeading];

If you expect the number of sets to exceed 9 but less than 100 (must be less than 85):
PIVOT Format([Seq],"00") & "_" & [CrosstabHeading];

Subsystem	01_AOwner	01_BeginDate	01_EndDate	02_AOwner	02_BeginDate	02_EndDate	03_AOwner	03_BeginDate	03_EndDate
J1	Own1	10/16/2023	8/4/2024
J2	Own1	9/16/2024	10/30/2025	Own2	7/1/2026	4/7/2028
J3	Own2	1/1/2026	2/28/2028	Own1	3/1/2028	12/31/2028
J4	Own2	9/15/2022	12/31/2025	Own3	1/1/2026	2/28/2028	Own1	3/1/2028	12/31/2028

 

[LeoM]

Thank you, now is clear.
Just a minor modification in the TRANSFORM query, should be like:

TRANSFORM Min(Switch([CrosstabHeading]="BeginDate",[STARTING_DATE],[CrosstabHeading]="EndDate",[ENDING_DATE],1,[Owner])) AS Expr2
SELECT qselSubSystems.Subsystem
FROM qselSubSystems, tblCrosstabHeading
GROUP BY qselSubSystems.Subsystem
PIVOT [Seq] & "_" & [CrosstabHeading];

Cheers.