Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Course Of The Month
5 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Access Query - Group by, but display field data in rows
Posted on 2011-09-14
Here's what I'm essentially trying to accomplish:
The table
What I want the query results to show:
My table will have anywhere from one row per ID (in this case year) to fifteen. I doubt this is possible without VBA, but maybe someone can enlighten me. I'm attaching a copy of my actual tables (MS Access 2007 ACCDB format) if a VB solution is required. (tblAwardsAll is the table that I need to accomplish this with-SSNs have been modified and last4's scrambled)
If VB is required and it is to make a table, it will need to overwrite/delete the existing table each time it is run. Also, it needs to be able to function as an ACCDE. I'm unsure if there are any limitations to creating/deleting tables via VBA using this locked format.
Thanks in advance for the help! I can do simple tasks in VBA but this is over my head.
Database1.accdb
The table
yr month nbr
2011 january 1
2011 february 2
2011 june 6
2012 february 2
2012 august 8
2012 december 12
2012 november 11
What I want the query results to show:
yr mo1 nbr1 mo2 nbr2 mo3 nbr3 mo4 nbr4
2011 january 1 february 2 june 6
2012 february 2 august 8 december 12 november 11
My table will have anywhere from one row per ID (in this case year) to fifteen. I doubt this is possible without VBA, but maybe someone can enlighten me. I'm attaching a copy of my actual tables (MS Access 2007 ACCDB format) if a VB solution is required. (tblAwardsAll is the table that I need to accomplish this with-SSNs have been modified and last4's scrambled)
If VB is required and it is to make a table, it will need to overwrite/delete the existing table each time it is run. Also, it needs to be able to function as an ACCDE. I'm unsure if there are any limitations to creating/deleting tables via VBA using this locked format.
Thanks in advance for the help! I can do simple tasks in VBA but this is over my head.
Database1.accdb
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
7
3- +1
22 Comments
Active today
@USArmy1
Do you know how to create a crosstab query? That would be the easiest transformation. Of course, a crosstab query would align the months.
I think you might benefit from this article:
http:/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
You can add the DConcat() code to your Access project.
Do you know how to create a crosstab query? That would be the easiest transformation. Of course, a crosstab query would align the months.
I think you might benefit from this article:
http:/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
You can add the DConcat() code to your Access project.
Active 6 days ago
1. You can pull the Month Name from the Month number.
So it seems that you would only need the Month Number and the year in your table.
I am confused by your output though...?
Since you provided a sample of your actual table can we frame the question based on the data there?
Which table are we dealing with? (tblAwardsAll?)
If so then what is the EXPR1 field?
What is the *exact* output you are expecting for this table?
So it seems that you would only need the Month Number and the year in your table.
I am confused by your output though...?
Since you provided a sample of your actual table can we frame the question based on the data there?
Which table are we dealing with? (tblAwardsAll?)
If so then what is the EXPR1 field?
What is the *exact* output you are expecting for this table?
Sorry, I guess my example above was a little oversimplified. I'm not really dealing with months here. Yes, tblAwardsAll is the table in question. Expr1 is, per say, the Employee ID number or SSN. When I created the new table and changed those values, I forgot to name it.
The expected result should list the ID number once (Expr1) and each award code(awnstrAwdCat) followed by the number of times awarded (awnintNbrGrnt). I don't necessarily need the date field. Then this would be repeated until all of the awards for the particular ID are listed on one row, and so on.
The expected result should list the ID number once (Expr1) and each award code(awnstrAwdCat) followed by the number of times awarded (awnintNbrGrnt). I don't necessarily need the date field. Then this would be repeated until all of the awards for the particular ID are listed on one row, and so on.
Active 6 days ago
Still would like to see a graphical example of the output with values from your sample data....
But start with this...
SELECT tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat,
FROM tblAwardsAll
GROUP BY tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat;
...and tell me what you think...
Perhaps I am way off bases here, so see what other Experts post as well.
But start with this...
SELECT tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat,
FROM tblAwardsAll
GROUP BY tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat;
...and tell me what you think...
Perhaps I am way off bases here, so see what other Experts post as well.
Not quite. Attached is your graphic example of what I'm looking for, but there should be 4944 records retrieved, because that is the number of distinct IDs (Expr1).
EXAMPLE-OUTPUT.xlsx
EXAMPLE-OUTPUT.xlsx
Active 6 days ago
It seems like it is beyond be to get you the exact output you are asking for.
Let's see if aikimark posts back.
In the mean time you can click the "Request Attention" link and ask that the SQL zone be added to this Q.
Perhaps an Expert there will be able to come up with something.
Let's see if aikimark posts back.
In the mean time you can click the "Request Attention" link and ask that the SQL zone be added to this Q.
Perhaps an Expert there will be able to come up with something.
Active today
I thought you were taking the lead on this one, Jeff. I've been watching.
=============
@USArmy1
I do not have Access2007/2010 on my system, so I can't see the database source data. If someone would please post an Access2003 (or earlier) version, I will be happy to look at it.
I never got a response to my question about a cross-tab query. Have you tried it?
Did read the article I linked to? Did you try the code?
=============
@USArmy1
I do not have Access2007/2010 on my system, so I can't see the database source data. If someone would please post an Access2003 (or earlier) version, I will be happy to look at it.
I never got a response to my question about a cross-tab query. Have you tried it?
Did read the article I linked to? Did you try the code?
Thanks for the effort thus far. I'm home from work now and haven't bothered to install 2007 on my home PC, so I'll post your request up in the morning, Aikimark.
@aikimark,
BTW, I didn't ignore your first comment. It's just taken me a while to try to figure out the information in that link. It seems VERY close to what I'm trying to do, however I need the values in separate columns for the report I need to create.
BTW, I didn't ignore your first comment. It's just taken me a while to try to figure out the information in that link. It seems VERY close to what I'm trying to do, however I need the values in separate columns for the report I need to create.
Active today
USArmy1,
test this.. click the button create new table
a new table "newtblAwardsAll" will be created
Database1-Rev.mdb
test this.. click the button create new table
a new table "newtblAwardsAll" will be created
Database1-Rev.mdb
Active today
@USArmy1
Before I dive into your database, please tell us about the report. Our solution should solve your actual problem.
Before I dive into your database, please tell us about the report. Our solution should solve your actual problem.
@aikimark:
This is the form that I am automating:
http://www.ngbpdc.ngb.army.mil/forms/adobe/ngb4100_1_r_e.pdf
Section II is what we're working with, and ideally I want to show on the report ONLY awards that are worth greater than zero points (awcintPts from tblAwardsCombinedCodes). I need to show it in each field as "CASAWDAR, 2 @ 20 Pts Total"
@capricorn1,
You've helped me in the past, so I know your programming skill is leagues and oceans above mine, however when I clicked your button, the end result told popped up a message box with the total time of processing (99 seconds), but did not create or change any actual tables...
This is the form that I am automating:
http://www.ngbpdc.ngb.army.mil/forms/adobe/ngb4100_1_r_e.pdf
Section II is what we're working with, and ideally I want to show on the report ONLY awards that are worth greater than zero points (awcintPts from tblAwardsCombinedCodes). I need to show it in each field as "CASAWDAR, 2 @ 20 Pts Total"
@capricorn1,
You've helped me in the past, so I know your programming skill is leagues and oceans above mine, however when I clicked your button, the end result told popped up a message box with the total time of processing (99 seconds), but did not create or change any actual tables...
Oops. I've never had that problem with accde files. Thanks for the clarification.
And that looks amazing and exactly what I'm looking for. I did not, however realize that we had people with 56 awards.
Is there a simple way to make this show only awards that are worth greater than zero points in tblAwardsCombinedCodes?
I will await your reply and will award you the points regardless.
@aikimark, thanks for your time and help! It looks like capricorn1's got me covered now.
And that looks amazing and exactly what I'm looking for. I did not, however realize that we had people with 56 awards.
Is there a simple way to make this show only awards that are worth greater than zero points in tblAwardsCombinedCodes?
I will await your reply and will award you the points regardless.
@aikimark, thanks for your time and help! It looks like capricorn1's got me covered now.
Active today
@USArmy1
That should be a two column sub-report that is fed by a Group By query. The main report and the sub report are linked by the ID (?Expr1?) field.
That should be a two column sub-report that is fed by a Group By query. The main report and the sub report are linked by the ID (?Expr1?) field.
Active today
I created two queries. The first one is the group by and the second one formats the data for your sub report.
AwardGroup
SubReportSource
AwardGroup
SELECT tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat,
tblAwardsAll.awnintNbrGrnt, tblAwardsCombinedCodes.awcintPts,
Sum([awnintNbrGrnt]*[awcintPts]) AS AwardedPoints
FROM tblAwardsAll INNER JOIN tblAwardsCombinedCodes ON
tblAwardsAll.awnstrAwdCat = tblAwardsCombinedCodes.awcstrCode
WHERE (((tblAwardsCombinedCodes.awcintPts)<>0))
GROUP BY tblAwardsAll.Expr1, tblAwardsAll.awnstrAwdCat,
tblAwardsAll.awnintNbrGrnt, tblAwardsCombinedCodes.awcintPts;
SubReportSource
SELECT AwardGroup.Expr1, [awnstrAwdCat] & ", " & [awnintNbrGrnt] & " @ " & [awcintPts] & " Pts: " & [AwardedPoints] AS ReportLine
FROM AwardGroup;
Thanks to both of you. I will look at both options and see which will suit my needs better. I like the procedural approach because I can use the table for several other needs as well, so I awarded capricorn1 more points.
Thank you both for your effort. As usual, Experts-Exchange hasn't let me down.
Thank you both for your effort. As usual, Experts-Exchange hasn't let me down.
Active today
You can get summary data from the group by query.
Total points
Count of non-zero point awards
If we go back to the tblAwardsAll table we can get information about all awards (pointed or not)
Count of all awards
Total points
SELECT AwardGroup.Expr1, Sum(AwardGroup.AwardedPoints) AS SumOfAwardedPoints
FROM AwardGroup
GROUP BY AwardGroup.Expr1;
Count of non-zero point awards
SELECT AwardGroup.Expr1, Count(AwardGroup.awnstrAwdCat) AS CountOfPointsawnstrAwdCat
FROM AwardGroup
GROUP BY AwardGroup.Expr1;
If we go back to the tblAwardsAll table we can get information about all awards (pointed or not)
Count of all awards
SELECT tblAwardsAll.Expr1, Count(tblAwardsAll.awnstrAwdCat) AS CountOfAllawnstrAwdCat
FROM tblAwardsAll
GROUP BY tblAwardsAll.Expr1;
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month5 days, left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.