Concatenate into multiple columns (1 Viewer)

[Galaxiom]

Wow Stopher, I am going to have to get my head around that.

But not tonight. I have already taken a similar path to ChrisO except my toxin is a Yellowglen bubbly.

 

[Uncle Gizmo]

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.

 

[stopher]

Wow Stopher, I am going to have to get my head around that.

But not tonight. I have already taken a similar path to ChrisO except my toxin is a Yellowglen bubbly.

It's probably easier to see if I show it here without the transformation. I've also removed the +1 and the "C" & which were only cosmetic.

SELECT C1, C2, DCount("[C2]","Table1","[C1]=" & [C1] & " AND [C2] <='" & [C2] & "'") AS ColID
FROM Table1

Produces

C1  C2   C3
1   MD    2
2   MD    1
3   MS    2
1   MA    1
3   KS    1

You can see that once a column ID has been generated then a pivot transformation is trivial.

The main priciple is to use a sequence to generate the column ID. An explanation of the method I used to apply a sequence is here. The only difference being that I need the sequence to start again for each C1 (rather than just one continuous sequence). So the [C1]=" & [C1] in the criteria deals with that.

I appreciate all the is a little hypothetical and an exercise for interest rather than practical.

Have a good drink and sleep well.

Chris

 

[ChrisO]

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.

 

[Galaxiom]

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

Anything is possible in Access with enough ingenuity and the curiosity to achieve it. Knowing how to kludge something is can be a useful skill.

There are times when the easy path is to answer the question rather than try to reinvent the database. Less than elegant solutions to meet a deadline can be a valid alternative.

But sometimes there comes a point where it makes sense to start over rather than work around oversights.

This is why it is so crucially important at the beginning of the design to think very hard about the data structure and how records need to be manipulated and reported. A little foresight can produce an extremely versatile database that actually becomes easier to build as the project matures while the wrong structure can make further development impractical.

Program structure is also important but data structure is the lynchpin in any design. Reuseable objects can allow rapid development of new program features but they only work if the underlying data structure is designed to support them.

 

[ChrisO]

Sounds a little pompous and condescending to me.

Here’s the way I see it; we don’t know.

We don’t know where the source data is coming from.
We don’t know if Lala has any control over the format of that data.
We don’t know why the destination data needs to be in different columns.
(I suspect it’s for column headings in the Report but I don’t know.)
(I also suspect that C1~CX} may not be the final column names required; but I don’t know.)
(It could also be for a chart in the Report where C1~Cx is the X axis; but I don’t know.)

I suspect that Lala is the only person who does know; but I don’t know that either.

So what do we do when we don’t know? Do we stay out of it altogether until we do know? Maybe, but that could also waste time.

Or do we ‘give it a shot’ based only on the information given and a bit of guess work?
I say we give it a shot because, even if it’s wrong, we are not left standing flat footed but can hit the ground running.
That is why the two demos I uploaded are called WhyNot and WhyNotBackwards. Why not?

So, until we do know, I think a lecture on the finer points of normalisation and programming is somewhat pompous and condescending.
Combined with your statement in post #19 of >>My query is better<< I have to ask…Better than what?
We don’t know.

Since today is Sunday I may have a go at charting it.
Even if I’m wrong, and I probably am, then at least I will learn something and that makes any day a good day to me.

It’s just recreational programming; and why not?

 

[RuralGuy]

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

 

[Uncle Gizmo]

Sounds a little pompous and condescending to me

Funny I don’t read it the same way you do, you see this a lot on the Internet, when you are interpreting the written word, you interpret it in the way that you want to, not necessarily the way the writer intended it to be read. So you want to be careful before you make such bold statements!

Here’s the way I see it; we don’t know

We don’t know, and that’s the point, and when you don’t know, the correct approach is to ask questions!

The other thing to do is make your observations. My observation is that if you are relying on the data to be in a particular column in the future, then it may not be in the column you expect it to be in. This is most probably irrelevant for this particular user, but, I don’t know! So I passed my observation on anyway.

This thread may be read by a different person with a similar problem at some time in the future; again in this “future” instance the observation may well be valid. But we don’t know! Also there will be a slight problem of not being able to ask the user!

