How to do this using MS ACCESS - Microsoft Access / VBA

Mark

I am producing a report that requires me, among many other things, to
calculate the average contracts for about 1000 companies during the yr
2003. The "quirk" is that all the companies do not have the same
number of months. When using DB2 the below code counts the average
contracts of each group for 2003. What code can I use in MS ACCESS to
produce the same result?

SELECT

COMPANY_NAME,
SUM(CONTRACTS)/COUNT(DISTINCT FIRST_DAY_OF_MO NTH) contracts

FROM TABLE
Thanks

Nov 12 '05 #1

Subscribe Reply

2496

Larry Linson

Instead of expecting us to read DB2 SQL, could you just state the problem a
little more clearly? Access has Totals Queries with Group By and Avg, so I
suspect not only can Access do what you want, but do so easily without your
actually having to write your own SQL. There's a dandy Query Builder.

Specifically I'm not quite sure I know what you mean by "calculate the
average contracts for ... during the yr" and "... do not have the same
number of months".

Larry Linson
Microsoft Access MVP

"Mark" <mf******@hotma il.com> wrote in message
news:c8******** *************** ***@posting.goo gle.com...

I am producing a report that requires me, among many other things, to
calculate the average contracts for about 1000 companies during the yr
2003. The "quirk" is that all the companies do not have the same
number of months. When using DB2 the below code counts the average
contracts of each group for 2003. What code can I use in MS ACCESS to
produce the same result?

SELECT

COMPANY_NAME,
SUM(CONTRACTS)/COUNT(DISTINCT FIRST_DAY_OF_MO NTH) contracts

FROM TABLE
Thanks

Nov 12 '05 #2

Mark

"> Specifically I'm not quite sure I know what you mean by "calculate
the

average contracts for ... during the yr" and "... do not have the same
number of months"."

First I would like to thank you for responding to my question and
appologize for my lack of clarity. I have been using DB2 on a
mainframe for about 3 years now at work.(I am an actuary for an
insurance company). Recently I had a OBDC driver installed on my PC. I
have never used ACCESS before so I thought this might be a good
oportunity to learn it. Lets say that I need to count the average
number of monthly contracts for Wal-Mart("contracts " are the insured
individuals or families.) Wal-Mart has been with my company for the
entire year and this is October so I have 10 months of contracts to
count/average. I add up the number of contracts for each month, which
fluctuates, and divide by the number of months to get an average. If
all the 100 or so groups had a full 10 months of contracts it would be
simple. I would just divide the sum for each group by 10. The first
part of the problem arises when a group like United Air or Food Lion
comes on board in April or May. In this case I do not have 10 months
of data so I cant just divide by 10. I have to divide by 5 or 4
months respectively. The second part of the problem is that each group
can have thousands of entries on the DB2 table that I am hitting. To
count the months I took advantage of the fact that every month has a
1st day. Jan 1st, Feb 1st....Oct 1st. I count the number of distinct
1st days of each month and divide it into the total number of
contracts. This simple technique gives me accurate results when using
DB2. More about the report: The report is simple in description but
very difficult in practice(the story of my life) I am looking at all
the monies we charge a group(admin fees) and all the monies we are
paying out(primarily network access fees) on a per state basis. So I
really need to know the average number of contracts in each
state/country(which I break out later). The contract counts are a
VERY small part of the report. The DB2 proc that I hope to replace
with access runs 17 queries pulling data from 9 different DB2 tables.
When all is said and done the final report is about 250 pages. So
what I need to know is how to calculate the average number of
contracts per month over the number of months in the current year that
each particular group has been a client of my company.(Wow thats a
mouth full) One more thing I know that my method is probably not the
best way of accomplishing the task at hand but I am "self taught" so
have had no formal SQL training thus a limited tool box. In college I
was a physics/math major programming primarily with fortran and LaTex.

Thanks

Nov 12 '05 #3

Similar topics

2293

Using Access+DBMS with >30 users

by: Remco Groot Beumer | last post by:

Hello, I'm trying to decide if the following situation would be workable: Generate an MS Access Front End (which will run localy on client computers), which will link to a DBMS (SQL server or Oracle). As far as I know there will be approx. 30 till 40 users max working on the database. I think approx. 25 users will actually enter data (others will be mostly reporting and viewing). I don't really know the network capacity.

.NET Framework

6079

How to change conditional compiler constants using VBA?

by: Andrew | last post by:

I use conditional compiler constants, set through the VBA IDE in Tools, <projectname> Properties, that I refer to throughout my code to control which code is used during development, and which during production. Usually, this only wraps code used to control quitting the whole app versus just shutting a form, but it can also control many other things. However, as part of the build before delivering an update, I have to remember to...

Microsoft Access / VBA

2848

Limits of Access 2003 using ADP on SQL Server Express BE?

by: Matt Alanzo | last post by:

On another newsgroup an Access knowledgable party posted: >You should be able to connect an Access ADP to an existing SQLExpress >database running in SQLS 2000 compatibility mode. The only thing you >won't be able to do is access any of the design surfaces for SQLS >objects in the ADP. I checked with the Access team on this :-) > I appealed for clarification but without reply. Maybe somone on this newsgroup can provide insight.

Microsoft Access / VBA

4164

Require some feedback regarding using tree - list- view in Access XP

by: Norman Fritag | last post by:

Hi there I have avoided to use active x controls because I thought they are causing more problems then they are doing any good. I a new application I would want to use the tree and list view control in access 2002. Prior to that I like to fine some information or here some feed back from developers who have use active x controls success fully in there application, what their experience was.

Microsoft Access / VBA

4093

Need better argument for using bound forms

by: James Fortune | last post by:

I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate the code that implements managing unbound controls on forms given the superior performance of unbound controls in a client/server environment. I can easily understand a newbie using bound controls or someone with a tight deadline. I guess I need...

Microsoft Access / VBA

6587

Access expoert reports to PDF using PDF writer 6.0 issues

by: Grasshopper | last post by:

Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on where the job is running, the job runs sucessfully, PDF files got generated, everything is good. If I scheduled the job to run at the time that I am not logged into the server, Access is not able to print to the printer. The error is pretty...

Microsoft Access / VBA

10888

Error 1307: Adding File Permissions to NTFS using System.Management Object in ASP.NET

by: Ben Dewey | last post by:

Project: ---------------------------- I am creating a HTTPS File Transfer App using ASP.NET and C#. I am utilizing ActiveDirectory and windows security to manage the permissions. Why reinvent the wheel, right? Everything so far is working well with the Active Directory. The problem I am having is with adding File Permissions to a directory. I am currently using some code courtesy of "Willy Denoyette "

C# / C Sharp

3519

Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

by: James | last post by:

I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The objective in utilizing this new deployment method is to reduce the maintenance overhead as well as making it easier for my users to setup and run the application initially. I have VS 2002, Windows XP, Access XP(2000 format). He is my problem....

Visual Basic .NET

3044

Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups 1

by: James | last post by:

Visual Basic .NET

6721

How to get recordcount or number of records when using linked tables

by: Robert | last post by:

How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that contains only tables. I have linked the tables for the front end to the back end database. I am trying to set the recordsource of a form to a query established by the user to narrow the scope but I don't want to display the form if there are no...

Microsoft Access / VBA

8440

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8352

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8863

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8636

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7378

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

4192

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4358

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2765

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1763

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General