Solved

Access Query - Group by, but display field data in rows

Posted on 2011-09-14
22
323 Views
Last Modified: 2012-06-27
Here's what I'm essentially trying to accomplish:

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

Open in new window


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

Open in new window


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
0
Comment
Question by:USArmy1
[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
 
LVL 45

Expert Comment

by:aikimark
ID: 36536855
@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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36537253
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?
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36537365
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36538539
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.
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36538626
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36538854
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36538983
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?
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36539937
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.
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36542293
Here are the files in 2003 format.

Database1.mdb
EXAMPLE-OUTPUT.xls
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36542392
@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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36542570
USArmy1,

test this.. click the button create new table

a new table "newtblAwardsAll" will be created


Database1-Rev.mdb
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36542578
@USArmy1

Before I dive into your database, please tell us about the report.  Our solution should solve your actual problem.
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36542650
@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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36542659
click on Tables and hit F5
0
 
LVL 1

Author Comment

by:USArmy1
ID: 36542697
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36542730
@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.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 36542769
USArmy1,

click the second button  create New Table_Points
Database1-Rev.mdb
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 150 total points
ID: 36542791
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;

Open in new window


SubReportSource
SELECT AwardGroup.Expr1, [awnstrAwdCat] & ", " & [awnintNbrGrnt] & " @ " & [awcintPts] & " Pts: " & [AwardedPoints] AS ReportLine 

FROM AwardGroup;

Open in new window

0
 
LVL 1

Author Closing Comment

by:USArmy1
ID: 36542830
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36542851
You can get summary data from the group by query.

Total points
SELECT AwardGroup.Expr1, Sum(AwardGroup.AwardedPoints) AS SumOfAwardedPoints 

FROM AwardGroup 

GROUP BY AwardGroup.Expr1;

Open in new window


Count of non-zero point awards
SELECT AwardGroup.Expr1, Count(AwardGroup.awnstrAwdCat) AS CountOfPointsawnstrAwdCat 

FROM AwardGroup 

GROUP BY AwardGroup.Expr1;

Open in new window


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;

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 36542912
@USArmy1

Be sure to compact your database on a regular basis if you run capricorn1's Table_Points creation process frequently.

If you want to explore the two column sub-report, please click the ask a related question link.
0

Featured Post

PeopleSoft Has Never Been Easier

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…

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.

Join & Ask a Question