Any of your comments directed at me? I don’t know, but just in case, this post!

 

[MarkK]

Now that is what I call assistance.

No s**t, eh! A fair bit of brain power was brought be bear.

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

Stopher, I think your solution is pretty cool. I'm impressed. I kicked at it for a while and couldn't see the way. I expected it would require creating a temp table and VBA.

Sounds a little pompous and condescending to me.

Like Uncle Gizmo, I don't see this part. I presumed that someone deleted his pompous condescending post after reading your response.
Or was it me? I sure as hell don't want to come across pompous. I don't mind condescending as much ...

 

[RuralGuy]

Thanks LagBolt for starting it off.

 

[ChrisO]

And so for the charting demo, for what it’s worth.

Regards,
Chris.

 

[ChrisO]

So that we are clear on this matter I was referring to post #25 and nothing else.

There are posting styles and some conventions on the Internet.
In a post, if someone is quoted, then the remainder of that post is pointed at the person quoted. That is if the focus, as set by the quote, is not moved away from the person quoted. In post #25 it remains with the quoted namely, me.

>>Sounds a little pompous and condescending to me.<< That’s my personal opinion and since post #25 was directed at me I believe I can express my personal opinion in reply.

But as some background, if people don’t already know it, I’ll try to explain.
I’ve been programming for 34 years, the last 14 of which in Access. Now granted I’m not the sharpest tool in the shed, if people want that they should go to Stephen Lebans site.

But some things have stuck over the years.

The main thing is don’t make assumptions about other peoples abilities or circumstances. Do not assume that they need a lecture on normalization or programming, we have no knowledge of that necessity.

Allow me to tell you a little story which took place at UA a few years ago and, as always, no names.

A person asked a question that involved around 150 check boxes on a Form. The then current normalization guru stated, without doubt, that is was absolutely a normalization problem. I chimed into the thread and asked the guru if he knew if the check boxes were bound to a table and, if not, how he could say that it was a normalization problem. I don’t think that question was ever answered. In other words, it was an assumption that lead to an absolute statement.

Now in post #25 I perceive a lecture on normalization and programming directed towards me, for the reasons as stated before. And, even if it wasn’t directed towards me, who knows Lala’s abilities? Why don’t we wait for the facts and then, if required, make some statement about normalization and programming?

But that was Saturday when the posts took place. We weren’t doing it just for free, free as in the Companies time (how some people get away with it a work I don’t know), we were doing it for free, free in our own time on the weekend. I don’t expect free time given to be answered with a lecture.

I’ll make one more observation, if I may. Till now only three working models have been uploaded into this thread; all by me. Sure, some SQL has been posted but was it untested? If it was tested then why post just the SQL; why not post the whole test bed? It makes it easier for people to test and see how it works. Why ask people to re-create something which should have been already created?

Regards,
Chris.

 

[Galaxiom]

Sounds a little pompous and condescending to me.

I certainly didn't mean to be pompous or condescending.
I actually began writing with something like this:

There are times when the easy path is to answer the question rather than try to reinvent the database.

By this I meant much the same as ChrisO posted. We all gave a shot at doing what Lala asked.

Or do we ‘give it a shot’ based only on the information given and a bit of guess work?
I say we give it a shot because, even if it’s wrong, we are not left standing flat footed but can hit the ground running.

So, until we do know, I think a lecture on the finer points of normalisation and programming is somewhat pompous and condescending.

Not intended as a lecture. These forums have an audience far beyond the the posters and to some extent I write to that audience. I know that I am not the only one with this perspective on our postings.

I thought the discussion of the reasons behind the task were completely relevant and then we provided three quite different ways to "skin the cat" (even if perhaps it looked like a stray cat).

My intention was to round out the discussion for those who find themselves reading this thread in isolation from the original discussion by summarising the what those who asked about the structure were implying. Data structure is the key to efficient data manipulation.

Combined with your statement in post #19 of >>My query is better<< I have to ask…Better than what?

Better then the vba in whynot.mdb
My query was quite tidy I thought. However I knew it would get messy for n columns. But since met lala's specification I posted it.

