Query to transpose rows to columns via Primary Key

Hi All,

This is similar to a question I asked earlier however this is following
a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).

I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all emails
relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:

DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 || DlrEmail
n

There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).

Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).

Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!

-Mal W

have you tried using a Crosstab query? if you've never created one, suggest
you start by using the Crosstab Query Wizard; then you can tweak it if
needed.

hth

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...

Quote:

Hi All,
>
This is similar to a question I asked earlier however this is following
a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).
>
I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all emails
relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:
>
DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 || DlrEmail
n
>
There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).
>
Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).
>
Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!
>
-Mal W
>

"tina" <nospam@address.comwrote in
news:1i5eh.167626$Fi1.155317@bgtnsc05-news.ops.worldnet.att.net:

Quote:

have you tried using a Crosstab query? if you've never created
one, suggest you start by using the Crosstab Query Wizard;
then you can tweak it if needed.
>
hth
>
>
"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...

Quote:

>Hi All,
>>
>This is similar to a question I asked earlier however this is
>following a more "correct" way of doing things. I have
>normalised and summarised an Exchange addressbook (a few
>thousand enteries) into a table storing DealerID and
>DealerEmail (where the DealerEmail is the Primary Key as it's
>unique). The thing to keep in mind is that Dealers can have
>multiple Emails. I have (simplified for examples sake)
>another table containing Dealer information held against
>DealerID (where DealerID is the unique Primary Key).
>>
>I need to be able to have a query that lists each DealerID,
>their relevant Dealer Information from the tblDealerInfo
>table AND all emails relating to that dealer from the
>tblDealerEmails HOWEVER I can only have 1 row per dealer.
>What I want the query to display is (again simplified for
>examples sake) as follows:
>>
>DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2
>|| DlrEmail n
>>
>There are specific reasons why I need the query to output in
>this format (and it has to do with interfacing correctly with
>a legacy system so I can NOT change the input format in any
>way shape or form).
>>
>Does anyone have any idea as to how I would go about this?
>(Keeping in mind some dealers may have 1 DlrEmail, others may
>have anything up to 5).
>>
>Please note I'm NOT after a philosophical debate about the
>"correct" ways to query/store/normalize data as I know this
>is an unorthodox way to present data but such is life! Thanks
>in advance for any help!
>>
>-Mal W
>>

A crosstab query will not work in this instance.

Quote:

>
>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

"m.wanstall" <m.wanstall@gmail.comwrote in
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com:

Quote:

Hi All,
>
I need to be able to have a query that lists each DealerID,
their relevant Dealer Information from the tblDealerInfo table
AND all emails relating to that dealer from the
tblDealerEmails HOWEVER I can only have 1 row per dealer. What
I want the query to display is (again simplified for examples
sake) as follows:
>
DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 ||
DlrEmail n
>
>
Does anyone have any idea as to how I would go about this?
(Keeping in mind some dealers may have 1 DlrEmail, others may
have anything up to 5).
>

Use a function to concatenate the column into a single
expression.

The most versatile one I've found is fConcatChild()
Google that word and copy the function text from the browser to
a new module. www.mvps.org/access/modules/mdl0004.htm is the
first site and it's a good one.

Use it as instructed.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Hi there,

I'm not sure how this would work with a crosstab query, my
understanding would be a Crosstab would try to make each email address
a Column Title and then try to do some sort of count (or some other
arithmetic calculation) on how many email addresses were grouped to
that DealerID.

I don't often use Crosstab queries so there maybe a large gap in my
understanding of their functionality, so if there is a way to do this
via Crosstab please explain! My understanding...I don't think there is.

Thanks!

tina wrote:

Quote:

have you tried using a Crosstab query? if you've never created one, suggest
you start by using the Crosstab Query Wizard; then you can tweak it if
needed.
>
hth
>
>
"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...

Quote:

Hi All,

This is similar to a question I asked earlier however this is following
a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).

I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all emails
relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:

DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 || DlrEmail
n

There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).

Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).

Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!

-Mal W

you and Bob are right, of course; i seem to have a mental block about
crosstab queries. <g>
but fortunately, somebody else has come up with a solution that may meet
your needs. see MVP Duane Hookom's solution at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'.

hth

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165550692.963877.75940@f1g2000cwa.googlegrou ps.com...

Quote:

Hi there,
>
I'm not sure how this would work with a crosstab query, my
understanding would be a Crosstab would try to make each email address
a Column Title and then try to do some sort of count (or some other
arithmetic calculation) on how many email addresses were grouped to
that DealerID.
>
I don't often use Crosstab queries so there maybe a large gap in my
understanding of their functionality, so if there is a way to do this
via Crosstab please explain! My understanding...I don't think there is.
>
Thanks!
>
tina wrote:

Quote:

have you tried using a Crosstab query? if you've never created one,

suggest

Quote:

you start by using the Crosstab Query Wizard; then you can tweak it if
needed.

hth

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...

Quote:

Hi All,
>
This is similar to a question I asked earlier however this is

following

Quote:

a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).
>
I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all

emails

Quote:

relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:
>
DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 ||

DlrEmail

Quote:

n
>
There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).
>
Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).
>
Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!
>
-Mal W
>

>

I would set up a query to apply a custom sequence number to each email,
dealerId, seq, email
where seq = 1,2,3 etc for each dealer.

