Concatenate into multiple columns (1 Viewer)

[lala]

oh my god
i didn't get the notifications and thought noone replied!!!!!!!
i'm going through everything right now, will reply as i go, thank you everyone so much!!!!!!!

 

[lala]

My 2c is that that result violates 1NF and is therefore a headache. Data in that shape is both hard to produce and hard to consume. I'd review your objectives and find a different way to get there.
What don't you like about this?

ID   States
1    MD, MA
2    MD
3    MS, KS

How is the other thing better?
Cheers,

it's not that it's better, i'm not designing databases at this job for the most part
what i'm doing is automating different reports using Access and VBA, so i have a format to work with and not much freedom on how the output should look like)))))))))))


i personally like this version and i know how to get it done, but they want columns for this report

 

[lala]

Perhaps the solution presented here will point you in the right direction.

Or perhaps check the solutions offered here or here

it did, if noone else replied that'd definitely be enough to get it to work
not as ingenious as you guys do here, but i'd manage))))))))

wow, i get all this praise from the people i work with for my access skills, when i come here i realize i don't know anything at all

you guys are freaking amazing

 

[lala]

This will do what you want for up to two states. It puts them in columns by alpha order.

SELECT C1, C2, IIF(Temp <> C2,Temp,Null) AS C3
FROM
   (SELECT Table1.C1, Min(Table1_1.C2) AS C2, Max(Table1_1.C2) AS Temp
   FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.C1 = Table1_1.C1
   GROUP BY Table1.C1)
;

Let me know if you need more columns.

i do, it can go up to 5 for each case

if i show you this spreadhseet with 220 columns you'd cry
trust me, i'd never design a database so poorly organized
but in this job i got into something new, using access, vba, excel macros and vbs to completely automate pulling data from oracle, manipulate it, export to excel, format it and save or email to whoever it goes

pretty cool, but unless i specify what i'm doing when i'm asking for help i always get raised eyebrows about my format)))))))))

 

[lala]

I agree with LagBolt, the point is your new layout does not contain any useful “meaning”.
Take for example your item listed as “1” it has two values, “MD” and “MA” which in the example you have shown translates to:

C1: 1, C2: “MD”, C3: “MA”

But also this would be correct:

C1: 1, C2: “MA”, C3: “MD”

So really the position of the states, “which column they appear in” depends on the order they appear in your list, so either your design is wrong, or there is some more information you have not given us which will be necessary to help us formulate the correct answer for you.

i didn't realize this had to be specified, you're right
the columns hav to be created in the order the entries appear
the states is an example, i'm working with doc degrees, specialties and stuff
so they have to go in the order they appear (they have a rank)

i thought once i figure out how to do the columns that will be easy to do, no?

 

[lala]

Anything is possible in Access with enough ingenuity and the curiosity to achieve it.

i say this all the time, thats why i love access so much despite all the "big shots" that diss it

everything else - i already explained why it has to be done this way
in the future i will explain it right away when asking a question
i do understand why you'd question this design, i would too
but ChrisO is right, i don't have control of the output, it has to look this wasy

so basically what i'm doing is i'm taking perfectly normalized data out of Orcale and making it into a mess with 220 columns))))))))))

 

[lala]

This query (assuming C1 is a number):

TRANSFORM Max(C2) AS ColID
SELECT C1
FROM Table1
GROUP BY C1
PIVOT "C" & (DCount("[C2]","Table1","[C1]=" & [C1] & " AND [C2] <='" & [C2] & "'")+1)

Produces this:

C1   C2   C3
1    MA   MD
2    MD
3    KS   MS

It will produce as many columns as required. It works by populating a column number field (ColID) and then cross-tabbing. The DCount function is used to provide the sequence. The +1 bit is simpy to ensure the columns start at 2.

Unfortunately the result in each row will be in alphabetical order since there is no other order defined in your list. If there is a primary key then the order can be corrected to exactly match what you requested.

But I agree with the others. What are you planning to do with the data in this structure? If you are simply putting it in a report then consider John's post or maybe use the multiple column feature in the print options.

hth
Chris

few questions

do i use your code in vba as a RunSQL, or just as an sql query in access

there's a primary key (just an autonumber, will that help? if not, i have a bunch to choose from)

i'm gonna try this out right now, hopwfully it works for me

thank you so much!!!!!!!!

 

[lala]

Yep, we don’t need a reason, it’s just recreational programming.

Columns C1 to C13 with lateral inversion of data. Demo attached.

Might be able to chart this but for the moment, Cyprus calls.

