473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Combine multiple records into one row with multiple columns [SOLVED]

63
I need to combine multiple records into one row with multiple columns for those records. Very similar to a crosstab query, but one that shows all the records, not a summary of them.

For example, if the table is like this:

Expand|Select|Wrap|Line Numbers
  1. Name     Month     Score
  2. John     Jan       92
  3. John     Jan       90
  4. John     Feb       97
  5. John     Feb       91
I want a query that gives these results like this:

Expand|Select|Wrap|Line Numbers
  1. Month     Name     Score1     Score2
  2. Jan       John     92         90
  3. Feb       John     97         91
Crosstab queries are set for summarizing data, but I don't want that--I just want ALL the scores laid out in a row for each person for each month.

I'm using Access 2007 and in this case, VBA is not an option.

Any help would be much appreciated.

EDIT: See complete solution down below.
Feb 18 '15 #1

✓ answered by Seth Schrock

So ignore the fix from the previous post. Try adding the following to the end of line 9 of your last code:
Expand|Select|Wrap|Line Numbers
  1.  AND T1.qID <= T2.qID

27 16095
Seth Schrock
2,965 Expert 2GB
Can there be more than two scores per person per month? There is a SQL solution for that, but the more scores, the harder it is to code.
Feb 18 '15 #2
postman
63
It would be a maximum of 4 scores.
Feb 18 '15 #3
Seth Schrock
2,965 Expert 2GB
Just an FYI, it is not a good idea to use Name or Month as field names as those are reserved words in Access. Access does permit you to use them, but it can sometimes cause problems.

Anyway, we first need to have an id field in your table. An autonumber field would be perfect for this. Then we need to create a querydef that puts a number beside each record to tell us if it is score 1, 2, 3 or 4. I'll assume a table name of tblScores.
Expand|Select|Wrap|Line Numbers
  1. SELECT ID
  2. , [Name]
  3. , [Month]
  4. , Score
  5. , DCount("*"
  6.     , "tblScores"
  7.     , "[Name] = '" & [Name] & 
  8.         "' And [Month] = '" & [Month] & 
  9.         "' And ID <= " & ID) 
  10.     As ScoreNum
  11. FROM tblScores
This should produce the results
Expand|Select|Wrap|Line Numbers
  1. ID    Name    Month    Score    ScoreNum
  2. 1     John    Jan      92       1
  3. 2     John    Jan      90       2
  4. 3     John    Feb      97       1
  5. 4     John    Feb      91       2
Save this query as qryScores. Now the query that gets the results that you want will have several subqueries to get the score numbers based on the ScoreNum field.
Expand|Select|Wrap|Line Numbers
  1. SELECT MQ.ID
  2. , MQ.[Name]
  3. , MQ.[Month]
  4. , S1.Score As Score1
  5. , S2.Score As Score2
  6. , S3.Score As Score3
  7. , S4.Score As Score4
  8. FROM qryScores As MQ 
  9. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 1) As S1 ON MQ.ID = S1.ID
  10. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 2) As S2 ON MQ.ID = S2.ID
  11. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 3) As S3 ON MQ.ID = S3.ID
  12. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 4) As S4 ON MQ.ID = S4.ID
Feb 18 '15 #4
jforbes
1,107 Expert 1GB
Nice Work! That is probably preferable to a Crosstab as you will have a predefined amount of columns.

But if you wanted to, since you already did the hard work of creating a RowNumber type column, you could use a Crosstab ;)
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(qryScores.[Month]) AS Scores
  2. SELECT qryScores.[Name], First(qryScores.[Month]) AS ReportMonth
  3. FROM qryScores
  4. GROUP BY qryScores.[Name], qryScores.[Month]
  5. PIVOT qryScores.[ScoreNum]
Feb 18 '15 #5
postman
63
Thanks, Seth.

I know about reserved names and ID fields. It was just a simple example to illustrate what I was after.

I actually tried this method with the DCount function before, but it ran extremely slow due to the aggregate function. But I see a few differences in your function's criteria that I'll try out.

I'll test and post results.

Thanks!
Feb 18 '15 #6
Seth Schrock
2,965 Expert 2GB
The only way that you can speed up the query is to reduce the number of records that it has to run the aggregate function on.
Feb 18 '15 #7
NeoPa
32,556 Expert Mod 16PB
Actually Seth, you could link in the data as a separate data source rather than use a Domain Aggregate function call. The latter are notoriously inefficient and should be avoided where possible within SQL.

As J Forbes illustrates this can still be done using a cross-tab. I would expect that to work more efficiently as you're letting the SQL engine to the hard work - which is what it's designed for.
Feb 18 '15 #8
postman
63
NeoPa, could you please clarify or give an example of linking in the data as a separate data source?

