Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Access Query - Group by, but display field data in rows
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
Asked:
-
8
7
3- +1
2 Solutions
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.
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.
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
@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.
@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.
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.
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;
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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.