search groups for ways to do that, using DMAX or a query.
then an update/append query on the lines:

email1 : iif(seq = 1, [newemail],[email1])
email2: iif(seq=2,[newemail],[email2])
.............................
or you could go the VBA route.

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...

Quote:

Hi All,
>
This is similar to a question I asked earlier however this is following
a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).
>
I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all emails
relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:
>
DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 || DlrEmail
n
>
There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).
>
Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).
>
Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!
>
-Mal W
>
>

Thanks to all, those concat functions have hit the nail on the head and
saved me from reinventing the wheel!

Much appreciated!

tina wrote:

Quote:

you and Bob are right, of course; i seem to have a mental block about
crosstab queries. <g>
but fortunately, somebody else has come up with a solution that may meet
your needs. see MVP Duane Hookom's solution at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'.
>
hth
>
>
"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165550692.963877.75940@f1g2000cwa.googlegrou ps.com...

Quote:

Hi there,

I'm not sure how this would work with a crosstab query, my
understanding would be a Crosstab would try to make each email address
a Column Title and then try to do some sort of count (or some other
arithmetic calculation) on how many email addresses were grouped to
that DealerID.

I don't often use Crosstab queries so there maybe a large gap in my
understanding of their functionality, so if there is a way to do this
via Crosstab please explain! My understanding...I don't think there is.

Thanks!

tina wrote:

Quote:

have you tried using a Crosstab query? if you've never created one,

suggest

Quote:

you start by using the Crosstab Query Wizard; then you can tweak it if
needed.
>
hth
>
>
"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...
Hi All,

This is similar to a question I asked earlier however this is

following

Quote:

a more "correct" way of doing things. I have normalised and summarised
an Exchange addressbook (a few thousand enteries) into a table storing
DealerID and DealerEmail (where the DealerEmail is the Primary Key as
it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another table
containing Dealer information held against DealerID (where DealerID is
the unique Primary Key).

I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all

emails

Quote:

relating to that dealer from the tblDealerEmails HOWEVER I can only
have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:

DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 ||

DlrEmail

Quote:

n

There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or form).

Does anyone have any idea as to how I would go about this? (Keeping in
mind some dealers may have 1 DlrEmail, others may have anything up to
5).

Please note I'm NOT after a philosophical debate about the "correct"
ways to query/store/normalize data as I know this is an unorthodox way
to present data but such is life! Thanks in advance for any help!

-Mal W

yep, Duane is da man! :)

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165796428.188532.174190@j72g2000cwa.googlegr oups.com...

Quote:

Thanks to all, those concat functions have hit the nail on the head and
saved me from reinventing the wheel!
>
Much appreciated!
>
tina wrote:

Quote:

you and Bob are right, of course; i seem to have a mental block about
crosstab queries. <g>
but fortunately, somebody else has come up with a solution that may meet
your needs. see MVP Duane Hookom's solution at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'.

Quote:

hth

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165550692.963877.75940@f1g2000cwa.googlegrou ps.com...

Quote:

Hi there,
>
I'm not sure how this would work with a crosstab query, my
understanding would be a Crosstab would try to make each email address
a Column Title and then try to do some sort of count (or some other
arithmetic calculation) on how many email addresses were grouped to
that DealerID.
>
I don't often use Crosstab queries so there maybe a large gap in my
understanding of their functionality, so if there is a way to do this
via Crosstab please explain! My understanding...I don't think there

is.

Quote:

>
Thanks!
>
tina wrote:
have you tried using a Crosstab query? if you've never created one,

suggest

Quote:

you start by using the Crosstab Query Wizard; then you can tweak it

if

Quote:

needed.

hth

"m.wanstall" <m.wanstall@gmail.comwrote in message
news:1165549299.287713.141670@80g2000cwy.googlegro ups.com...
Hi All,
>
This is similar to a question I asked earlier however this is

following

Quote:

a more "correct" way of doing things. I have normalised and

summarised

Quote:

an Exchange addressbook (a few thousand enteries) into a table

storing

Quote:

DealerID and DealerEmail (where the DealerEmail is the Primary Key

as

Quote:

it's unique). The thing to keep in mind is that Dealers can have
multiple Emails. I have (simplified for examples sake) another

table

Quote:

containing Dealer information held against DealerID (where

DealerID is

Quote:

the unique Primary Key).
>
I need to be able to have a query that lists each DealerID, their
relevant Dealer Information from the tblDealerInfo table AND all

emails

Quote:

relating to that dealer from the tblDealerEmails HOWEVER I can

only

Quote:

have 1 row per dealer. What I want the query to display is (again
simplified for examples sake) as follows:
>
DealerID || DlrName || DlrAddress || DlrEmail1 || DlrEmail2 ||

DlrEmail

Quote:

n
>
There are specific reasons why I need the query to output in this
format (and it has to do with interfacing correctly with a legacy
system so I can NOT change the input format in any way shape or

form).

Quote:

>
Does anyone have any idea as to how I would go about this?

(Keeping in

Quote:

mind some dealers may have 1 DlrEmail, others may have anything up

to

Quote:

5).
>
Please note I'm NOT after a philosophical debate about the

"correct"

Quote:

ways to query/store/normalize data as I know this is an unorthodox

way

Quote:

to present data but such is life! Thanks in advance for any help!
>
-Mal W
>
>

>

Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

re: Query to transpose rows to columns via Primary Key

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about