Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Summary report

Posted on 2014-04-18
8
Medium Priority
?
280 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 39

Assisted Solution

by:PatHartman
PatHartman earned 200 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 1800 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

664 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