Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
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.
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.
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
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 1 day ago
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
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database.
Split up all multi-value fields into single values:
Split up fields that belong to other things into separate tables:
Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month14 days, 13 hours left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.