Transpose A Table

[ooch1]

Hi.

What i would like to do is re-group a set of data that is define by it's ID, so that each row has it's own id.

i.e The current Format is:
ID NAME ADDRESS_LINE1

a1 JAMES 17 BRICK LANE
a2 JAMES 17 BRICK LANE
a3 JAMES 17 BRICK LANE

The reult i am after is that the data is grouped by address with the IDs in seperate columns.

NAME ADDRESS_LINE1 ID1 ID2 ID3....

JAMES 17 BRICK LANE a1 a2 a3

One way is thinking is to sort the IDs and ascending order and then ID1 = first row ID, ID2 = second row id, but i don't know how to select a specific value by order.

....I hope this makes sense????

 

[Frink]

Hallo,

Will a Crosstab query work? Don't know much about them, but it might be what you want.

- Frink

 

[ooch1]

Basically No, what that does is create a seperate column header per ID

i.e
Name Addressline2 ID1 ID2 ID3
JAMES 17 BRICK LANE a1 a2 a3
if another row is included it would then look like:

Name Addressline2 ID1 ID2 ID3 ID4 ID5 ID6
TERRY 18 ROCK ROAD b1 b2 b3

So ever each ID will have it's own column which is not right, but maybe there is another way to do it??

 

[dhookom]

Gathering from both threads you have on this same question, I am guessing you could use a crosstab that uses a DCount() in its column heading expression:
[red][tt]
TRANSFORM First(SiteRefNum) AS FirstOfSiteRefNum
SELECT [Site name], MeterPointRef
FROM Gas_Hull_PortAnlys_IMPORT
GROUP BY [Site name], MeterPointRef
PIVOT "ID" & DCount("*","Gas_Hull_PortAnlys_IMPORT","[Site Name]='" & [Site Name] & "' AND SiteRefNum <='" & [SiteRefNum] & "'");[/tt][/red]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

ooch1, that is a report function not a query function. Duane, I believe the request is to have:

Name AddressLine2 ID1 ID2 ID3
record 1
Name AddressLine2 ID1 ID2 ID3
record 2
Name AddressLine2 ID1 ID2 ID3
record 3

BETWEEN each record that is returned in the QUERY.

Leslie

 

[lespaul]

and add fields at the end of the next record too:

Name AddressLine2 ID1 ID2 ID3
record 1
Name AddressLine2 ID1 ID2 ID3 ID4 ID5 ID6
record 2
Name AddressLine2 ID1 ID2 ID3 ID4 ID5 ID6 ID7 ID8 ID9
record 3

why don't you tell us what you are trying to accomplish? Some raw data, what you want to do with the data, etc.


Leslie

 

[ooch1]

The data is going to be used as a mailing file, that is why i would prefer to have the info driven by the addresses and have their meter numbers appended, as apposed to mail a customer based upon their how many meters they have.

I'm not to sure if this is even possible and i have not tried it before.

So assume that the ID example above are meter numbers and the same rules apply.

 

[dhookom]

The solution I provided would change this:
[tt][red]
SiteRefNum Site name MeterPointRef
a1 JAMES 17 BRICK LANE
a2 JAMES 17 BRICK LANE
a3 JAMES 17 BRICK LANE
a4 OOCH1 3 OAK ST
a5 OOCH1 3 OAK ST
a6 LESPAUL 4 TEK-TIPS
a7 LESPAUL 4 TEK-TIPS
a8 LESPAUL 4 TEK-TIPS
a9 LESPAUL 4 TEK-TIPS[/red]
[/tt]

into:
[tt][red]
Site name MeterPointRef ID1 ID2 ID3 ID4
JAMES 17 BRICK LANE a1 a2 a3
LESPAUL 4 TEK-TIPS a6 a7 a8 a9
OOCH1 3 OAK ST a4 a5[/red]
[/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Frink]

Hallo,

What about writing a function to return a string comprised of all the SiteNumRefs for a particular Site Name? Then write a query to give all the unique Site Names and the results of your function.

- Pete

 

[JonFer]

Go with Duane's solution. It doesn't require any coding and can be easily modified for other situations.

If you have a lot of records, the DCount() can be replaced by a self-join for efficiency but that is not necessary.

John

http://www.interautomateconsulting.com

 

[scorpio1769]

I have a very similar problem and would like some help. I'm trying to make my current table go from:

ZONE SOURCE INHOMEDATE
BA12 1 5/1/04
BA12 1 6/16/04
BA12 1 8/9/04
CJ16 1 1/1/04
CJ16 1 3/24/04
A19 1 11/17/04

To:

ZONE DATE1 DATE2 DATE3 DATE4 .....
BA12 5/1/04 6/16/04 8/9/04
CJ16 1/1/04 3/24/04
A19 11/17/04

I tried messing with duane's example crosstab but I could not get it to work. I got a type mismatch error and perhaps that's from the date value. I'm a newbie so please be gentle. The sql attempted was

TRANSFORM First(InHomeDate) AS FirstOfInHomeDate
SELECT [Zone], Source
FROM Qry_AAGold_InHomeCrosstab
GROUP BY [Zone], Source
PIVOT "ID" & DCount("*","Qry_AAGold_InHomeCrosstab","[Zone]='" & [Zone] & "' AND InHomeDate <='" & [InHomeDate] & "'");

Many thanks.

 

[dhookom]

Dates are delimited with "#" rather than quotes:

TRANSFORM First(InHomeDate) AS FirstOfInHomeDate
SELECT [Zone], Source
FROM Qry_AAGold_InHomeCrosstab
GROUP BY [Zone],  Source
PIVOT "ID" & DCount("*","Qry_AAGold_InHomeCrosstab","[Zone]='" & [Zone] & "' AND InHomeDate <=#" & [InHomeDate] & "#");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scorpio1769]

Duane,

That worked like a champ. Thank you much.

JB