transposing normalized access table

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Dear helpers: I'm in a horrible bind! I have something
simliar to this:

Id JobId Start End
1 1 1981 1989
1 1 1994 1995
1 1 1999 2000

Nice and normal, and everyone's happy... except for the
person who needs it:

ID Job1 Job1Start1 Job1End1 Job1Start2 Job1End2 Job1Start3
Job1End3
1 -1 1981 1989 1994 1995 1999
2000

I can get it to work using GroupBy ID, MinofStart/End for
the first, Max of Start/End for the last, but the fact
that there is a middle entry is throwing me for a loop!

The "real life" problem consists of up to 10 entries for
dozens of these types of variables. I'm in no position to
question why this must be done, but I've been charged to
do it. They're counting on me, and I'm counting on a
couple of hints from you, oh wise Newsgroupers!

thanks ---
 
Arrrggh. Print it Landscape and then hand it to them portrait?? <just
kidding>

You have two choices: Create a function that reads the table separately and
returns the values as a concatenated string. Call the function from a
SELECT DISTINCT query.

Or, you can try SQL that looks like this:

SELECT T1.ID, T1.JobID, T1.Start, T1.End, T2.Start, T2.End, ... ,T10.Start,
T10.End
FROM MyTable As T1
LEFT JOIN MyTable As T2
ON T1.ID = T2.ID AND T1.JobID = T2.JobID
LEFT JOIN MyTable As T3
ON T1.ID = T3.ID AND T1.JobID = T3.JobID
....
WHERE T2.Start =
(SELECT Min(Start) FROM MyTable AS M2
WHERE M2.Start >= T1.End) AND
T3.Start =
(SELECT Min(Start) FROM MyTable As M3
WHERE M3.Start >= T2.End) AND
....

By the time you get to all 10 copies of the table, you might get "query too
complex" -- or it could take ages to run.

Good luck!

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Dear Tim:

For an alternative solution, I'm thinking you could rank the Start/End
rows by Start time (or End time - they should sort the same order,
right?) Then you could use a set of subqueries to report them
together. I'm thinking of a cross tab like approach, which this does
look like, except there are multiple values in the columns, both
"Start" and "End" and they are not aggregates.

You see, a cross tab query is (as I see it) a kind of a shorthand way
of producing a query which you can also do without using a cross tab.
And, once you have learned how to do this, you can remove some of the
restrictive "assumptions" that were introduced with the use of the
cross tab wizard. Once again, we discover that wizards can blind us
to some of the bigger issues behind what we program.

Let me know if you want to persue this with me further, OK?

Finally, for some of my friends, you may note my signature has
reverted to its former length.

Dear helpers: I'm in a horrible bind! I have something
simliar to this:

Id JobId Start End
1 1 1981 1989
1 1 1994 1995
1 1 1999 2000

Nice and normal, and everyone's happy... except for the
person who needs it:

ID Job1 Job1Start1 Job1End1 Job1Start2 Job1End2 Job1Start3
Job1End3
1 -1 1981 1989 1994 1995 1999
2000

I can get it to work using GroupBy ID, MinofStart/End for
the first, Max of Start/End for the last, but the fact
that there is a middle entry is throwing me for a loop!

The "real life" problem consists of up to 10 entries for
dozens of these types of variables. I'm in no position to
question why this must be done, but I've been charged to
do it. They're counting on me, and I'm counting on a
couple of hints from you, oh wise Newsgroupers!

thanks ---
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I played with this a little bit, and the following technique, although
not pretty, seems to work.

1. Compute the sequence number for each Job, Start (save this as
qrySequence)

SELECT t.ID, t.JobID, t.Start, t.End,
(Select Count(*) From yourTable where ID = T.ID and
JobID = T.JobID And Start <= T.Start) AS Seq
FROM yourTable AS t

2. Create a query to build your column headers (save this as
qryColumns).

SELECT S.ID
, S.JobID
, 'Job' & [JobID] & 'Start' & [Seq] AS ColHeader
, S.Start AS ColValue
, Seq*2 + 0 as SortOrder
FROM qrySequence AS S
UNION
SELECT S.ID
, S.JobID
, 'Job' & [JobID] & 'End' & [Seq] AS ColHeader
, S.End AS ColValue
, Seq*2 + 1 as SortOrder
FROM qrySequence AS S;

3. Create a make table query to create a temporary table based on
this previous query. Ideally, you should be able to just use the
previous query as the source for a CrossTab query, but in my
experience JET doesn't like create CrossTabs from queries, at least
not ones where the query is based on another query.

SELECT qryColumns.*
INTO NewColumns
FROM qryColumns;

4. At this point, I created a procedure that would run the make table
from step 3 and then dynamically create the SQL string for a pivot
table.

Public Sub CrosstabJobs()

DoCmd.SetWarnings False
On Error Resume Next
DoCmd.DeleteObject acTable, "NewColumns"

CurrentDb.Execute "qryMakeNewColumnsTable"
DoCmd.SetWarnings True
On Error GoTo 0
Dim strSQL As String
strSQL = "TRANSFORM First(NewColumns.ColValue) AS CellValue " _
& "SELECT NewColumns.ID, NewColumns.JobID " _
& "FROM NewColumns " _
& "GROUP BY NewColumns.ID, NewColumns.JobID " _
& "PIVOT ColHeader IN ("

Dim rs As Recordset
Set rs = CurrentDb.openrecordset("SELECT DISTINCT ColHeader, SortOrder
FROM qryColumns ORDER BY SortOrder")
While Not rs.EOF
strSQL = strSQL & Chr$(34) & rs("ColHeader") & Chr$(34) & ","
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
strSQL = Left(strSQL, Len(strSQL) - 1) & ")"

MsgBox strSQL
Debug.Print strSQL
CurrentDb.QueryDefs("qryMyCrosstab").SQL = strSQL
DoCmd.OpenQuery "qryMyCrossTab"

End Sub


--
HTH

Dale Fye


Dear helpers: I'm in a horrible bind! I have something
simliar to this:

Id JobId Start End
1 1 1981 1989
1 1 1994 1995
1 1 1999 2000

Nice and normal, and everyone's happy... except for the
person who needs it:

ID Job1 Job1Start1 Job1End1 Job1Start2 Job1End2 Job1Start3
Job1End3
1 -1 1981 1989 1994 1995 1999
2000

I can get it to work using GroupBy ID, MinofStart/End for
the first, Max of Start/End for the last, but the fact
that there is a middle entry is throwing me for a loop!

The "real life" problem consists of up to 10 entries for
dozens of these types of variables. I'm in no position to
question why this must be done, but I've been charged to
do it. They're counting on me, and I'm counting on a
couple of hints from you, oh wise Newsgroupers!

thanks ---
 
Back
Top