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.