ACCESS FEED: RE: [MS_AccessPros] adding up columns in query

Senin, 21 September 2015

RE: [MS_AccessPros] adding up columns in query

And if he has a fax number we wonder why he is living in the past.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 21 September 2015 17:22
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] adding up columns in query

Bob-

Excellent way to describe it.

I also like to use the example of a customer fax number. If it contains a set of numbers, that's the customer's number. If it contains a blank, we know the customer does not have a fax. And if it contains a Null, then we don't know whether the customer has a fax or not.

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 Sep 21, 2015, at 5:48 PM, 'Robert Peterson' bob@alternatefinishing.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I find a good way to wrap your head around Null is to think of it as "I don't know if anything goes here and if something does go here I don't know what it is".

If you add "I don't know" to anything it is still "I don't know" which is why we use Nz to turn the Null to zero before doing math.

Bob Peterson

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, September 21, 2015 10:46 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] adding up columns in query

actually, you can't HAVE nulls either ... there can be Nulls!

On 9/21/2015 8:41 AM, crystal 8 wrote:

Hi Patty,

> "null values "
fyi, there is no such thing ... null is the absence of a value
... just want to make sure you learn the term properly

you can have Nulls, but you can't have Null Values

warm regards,
crystal
On 9/21/2015 7:14 AM, pattykf@cox.net [MS_Access_Professionals] wrote:

THANK you Duane
I used the Nz and I was able to get the column to sum up the data, thank you so much
I did have null values in the columns
really appreciate the additional skill
Have a wonderful day
Patty

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Patty,
Are there any Nulls in the fields? If so you must wrap the column in the Nz() function like: Nz(PsychID,0)+

Are all of the columns right-aligned when viewed in datasheet?

Also, it's near impossible for us to actually determine what's going on without seeing some of the records that display the behaviour you are suggesting.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 18 Sep 2015 14:13:35 -0700
Subject: [MS_AccessPros] adding up columns in query

I am sure I have something missing here but I have tried two ways to have the query add columns together. I checked the queries that lead up to this to make sure the fields output to general numbers but it does not add up the information, I want the psychID, the counUrgent and the CountNoSHow to add up to give me a sum of the information
any help/hints will be appreciated
thank you so much
Patty

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, ER_HOSP_Psych_related.PsychID, ER_VISITS_6MOS_3_or_More_COUNT.CountUrgent, Appts__6MOS_2_or_More_No_Show_COUNT.CountNoSHow, [PsychID]+[CountUrgent]+[CountNoSHow] AS Risk_total_
FROM ((dbo_person LEFT JOIN ER_VISITS_6MOS_3_or_More_COUNT ON dbo_person.person_id = ER_VISITS_6MOS_3_or_More_COUNT.person_id) LEFT JOIN Appts__6MOS_2_or_More_No_Show_COUNT ON dbo_person.person_id = Appts__6MOS_2_or_More_No_Show_COUNT.person_id) LEFT JOIN ER_HOSP_Psych_related ON dbo_person.person_id = ER_HOSP_Psych_related.person_id;