Solved

Query Summary report

Posted on 2014-04-18
8
273 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
  • 4
  • 3
8 Comments
 
LVL 35

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 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