Solved

Query Summary report

Posted on 2014-04-18
8
276 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 37

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
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…

710 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