union

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data in this format:

Market Plan Description Channel
ALH BNI000951 AF BP NGSM 1250 C2
ALH BNI000951 AF BP NGSM 1250 ITBG
ALH BNI001451 AF BP NGSM 1875 C2
ALH BNI001451 AF BP NGSM 1875 ITBG
ALH BNI002201 AF BP NGSM 2800 C2
ALH BNI002201 AF BP NGSM 2800 ITBG

I'd like to change it to this format:

Market Plan Description Channels
ALH BNI000951 AF BP NGSM 1250 C2,ITBG
ALH BNI001451 AF BP NGSM 1875 C2,ITBG
ALH BNI002201 AF BP NGSM 2800 C2,ITBG

Any assistance would be appreciated.
 
I'm not trying to be snotty when I say my advice is to not do it. It would
reduce redundancy in your data (first three fields), but makes your channel
data much more cumbersome, harder to search, separate, etc.

The preferable alternative is to create a simple unique identifier for each
unique market, plan, description, make a new table to hold your channels, and
link them based on that unique identifier. In fact, if you consider market,
plan, description to be a multiple field key, your data is normalized pretty
satisfactorily as it is.

If you insist on following through with your initial request, there are a
few ways to go about this, depending upon how many possible channels you have
(since your data was just a sample, I'm not going to assume there aren't more
channel possibilities). Are "C2" and "ITBG" the only possibilities? Taking
care of that would be pretty simple.
 
Thanks for the reply. I was remiss in not stating up front that the
transformation was for reporting / presentation purposes. I appreciate your
confirmation of the normalization (I'm fairly new so any bit of affirmation
helps!) but because the sample data I presented was the product of a join
query of two tables and its ultimate use is for "readability" in the format
to which I want to change it, please provide additional details on how to go
about this.

To further clarify, yes, there are at least 17 CHANNEL codes and not all
PLANs have all 17: one PLAN may have CHANNELs C2,ITBG,R3 while another PLAN
may have CHANNEL R3 only. In this example, then, I'd like the data to appear
as:

Market Plan Description Channels
ALH 1 Description1 C2,ITBG,R3
ALH 2 Description2 C3
....

Thanks again.
 
Allen said:
I have data in this format:

Market Plan Description Channel
ALH BNI000951 AF BP NGSM 1250 C2
ALH BNI000951 AF BP NGSM 1250 ITBG
ALH BNI001451 AF BP NGSM 1875 C2
ALH BNI001451 AF BP NGSM 1875 ITBG
ALH BNI002201 AF BP NGSM 2800 C2
ALH BNI002201 AF BP NGSM 2800 ITBG

I'd like to change it to this format:

Market Plan Description Channels
ALH BNI000951 AF BP NGSM 1250 C2,ITBG
ALH BNI001451 AF BP NGSM 1875 C2,ITBG
ALH BNI002201 AF BP NGSM 2800 C2,ITBG
Click to expand...


You can use a concatenate function for this. Probably
eliminate the Join in the report's record source query.
There are several of this kind of function out there on the
web and here's a popular one:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
 
Marsh,

I'm curious about your solution but I wasn't able to go to the sample you
suggested. Any other samples that you can suggest out there?
 
Allen,

Sorry, I should have realized since you were posting to reports that you're
concerned with modifying the presentation, not the table structure.

Grouping your report based on a concatenation of Market, Plan, Description
will work, but will look like this:

Market Plan Description Channel
ALH BNI000951 AF BP NGSM 1250 C2

ITBG
ALH BNI001451 AF BP NGSM 1875 C2

ITBG
ALH BNI002201 AF BP NGSM 2800 C2
ITBG

And I don't think that's what you're after. Perhaps the concatenation
Marshall suggests will work for you, but I can't follow that link.

You could deal with this programatically by using visual basic to step
through the recordset and create a new record in a target table every time
the Market, Plan, Description combination changes, concatenating your channel
value to the channel field in a sigle record until the Market, Plan,
Description combination changes. Perhaps that's the type of concatenation
function Marshall's suggesting.

Still stuck, just reply again.
 
Keith said:
I'm curious about your solution but I wasn't able to go to the sample you
suggested. Any other samples that you can suggest out there?
Click to expand...

Here's a pointer to the Google newsgroup archives with a
copy of Joe Foster's DConcat function (his web site is long
gone):
http://groups.google.com/group/comp...roup:*ms-access&rnum=9&hl=en#1d680de81b9ca2e9

Personally, I think this one is more confusing to use:
http://www.mvps.org/access/modules/mdl0004.htm

I think Duane's function is more intuitive so I suggest that
you make sure you have correctly copied the URL into your
browser's address box. It worked fine for me a few minutes
ago so keep trying.
 
Back
Top