Solved

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

Posted on 2011-09-14
22
305 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now