Solved

Query Summary report

Posted on 2014-04-18
8
264 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 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
Comment Utility
@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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
>>>>>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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Beeyen
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Very Good! Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

11 Experts available now in Live!

Get 1:1 Help Now