Thanks.
Feb 19 '15 #9
postman
63
Seth, the performance is much better on the DCount function using the method in your example, thank you.

But I keep getting a "syntax error (missing operator)" on the second query example you gave with the subqueries. The error is in the section with the LEFT JOINS.

Any ideas?
Feb 19 '15 #10
Seth Schrock
2,965 Expert 2GB
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT MQ.ID
  2. , MQ.[Name]
  3. , MQ.[Month]
  4. , S1.Score As Score1
  5. , S2.Score As Score2
  6. , S3.Score As Score3
  7. , S4.Score As Score4
  8. FROM ((((qryScores As MQ 
  9. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 1) As S1 ON MQ.ID = S1.ID)
  10. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 2) As S2 ON MQ.ID = S2.ID)
  11. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 3) As S3 ON MQ.ID = S3.ID)
  12. LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 4) As S4 ON MQ.ID = S4.ID)
Access sometimes doesn't like multiple joins without the parenthesis.
Feb 19 '15 #11
postman
63
Thanks.
That eliminated that error, but now it's prompting for a Score parameter when I run the query.
Feb 19 '15 #12
Seth Schrock
2,965 Expert 2GB
Can you copy and paste into this thread what you have just in case there is an error somewhere?
Feb 19 '15 #13
postman
63
Expand|Select|Wrap|Line Numbers
  1. SELECT MQ.qID
  2. , MQ.[sMonth]
  3. , MQ.[rName]
  4. , MQ.MgrName
  5. , S1.Score AS [Score 1]
  6. , S2.Score AS [Score 2]
  7. , S3.Score AS [Score 3]
  8. , S4.Score AS [Score 4]
  9. FROM (((([ScoreCount] AS MQ 
  10. LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 1) As S1 ON MQ.qID = S1.qID) 
  11. LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 2) As S2 ON MQ.qID = S2.qID) 
  12. LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 3) As S3 ON MQ.qID = S3.qID) 
  13. LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 4) As S4 ON MQ.qID = S4.qID);
  14.  
Feb 19 '15 #14
Seth Schrock
2,965 Expert 2GB
Well, nothing is obviously wrong. Try removing lines 6 through 8 and see if you get an error. Keep adding lines one at a time until you do receive the error and then let us know which one errors out.
Feb 19 '15 #15
postman
63
I tried that and I still get the prompt to enter a Parameter Value for Score.

I also removed lines 10-12 but still get the prompt.

Any other ideas?
Feb 20 '15 #16
Seth Schrock
2,965 Expert 2GB
It sounds like the field name Score isn't accurate.
Feb 20 '15 #17
postman
63
Okay, it was my fault. Had the Score field name wrong. Fixed that and I don't get the prompt, but it's not giving the expected results.

It's just listing the first score and showing a different row for each record still.

And the final query runs extremely slow.

Any ideas? I really appreciate your help on this.
Feb 20 '15 #18
Seth Schrock
2,965 Expert 2GB
Did you start back with the query from post #14 and fix the score field name? As NeoPa and JForbes said, a cross tab might be better since you have the ScoreNum field.
Feb 20 '15 #19
postman
63
Yes fixed the Score field name.

Trying the crosstab again. I'll post results.

Do you know what NeoPa meant by "link in the data as a separate data source rather than use a Domain Aggregate function call" ?

Sounds like it might help the performance on the ScoreCount query that is getting the ScoreNum numbering.
Feb 20 '15 #20
Seth Schrock
2,965 Expert 2GB
You would create another subquery and use the Count() function. Try the following for your ScoreCount query:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.qID
  2. , T1.sMonth
  3. , T1.rName
  4. , T1.MgrName
  5. , T1.Score
  6. , Count(T2.*) As ScoreNum
  7. FROM tblScores As T1 LEFT JOIN
  8.     (SELECT * FROM tblScores) As T2 
  9.     ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth
Feb 20 '15 #21
postman
63
I get this error when saving the query: "Syntax error in query expression 'Count(T2.*)'"

So I tried putting a field name: 'Count(T2.qID)' and that got past the error.

But when I ran the query, I got an error saying it did not include the specified expression 'qID' as part of an aggregate function--in reference to the qID in the SELECT statement at the beginning.

I really appreciate the help on this because I really want this to work. Using the other way with the DCount takes 45 seconds just to open that query, so I'm hoping the performance with this method can cut that down.
Feb 20 '15 #22
postman
63
I added GROUP BY to the end and that solved the error. But it's just showing a total count for each person for each month on each row--not the incremental count.

