ACCESS FEED: Re: [MS_AccessPros] Count of count

Sabtu, 07 Februari 2015

Re: [MS_AccessPros] Count of count

Thanks John,

I also do not remember why I used "HAVING".

Your SQL did the job.

Cheers,

Robin Chapple

At 7/02/2015 07:00 PM, you wrote:

Robin-

Not sure why you included HAVING Count(Data.fFirstName) Is Not Null.

Your query should work without that as long as Females have a Null value in mFirstName (Count won't count Null) and vice-versa. Try this:

SELECT Count(Data.fFirstName) AS CountOffFirstName,
Count(Data.mFirstName) AS CountOfmFirstName,
Count(Data.fFirstName) + Count(Data.mFirstName) As TotalRes
FROM Data

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 7, 2015, at 6:18 AM, Robin Chapple robinski@westnet.com.au [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:

Thanks Duanne,

Females are in the field "fFirstName" and males are in the field "mFirstName". All are residents.

So I would count total residents as the sum of the two fields above.

Do you need any more information.

Cheers,

Robin

At 7/02/2015 03:32 PM, you wrote:

Robin,

I don't see anything in your SQL that identifies gender or residents. Do you have a field for Gender? If so, what are the possible values?

Can you provide some information about your table and how you would count "total residents"?

Duane Hookom, MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sat, 7 Feb 2015 10:17:55 +1100
> Subject: [MS_AccessPros] Count of count
>
> I have a query that counts males and females in my datatbase.
>
> SELECT Count(Data.fFirstName) AS CountOffFirstName,
> Count(Data.mFirstName) AS CountOfmFirstName
> FROM Data
> HAVING (((Count(Data.fFirstName)) Is Not Null));
>
> Is it possible to calculate total residents within the same query?
> Some residents are couples and are just one record in the table.
>
> Many thanks,
>
> Robin Chapple
>
>
>
> ------------------------------------
> Posted by: Robin Chapple <robinski@westnet.com.au >
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/
>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/join
> (Yahoo! ID required)
>
> <*> To change settings via email:
> MS_Access_Professionals-digest@yahoogroups.com
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
> <*> To unsubscribe from this group, send a n email to:
> MS_Access_Professionals-unsubscribe@yahoogroups.com
>
> <*> Your use of Yahoo Groups is subject to:
> https://info.yahoo.com/legal/us/yahoo/utos/terms/
>