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
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.
0
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'.
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, Sum(tblAwardsAll.awnintNbrGrnt) AS SumOfawnintNbrGrnt
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
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.
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?
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.
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.
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...
I created two queries. The first one is the group by and the second one formats the data for your sub report.
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;
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.
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.
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.