Solved

Query Summary report

Posted on 2014-04-18
8
278 Views
Last Modified: 2014-04-25
Good Day Experts,

I have an Access Database attached with the field below. I would like to create a query that would provide a summary report rolled up by GPI 14 and MSI to include the fields below.  Then I would like to export the summary into Excel.

GPI14
GPI14Description
GenericIndicatorOverride
FormularyTier
#ofMember
ClaimIdentifier
TotalAllowedAmount
MemberPaidAmount
PlanPaid
NTI-Sample.accdb
0
Comment
Question by:Beeyen
[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
  • 4
  • 3
8 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 40008751
@Beeyan,
We can't always download your sample files so please tell us what problem you are having.  What you have posted sounds like a statement of work that you are expecting someone to do for you.  This is a help forum not a free programming forum.  In an earlier thread, I described how to create a totals query.  Did you try that?  What was your result?  Why was it different from what you expected?

I will repeat the instructions for convenience.
Click on the create query button.
Select the table from the list.
Select the columns you want in the output.
Click the sigma button to make this a Totals query.
Change the "Group By" to Sum/Count/whatever for the columns you want to aggregate.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40009596
I kind of agree with PatHartman ... we are here to help you with specific questions about various Access functions, objects, etc. and any problems you might be having using those.  It may not have been your intent but your question is structured in a way that says ... create me a query and or report ... It is not directed a a specific question.

That being said ... I looked at your sample ... please review the query and report in the attached modified sample db to see if that's what you are trying to accomplish.  Once the Totals query has been created ... Use the External Data menu option to export the query object out to Excel.

HTH ... ET
NTI-Sample2.accdb
0
 

Author Comment

by:Beeyen
ID: 40009804
Thanks for your assistance.  I have an excel spreadsheet with 114900 lines or records which I know how to get sum and total.  You have provided in Access a sample and idea of what I was looking for. The query was only one part.  The other idea is the report in a summary format.  I am currently researching a bit more into the report portion. I wll respond to the PatHartman's comments later.  thanks again.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40009873
>>>>>Thanks for your assistance.  I have an excel spreadsheet with 114900 lines or records which I know how to get sum and total.  You have provided in Access a sample and idea of what I was looking for. >>>>>

Yes, you can do the Grouping and Totals directly in the Report Object if need be.  If you want to export it to Excel then a query with Totals and Groups is what you will need.

At 114900 records ... I would recommend using a Access report with groupings and sub-totals in the group footers.  

Post back if you have additional questions about the various report options in Access or provide a more detailed sample of exactly what you want to see on the report.  We can help you with that.

ET
0
 

Author Comment

by:Beeyen
ID: 40011555
Good Day ET,

I looked at the report but due to my lack of experience, I could see the design but could not figure out how the information looks in a report format.  I think the best approach is the query with grouping and sub-totals then exporting into excel.  With that I can you assist me with summarizing the information by GPI14 and MSI to include GPI14, GPI14Description, MSI, GenericIndicatorOverride, FormularyTier, #ofutilizers =lname & fname, #ofclaims, dollarallowedamount, dollarpaidamount, dollarplanpaid.
I have added a number of records, renamed the fields than reran the query (attached NIT-sample 3.    But would like to return the details in the word format attached. Let me know what you think and thank you for any suggestion and details you can provide.
NTI-Sample3.accdb
Word-Format.docx
0
 

Author Comment

by:Beeyen
ID: 40011795
Good Day ET, I have decided to hold on the report in Access until I can spend additional time. But it you can provide me with another start based on my previous message that would be great.  I think I have worked out most of my query but am having problems with one of my statement.

I have written a statement to combine two fields "LastName and FirstName into one returning the first and last name.  I am also trying to count the number of names of which I have combined but do not want to include duplicate names in the results. Could you please suggest how I can do this?  Thank you.

SELECT tblWellmark_NTI.GenericProductIndicatorGPI14, tblWellmark_NTI.MultisourceIndicator, First(tblWellmark_NTI.GPI14Description) AS FirstOfGPI14Description, First(tblWellmark_NTI.GenericIndicatorOverride) AS FirstOfGenericIndicatorOverride, First(tblWellmark_NTI.FormularyTier) AS FirstOfFormularyTier, Count([MemberFirstName] & " " & [MemberLastName]) AS [# of Utilizers], Count(tblWellmark_NTI.ClaimIdentifier) AS CountOfClaimIdentifier, Sum(tblWellmark_NTI.MemberPaidAmount) AS SumOfMemberPaidAmount, Sum(tblWellmark_NTI.PlanPaid) AS SumOfPlanPaid, Sum(tblWellmark_NTI.TotalAllowedAmount) AS SumOfTotalAllowedAmount
FROM tblWellmark_NTI
GROUP BY tblWellmark_NTI.GenericProductIndicatorGPI14, tblWellmark_NTI.MultisourceIndicator
ORDER BY tblWellmark_NTI.GenericProductIndicatorGPI14, tblWellmark_NTI.MultisourceIndicator;
NTI-Sample3.accdb
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 450 total points
ID: 40013372
You would need to DCount() the MemberNumber field based on the GPI14 and MSI groupings as shown below.  You don't need the First and Last name fields.

SELECT [NTI Sample].GPI14, [NTI Sample].MSI, First([NTI Sample].GPI14Description) AS GPI14Description, First([NTI Sample].GenericIndicatorOverride) AS GenericIndicatorOverride, First([NTI Sample].FormularyTier) AS FormularyTier, Count([NTI Sample].Claims) AS Claims, Sum([NTI Sample].Dollarmemberpaidamount) AS Dollarmemberpaidamount, Sum([NTI Sample].Dollarplanpaidamount) AS Dollarplanpaidamount, Sum([NTI Sample].Dollarallowedamount) AS Dollarallowedamount, DCount("MemberNumber","[NTI Sample]","[GPI14] = '" & [GPI14] & "' AND [MSI] = '" & [MSI] & "'") AS Number_Of_Utilizers
FROM [NTI Sample]
GROUP BY [NTI Sample].GPI14, [NTI Sample].MSI
ORDER BY [NTI Sample].GPI14, [NTI Sample].MSI;


Refer to the Copy of qrySummary Query in the attached sample db.

ET
NTI-Sample3-Fixed.accdb
0
 

Author Closing Comment

by:Beeyen
ID: 40023769
Very Good! Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

623 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