Query to transpose rows to columns via Primary Key

[m.wanstall]

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]

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

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

[Bob Quintal]

"tina" <no****@address.comwrote in
news:1i*********************@bgtnsc05-news.ops.worldnet.att.net:

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

>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.

>

--
Bob Quintal

PA is y I've altered my email address.

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

[m.wanstall]

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:

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

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

[Bob Quintal]

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

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

[tina]

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.********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegrou ps.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:

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

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
>

[David F Cox]

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

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

[m.wanstall]

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

Much appreciated!

tina wrote:

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.********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegrou ps.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:

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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
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]

yep, Duane is da man! :)
"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

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

Much appreciated!

tina wrote:

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.********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegrou ps.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:
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.********@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
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
>
>