i think i'm slow but i don't get what the report is showing

or i have to create the query and use the function to see the results?

 

[lala]

Now that is what I call assistance. If and when Lala comes back from vacation she/he should be pleased. Thanks everybody. Great turnout.

i'm a she))))))))))))) with a name like lala))))))))))))))

i'm not on vacation))))))))))) i wish i checked this topic over the weekend, i'd get so much done

thank you thank you thank you all!!!!!!!!

 

[lala]

The easiest though somewhat limited way to do this is to use queries...

Do you have some UNIQUE way of identifying the records?

i do, autonumber you mean?

 

[lala]

Edit: Never mind. Didn't see there were two more pages of replies after the first page.

Lagbolt is right. However, it can be done. The key is to recognize that you're asking the database for information that you haven't expliclty provided to it.

To whit: look at ytour output. There's a column called C3. Look at your input. No such information exists. Your human brain can figure out what you mean, but computers require a more literal explanation.

Here's the SQL for a Query based on 'Table1' with your columns, and an autoid primary key field.

SELECT Table1.C1, Table1.C2, DCount("*","Table1","[C1]=" & [C1] & " AND [autoid]<=" & [autoid]) AS Expr1
FROM Table1;

This query counts how many times the value in C1 has been used. This then can be used as the basis for a column header field in a crosstab query. And the SQL for that would be:

TRANSFORM First(Query1.C2) AS FirstOfC2
SELECT Query1.C1
FROM Query1
GROUP BY Query1.C1
PIVOT "C" & [Expr1]+1;

And I tweaked it to make the column name follow the convention in your example.

this looks similar to stopher's, no?
i'm trying it out right now


also, i didn't understand what you meant by C3 not existing (columns names and data are fake, the column names they want are long and with spaces so i used C1, C2))))))))
anyway, yes, this column doesn't exist in the table and that's what i'm trying to do - create it
as a matter of fact, i'm trying to create as many columns as there are rows for each set
so i didn't understand what you meant that it had to exist in the original data

anyhow, i'm trying this out and thank you so much for your help

 

[CraigDolphin]

Yep...much the same as Stopher's. I came up with it independently but hadn't noticed there were other replies beyond the first page, so my post was redundant.

As for the point I was making, your output required a piece of information that was not inherent in the data you were using. The first query provides that extra bit of information (the field to be used for the column heading) and then the crosstab uses the output of the first query to generate your desired output. Regardless, Stopher's solution should work for you.

 

[lala]

Edit: Never mind. Didn't see there were two more pages of replies after the first page.

Lagbolt is right. However, it can be done. The key is to recognize that you're asking the database for information that you haven't expliclty provided to it.

To whit: look at ytour output. There's a column called C3. Look at your input. No such information exists. Your human brain can figure out what you mean, but computers require a more literal explanation.

Here's the SQL for a Query based on 'Table1' with your columns, and an autoid primary key field.

SELECT Table1.C1, Table1.C2, DCount("*","Table1","[C1]=" & [C1] & " AND [autoid]<=" & [autoid]) AS Expr1
FROM Table1;

This query counts how many times the value in C1 has been used. This then can be used as the basis for a column header field in a crosstab query. And the SQL for that would be:

TRANSFORM First(Query1.C2) AS FirstOfC2
SELECT Query1.C1
FROM Query1
GROUP BY Query1.C1
PIVOT "C" & [Expr1]+1;

And I tweaked it to make the column name follow the convention in your example.

this and stopher's example (they're the same) is amazing!!!!!!!!!!!!
please explain how you did this, this is so simple that i'm in awe

 

[lala]

this and stopher's example (they're the same) is amazing!!!!!!!!!!!!
please explain how you did this, this is so simple that i'm in awe

ok, i understood how it works, but one slight problem
i can't do anything with this data, i can't make it into a table, or append it or anything

 

[lala]

i'm sorry, disregard the last post, i'm an idiot

 

[RuralGuy]

i'm sorry, disregard the last post, i'm an idiot

Ahh...but you are getting wiser by the minute.

 

[lala]

Ahh...but you are getting wiser by the minute.

))))))))))))))))) it's contageous
with this many smart people here something had to rub off

 

[RuralGuy]

No rubbing on these Forums. There is a policy against it.

 

[boblarson]

No rubbing on these Forums. There is a policy against it.

Hmmm, Jon never mentioned that to me?

I'd like to see that one (or is it just an "unwritten rule" like in Baseball)?

 

[RuralGuy]

You know there is no crying in baseball Bob.