Solved

Summarizing data in Microsoft Access

Posted on 2014-10-15
2
204 Views
Last Modified: 2014-10-15
Experts,

In SAS I am able to use a Proc Summary and it gives me exactly what I want.  Is there  a way in Access to get the same results.

My SAS statement looks like this.

Proc Summary Data=CLAIMS_TOT;
  Var BILLED ADJUD CLAIM_CT;
  BY MAINT FFSORCAPIND LICENSE PROV_NAME;
  Output Out=Total_APR_2013 Sum=;
Run;

It gives me totals for Billed ADJUD and CLAIM_CT by prov_name within license within ffsocapind within Maint(DATE)
0
Comment
Question by:morinia
2 Comments
 
LVL 57
ID: 40382674
In Access, that would be accomplished with a SQL Statement either by:

1. Writing a SQL statement directly.
2. Using a querydef (which is a saved SQL Statement along with some properties to control processing).
3. Using a Domain Funtion, such as DSUM()  (you typically use this in places where SQL is not allowed, but an expression is - all the Domain Functions represent simple SQL Statements).
4. You write a custom VBA function to return the data you want and there are numerous ways that you might write that to actually grab the data..

Jim.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40382691
So the SQL query might look like:

SELECT Maint, FFSORDAPIND, LICENSE, Prov_Name, Sum(Billed) as SumOfBilled,
    SUM(AJDUD) as SumOfAdjud, SUM(CLAIM_CT) as SumOfClaimCt
FROM YourTableName
GROUP BY MAINT, FFSORCAPIND, LICENSE, PROV_NAME;
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

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