By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,482 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,482 IT Pros & Developers. It's quick & easy.

Query to transpose rows to columns via Primary Key

P: n/a
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

Dec 8 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

Dec 8 '06 #2

P: n/a
"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

Dec 8 '06 #3

P: n/a
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
Dec 8 '06 #4

P: n/a
"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

Dec 8 '06 #5

P: n/a
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
>

Dec 8 '06 #6

P: n/a
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


Dec 8 '06 #7

P: n/a
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
Dec 10 '06 #8

P: n/a
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
>
>

Dec 11 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.