Vertical to Horizontal

[shift2076]

Here is how my table is set up:

System1 Subsystem1
System1 Subsystem2
System1 Subsystem3

I need a query that puts the data into one record, different cells:

System1 Subsystem1 Subsystem2 Subsystem3

Can this be done?
Thanks,
MJ

 

[shift2076]

I managed to get the two put together with a cross tab query, which is groovy. There is a third column that I need now which is giving me a problem. The actual setup needs to be:

Sys1 SubSys1 SubSysStat1 Subsys2 SubSysStat2
Sys2 SubSys1 SubSysStat1 Subsys2 SubSysStat2...

And according to access, I can only specify 1 column heading.

Still workin on it,
MJ

 

[shift2076]

I can't seem to get it. I tried to write an additional crosstab query for the Stat column, but I can't seem to figure out how to map it to the SubSys column so I could union them together.

STILL workin on it.

MJ

 

[shift2076]

Ok... In my cross tab query, I currently have the systems set up as row headings and the subsystems set up as column headings. In each cell is listed the name of the subsystems again... which is completely unneccasary. I would like to put the Status in the cells, but when I try I get some frelling 'aggregate function' message. How can I get Stat in there (the bolded text below)?

TRANSFORM MTab.CatSubSys
SELECT MTab.CatSys
FROM MTab
GROUP BY MTab.CatSys
PIVOT MTab.CatSubSys;

MJ

 

[shift2076]

Okie Dokie...

Here is where I'm at now. I'm writing a second query based on the crosstab and the main table. When I input a subquery to pull up the status column, I get an error that says:

'the ms jet database engine does not recognize '[crosstab1].[catsubsys]' as a valid field name or expression'

here is the sql:

SELECT
(SELECT[CatStat]FROM[MTab]WHERE[CatSys]="Sys1"AND[CatSubSys]=[CrossTab1].[CatSubSys]) AS Sys1Stat, [CatSubSys], [Sys1], [Sys2], [Sys3]
FROM CrossTab1;

As you can see, CrossTab1.CatSubSys is indeed a valid field, and when I pull the subquery outta there, everything works just fine. Has Access gone loopy or have I?

MJ

 

[shift2076]

I forgot to mention, obviously that's not a completed version of the SQL as each system will need it's own subquery when I get this thing to work.

 

[WayneRyan]

Shift,

This is a topic that has come up many times.

http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset

hth,
Wayne

 

[shift2076]

Thank you Wayne... I thought I was talking to myself for awhile. Unfortunately I don't even know what to do with that. Looks like the hunt is still on.

MJ

 

[WayneRyan]

MJ,

No, you're not talking to yourself.

It should be pretty easy. Can you post a sample DB?

Meanwhile, I'll look for a more thorough example.

Wayne

 

[shift2076]

OK...

This is an extremely watered down version of the db (hope it uploaded correctly). As you can see in the queries, the first one works the way i want it, with the subsystems listed underneath the correct system, as opposed to the table version of the data.

I tried going in to the crosstab query and specifying the data parameters (which was TEXT (255)) that did not work either. Instead I received an additional popup window asking for input, and when I ran crosstab2, it gave me the same message as above but told me that 'crosstab2' no longer was a valid filed name or expression. This made even less sense to me than the first error, as crosstab2 doesn't refer to itself anywhere in the sql.

My next step is to open a law suit against microsoft to help pay for the zantac and ibuprofen

MJ

 

[WayneRyan]

Shift,

Oops ... no DB.

Wayne

 

[shift2076]

HMMM.. I don't see the attachment. I'll try again.

 

[shift2076]

I forgot to change the crosstab2 query. the fields listed there are the actual system names, just so you're not confused.

 

[WayneRyan]

Shift,

Uh oh ... now you're talking to yourself.

Your table MTab has both the systems and subsystems in it.

The vertical to horizontal will only work with parent/child tables.
You need to normalize your data.
You need a table for your systems: PK (autonumber), SystemName
You need a subsystem table: PK (autonumber), FK (System), SubsystemName

Then, it's an easy process.

As it is now, you'll have to do a lot of parsing and organizing data to
get what you want.

hth,
Wayne

 

[shift2076]

I don't know if that's possible in my situation. There are 102 Subsystems involved so my SQL would go something like:

SELECT [CatSys]
(SELECT[CatSubSys]FROM[SubSysTab]WHERE[CatSys]=[SysTab].[System]AND[CatSubSys]="SubSys1") AS SubSys1x
(SELECT[CatStat]FROM[SubSysTab]WHERE[CatSys]=[SysTab].[System]AND[CatSubSys]="SubSys1") AS SubSysStat1x
FROM [SysTab]

Where the bolded part would need to be repeated 102 times. Wouldn't that be too big?

MJ

P.S. Thanx for your time!!!

 

[WayneRyan]

Shift,

There is a work-around.

Put the following in a public module:

Public Function fnGetSubs(SystemName As String) As String
Dim rst As DAO.RecordSet
Dim strTemp As String

Set rst = CurrentDB.OpenRecordset ("Select Distinct(CatSubSys) " & _
                                   "From   MTab " & _
                                   "Where  CatSys = '" & SystemName & "' " & _
                                   "Order By CatSubSys")
strTemp = ""
While Not rst.EOF And Not rst.BOF
   strTemp = strTemp & rst!CatSubSys & ", "
   rst.MoveNext
   Wend

If Len(strTemp > 0 Then strTemp = Mid(strTemp, 1, Len(strTemp) - 2)
fnGetSubs = strTemp
End Function

Then in your query, add a new column:

AllSubs: fnGetSubs([CatSys])

The new column in your query will be a comma-delimited string of
all Subsystems for that System.

You'll need a reference to DAO, AND you might run into a max string length
in your query if you have a LOT of subsystems for a particular system.

hth,
Wayne