Stopher's is truly elegant though I admit I still don't fully grasp it. But I do know it is something with potential worth of study.

Since today is Sunday I may have a go at charting it.
Even if I’m wrong, and I probably am, then at least I will learn something and that makes any day a good day to me.

It’s just recreational programming; and why not?

If something does the job then it is a valid solution. Comparing totally different aproaches to a problem is stimulating which is why you called it recreational.

However to some extent by refering to it in this way you implied that the question was not as valid as any other. I was agreeing with lala's decision to go with something they knew even if it was technically a kludge.

FWIW I always took the opening post as a valid question. In fact I think the posters who had a problem with a requirement to denormalise the data were not exactly on the mark. Denormalisation is an intrinsic part of the reporting process as we convert the structures that work best in the machine to those suited to human interpretation. Simply the reverse of the familiar normalisation as data in imported from a spreadsheet.

If it was for a report then perhaps there would be other ways to use Access's reporting which I think some posters pointed out.

However using the query would be the shortest path to Excel rather than having to design a report to export the denormalised information.

 

[Galaxiom]

I’ll make one more observation, if I may. Till now only three working models have been uploaded into this thread; all by me. Sure, some SQL has been posted but was it untested?

So this invalidates our solutions?

If it was tested then why post just the SQL; why not post the whole test bed? It makes it easier for people to test and see how it works. Why ask people to re-create something which should have been already created?

Firstly posting the SQL is a lot more compact than attaching a database.

Aside from being much less effort to post, it is also easier for others to simply clip from the screen than go through the download process, unzip and open a separate database, then copy and paste the query into their own work.

With a few clicks, the sql can be pasted straight out of the sql view to the post. At the other end the same. They can then convert to design mode if they like. However note that the design mode of subquery structures (like my solution) is often not particularly pretty anyway.

I have a number of testbeds for what I post and I don't particularly want to export the relevant objects and remove the irrelevant fields from the tables. Basically posting the database I used for testing is more work than the sql which IMHO is more useful anyway.

Additionally posting the tables and dummy data is somewhat pointless. The OP has already go their tables and data.

 

[ChrisO]

In reply to post #33.

Well, I agree with most of what you say but will try to explain better what I mean by recreational programming.

If I may quote you: -
>>However to some extent by refering to it in this way you implied that the question was not as valid as any other.<<

This is not meant for you, as I’m sure you already know, but for anyone that posts a question this is what I mean.

The question is not as valid as any other.

The OP states in post #9 that basically there is no time to learn; this is a rush job.
The OP was asked, by lagbolt in post #15, why the different columns are needed, there was no answer.
The answer to that question is crucial to the problem.
Post #9 (rush) and Post #15 (lack of response to the question) places some order in my mind.

We are doing this on the weekend free of charge, why is the OP not with us?
What value does the OP put on ‘rush’?

If the OP is prepared to hang in here with us then that’s a different matter; everything else is just recreational programming to me.



In reply to post #34.

>>So this invalidates our solutions?<<
Of course not, but what it can do is allow the OP to typo the reply into oblivion.
So that, in your SQL, Min(Table1_1.C2) they may build a table by the name of Tabel1_1 and wonder why it doesn’t work. By uploading the test bed, even though it takes a little more effort, you know that what you uploaded works, well at least as far as it was tested by you under the circumstances.

That is the reason I prefer a working downloadable demo. You, you not the OP, can control it. I mentioned before about not assuming the abilities of the OP and that cuts both ways.

And now, if I don’t get any more interruptions , I would like to go and have a play with stopher’s elegant solution in post #20.

Regards,
Chris.

 

[RuralGuy]

You folks are tooooo much.

 

[ChrisO]

And how are you, Allan, on this glorious day in Brisbane?
(Currently 20 deg C here, with a slight cloud cover; but that’s winter. )

Regards,
Chris.

 

[RuralGuy]

Just great Chris, thanks for asking. I hope everything is fine with you as well. I always enjoy your posts; they keep everyone thinking.

 

[namliam]

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

Do you have some UNIQUE way of identifying the records?

 

[CraigDolphin]

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.