Creating a single record from multiple records 1

[rachleck]

Hi,

Wondered if anyone could help me, i have some data in the following format;

Team_ID Date Outcome
1 01/09/09 1
1 02/09/09 2
1 03/09/09 X
2 01/09/09 1

I would like to have a single line per team_id like the following;

Team_ID Outcome
1 12X
2 1

Im pretty new with VBA so struggling where to start really, ive read a few articles but all the examples are a little different to what i want to achieve. i dont know enough at the moment to confidently tweak any code examples ive seen. Can someone help me please??

 

[Darrylles]

Hi rach,

I think that you need to analyse exactly what it is that you want to report on.

E.g. If team 1 has 1500 outcomes of '1', do you want to see 1500 '1's printed on a single line?

Can a team only EVER have one outcome per day?
What period is the report to cover - a week - a month - a year - a decade?

If it's a month, what will the report tell you if a team has had every possible outcome in a month? Anything?

ATB

Darrylle











Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[rachleck]

Hi Darrylle

Thanks for your reply, I need to do it purely for reporting really. The string of outcome's would be only ever get to around 35 maximum, so i wouldnt end up with huge strings. The date isnt that important really other than to order the string so id like the first outcome to be the first character in the string reading from left to right. On the example i gave i just made dates up really, they are much less frequent than the dates i entered. I know it looks a little strange but thats how someone wants to view the data here! Any ideas on how i would go about it?

Thanks

 

[HarleyQuinn]

You might want to have a look at Duane's FAQ on this very subject: faq701-4233

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[rachleck]

Hi HarleyQuinn

Thanks for your help. I think that is actually one of the documents i looked at before posting. I couldn't quite work out what i needed to tweak to get it to work for me. One problem being that im not very familiar with VBA, my background really lies in crystal reporting, oracle sql and to a lesser extent oracle pl/sql. Im just starting out really with VBA stuff. Whilst the principles are pretty similar the VBA is sufficiently different to cause me problems!! Would anybody be so kind to perhaps explain the FAQ document or give me a few pointers as to how i can get it working for my particular problem. Its frustrating as i thought it would be a quick straight forward thing! i suppose they never are!!

 

[HarleyQuinn]

Of course

You will need to create a new module in Access and paste in the code from the FAQ.

Then in a new query (go to Design view, cancel the table selection and click on the SQL button in the top left) paste the following SQL (changing all instances of MyTestTable to the name of your table):

Select TeamID, MyResult 
FROM
(
SELECT MyTestTable.TeamID, Concatenate("Select Outcome FROM MyTestTable WHERE TeamID =" & [TeamID],"") AS MyResult
FROM MyTestTable
)
GROUP BY TeamID, MyResult;

Does that work for you? If it does post back and then if you need anything clarifying about how it works we'll be more than happy to oblige.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[rachleck]

Hi HarleyQuinn,

That worked perfectly for me thanks! I think i was overcomplicating things slightly. Originally when i looked at the FAQ i was attempting to amend the code in the module. I didnt actually realise it could work without making any changes. Looking at the SQL you provided its fairly straight forward. Always makes more sense when working with your actual fields and table names. I always find it difficult to get my head around generic examples.
Thank you very much!

 

[HarleyQuinn]

Glad you got it sorted, thanks for the star

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post