Tranposing data

[janmd]

How can I change a table that looks like


Client ID 2005 2006 2007 2008
1 y Y
2 Y
3 Y Y


to look something like

Client ID Eventdate
1 2006
1 2008
2 2007
3 2005
3 2008

I'm sure its not that difficult, but its been a long day.

 

[John W. Vinson]

How can I change a table that looks like


Client ID 2005 2006 2007 2008
1 y Y
2 Y
3 Y Y


to look something like

Client ID Eventdate
1 2006
1 2008
2 2007
3 2005
3 2008

I'm sure its not that difficult, but its been a long day.

A "Normalizing Union Query" is the ticket here: in the SQL window edit a query

SELECT [Client ID], (2005) AS Eventdate FROM yourtable
WHERE [2005] = "Y"
UNION ALL
SELECT [Client ID], (2006) AS Eventdate FROM yourtable
WHERE [2006] = "Y"
UNION ALL
SELECT [Client ID], (2007) AS Eventdate FROM yourtable
WHERE [2007] = "Y"
UNION ALL
SELECT [Client ID], (2008) AS Eventdate FROM yourtable
WHERE [2008] = "Y"


Base an Append or MakeTable query on this query.

 

[janmd]

How can I change a table that looks like

Client ID 2005 2006 2007 2008
   1                  y            Y
   2                          Y
   3        Y                      Y

to look something like

Client ID  Eventdate
 1            2006
 1            2008
   2            2007
 3            2005
 3            2008

I'm sure its not that difficult, but its been a long day.

A "Normalizing Union Query" is the ticket here: in the SQL window edit a query

SELECT [Client ID], (2005) AS Eventdate FROM yourtable
WHERE [2005] = "Y"
UNION ALL
SELECT [Client ID], (2006) AS Eventdate FROM yourtable
WHERE [2006] = "Y"
UNION ALL
SELECT [Client ID], (2007) AS Eventdate FROM yourtable
WHERE [2007] = "Y"
UNION ALL
SELECT [Client ID], (2008) AS Eventdate FROM yourtable
WHERE [2008] = "Y"

Base an Append or MakeTable query on this query.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Many thanks - I'll give it a try

 

[a a r o n _ k e m p f]

Wow that's great

of course, SQL Server supports the PIVOT and UNPIVOT command

maybe if your database (JET) wasn't depecrated, you could enjoy the
native capability to do this

How can I change a table that looks like

Client ID 2005 2006 2007 2008
   1                  y            Y
   2                          Y
   3        Y                      Y

to look something like

Client ID  Eventdate
 1            2006
 1            2008
   2            2007
 3            2005
 3            2008

I'm sure its not that difficult, but its been a long day.

A "Normalizing Union Query" is the ticket here: in the SQL window edit a query

SELECT [Client ID], (2005) AS Eventdate FROM yourtable
WHERE [2005] = "Y"
UNION ALL
SELECT [Client ID], (2006) AS Eventdate FROM yourtable
WHERE [2006] = "Y"
UNION ALL
SELECT [Client ID], (2007) AS Eventdate FROM yourtable
WHERE [2007] = "Y"
UNION ALL
SELECT [Client ID], (2008) AS Eventdate FROM yourtable
WHERE [2008] = "Y"

Base an Append or MakeTable query on this query.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

 

[Tony Toews [MVP]]

maybe if your database (JET) wasn't depecrated, you could enjoy the
native capability to do this

Jet has not been deprecated and is under active development by the
Access product group under the name of ACE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

so.. what you're saying.. is that Jet isn't called Jet anymore?

ROFL

whatever kid. Jet has been obsolete for a decade, and it will always
be obsolete.

SQL Server won the war, anyone using Jet or ACCDB or any crap --
should be fired and it's easy to find someone to help you to make a
better, more reliable-- higher performance database-- for the same
price.

Technically, SQL Server develpment is _EASIER_ than Jet!