ACCESS FEED: Re: [MS_AccessPros] Group by issue

Sabtu, 26 Mei 2012

Re: [MS_AccessPros] Group by issue

John,

That was perfect. Thank you.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, May 26, 2012 2:53 PM
Subject: RE: [MS_AccessPros] Group by issue

Art-

You need two queries:

qryCountApps:

SELECT HouseHoldID, Count(ApplicationID) As AppCount

FROM tblApplicationHousehold

GROUP BY HouseHoldID;

SELECT Avg(AppCount) As AvgPerHousehold

FROM qryCountApps

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Saturday, May 26, 2012 9:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Group by issue

I am trying to get the avg number of household member per application:

The two fields that would be used is the applicationID and HouseholdID.

Something like this:

SELECT Avg(tblApplicationHousehold.HouseholdID) AS AvgOfHouseholdID,
tblApplicationHousehold.ApplicationID
FROM tblApplicationHousehold
GROUP BY tblApplicationHousehold.ApplicationID;

But this returns a number per application, I just want to have a single avg
returned for all applications.

Thanks,

Arthur Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]