Here's the query now:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.qID
  2. , T1.sMonth
  3. , T1.rName
  4. , T1.MgrName
  5. , T1.Score
  6. , Count(T2.qID) As ScoreNum
  7. FROM tblScores As T1 LEFT JOIN
  8.     (SELECT * FROM tblScores) As T2 
  9.     ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth
  10. GROUP BY T1.qID, T1.sMonth, T1.rName, T1.MgrName, T1.qScore
Produces results like this:
Expand|Select|Wrap|Line Numbers
  1. qID  sMonth  rName    MgrName    Score  ScoreNum
  2. 1    Jan     John     George     92     3
  3. 2    Jan     John     George     94     3
  4. 3    Jan     John     George     98     3
  5. 4    Feb     John     George     97     2
  6. 5    Feb     John     George     91     2
Feb 20 '15 #23
Seth Schrock
2,965 Expert 2GB
Try removing the qID field from both the SELECT and GROUP BY clauses.

***Edit*** The above won't work. The Count function needs modified to only get records with lesser qID values that have matching sMonth and rName fields. I'm looking into this.
Feb 20 '15 #24
Seth Schrock
2,965 Expert 2GB
So ignore the fix from the previous post. Try adding the following to the end of line 9 of your last code:
Expand|Select|Wrap|Line Numbers
  1.  AND T1.qID <= T2.qID
Feb 20 '15 #25
postman
63
That did it! And the performance is WAY better.

Thank you, Seth!

Here is the complete solution:

Step 1: Created a SourceCount query to provide numbered results for each person in each time period:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.qID
  2. , T1.sMonth
  3. , T1.rName
  4. , T1.MgrName
  5. , T1.Score
  6. , Count(T2.qID) As ScoreNum
  7. FROM tblScores As T1 LEFT JOIN
  8.     (SELECT * FROM tblScores) As T2 
  9.     ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth AND T1.qID <= T2.qID 
  10. GROUP BY T1.qID, T1.sMonth, T1.rName, T1.MgrName, T1.qScore

Which gives results like this:
Expand|Select|Wrap|Line Numbers
  1. qID  sMonth  rName    MgrName    Score  ScoreNum
  2. 1    Jan     John     George     92     2
  3. 2    Jan     John     George     94     3
  4. 3    Jan     John     George     98     1
  5. 4    Feb     John     George     97     1
  6. 5    Feb     John     George     91     2

Step 2: Create a crosstab query:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First([ScoreCount].qScore) AS Score
  2. SELECT First([ScoreCount].[sMonth]), [ScoreCount].[rName], [ScoreCount].MgrName
  3. FROM ScoreCount
  4. GROUP BY [ScoreCount].[sMonth], [ScoreCount].[rName], [ScoreCount].MgrName
  5. PIVOT [ScoreCount].[ScoreNum];

Which gives the desired final result:
Expand|Select|Wrap|Line Numbers
  1. sMonth  rName    MgrName    Score1  Score2  Score3  
  2. Jan     John     George     98      92      94
  3. Feb     John     George     97      91
  4.  
Thanks again for everyone's help!
Feb 20 '15 #26
Seth Schrock
2,965 Expert 2GB
Glad you got it to work. Good luck on your project.
Feb 20 '15 #27
NeoPa
32,556 Expert Mod 16PB
Congratulations Seth. Good work.

@Postman.
Please accept my apologies for leaving you without my response for so long. Work is keeping me very busy ATM and I don't get to visit as often as I'd like to. This is one I would have enjoyed too.

Luckily our Seth is getting more and more experienced and was able to resolve it for you.
Mar 2 '15 #28

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: glenn | last post by:
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be...
2
by: Omega | last post by:
Hi How do I make my datagrid to display multiple records on a row ? With: <Columns> <asp:TemplateColumn> <ItemTemplate> <td>Blablabla</td>
5
by: jhutchings | last post by:
Hello everyone, I have a database where I collect shipment data from various tables. However, I have a problem. Whenever I want to see shipping data for orders that were set to ship on or before...
4
by: musicloverlch | last post by:
I have a table with multiple records in it. I am being forced to combine multiple records into one record in order to be uploaded to a website, and I only get one record per client. How can I...
1
by: NumberCruncher | last post by:
Hi All, I am struggling with setting up my first system of tables, forms,and reports, and could use your help! I am setting up a database to keep track of the production of a produced item. The...
4
werks
by: werks | last post by:
Hello experts could someone tell me how can i combine multiple records into one value. Sample in MS Access: (Lastname & "" & "," & " " & Firstname) AS Name How can i do this in MySQL?..tnx in...
1
by: bluereign | last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script...
7
by: mattd | last post by:
I have a data file that contains multiple records for each employee. I'm trying to build a view of the table that would show me a combined date range for per location. The example below are the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.