Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22

    Question Calculating the Sum of Mulitple Columns

    I want to be able to pull a query, and it have instead of False positive for A B and C one column of False Positives (sum of abc). I want to be able to combine certain columns to make the query I pull much smaller, but need the information to be seperate due to other reports.



    I've tried everything that should work, and it just doesnt. Please help.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would be very helpful if you would post the SQL of the query you tried (or 2 or 3). Some sample data and an example or 2 of the output you want would also be nice.

  3. #3
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    SELECT [Monthly %].[Month], [Monthly %].[Day], [Monthly %].[Year], [Monthly %].[Suicide Risk], [Monthly %].[Comments], [Monthly %].[Clinic], [FP (Dep)]+[FP (PTSD)]+[FP(Both)] AS Expr1, [Monthly %].[FP (Dep)], [Monthly %].[FP (PTSD)], [Monthly %].[FP(Both)], [Monthly %].[R-Mil Declined], [Monthly %].[BH Declined], [Monthly %].[Both RM & BH Declined], [Monthly %].[Already in R-Mil], [Monthly %].[Already in BH], [Monthly %].[Already in Both], [Monthly %].[Already Seeing Other PS], [Monthly %].[Referral to R-Mil], [Monthly %].[Referral to BH], [Monthly %].[Refer to RM/BH], [Monthly %].[Referral to Other PS], [Monthly %].[PCM Treatment Only], [Monthly %].[No BH needed], [Monthly %].[Xtra Form Decline], [Monthly %].[Disp Incomp]
    FROM [Monthly %]
    WHERE ((Not ([Monthly %].[Suicide Risk])="blank"));

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, we need to get the query returning data.

    What data type are the fields [FP (Dep)], [FP (PTSD)] & [FP(Both)] ??

    What data type is the field [Suicide Risk]? (Sample data?)
    Does the field [Suicide Risk] really contain the word "blank"?
    Code:
    WHERE ((Not ([Monthly %].[Suicide Risk])="blank"));
    If there is nothing entered, it might be Empty or Null. You cannot test for Empty or Null using "blank".You might try
    Code:
    WHERE Not IsNull([Monthly %].[Suicide Risk]);
    BTW, "Month", "Day" & "Year" are reserved words in Access. They are also functions. Using "Month", "Day" or "Year" as field names will cause you to bang your head against the wall....

    In addition, they are not very descriptive. "Month" of what? DOB, Lunar phase?

    Names of objects (fields, tables, queries)also should not have spaces. Nor should you use special characters / , & , () , %
    (Underscores "_" are OK.)

  5. #5
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    Ok, let me explain. The information is pulled off a form, where in the provider will mark a suicide risk "low, med, high"... if the field (text) is blank I do not want it to pull the field... but that's not the problem.
    The problem is getting the number fields to add together ie: all the FP's adding together by date. so instead of 3 seperate fields I'll have one FP field.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When I ask questions, its because I cannot see your dB. I do not know what your data looks like or the structure of your tables. It makes a difference on how I respond.

    Ok, let me explain. The information is pulled off a form, where in the provider will mark a suicide risk "low, med, high"... if the field (text) is blank I do not want it to pull the field... but that's not the problem.
    It is a problem. Now I know the data in [Suicide Risk] is text and the data is "low", "med" or "high" but not (the word) "blank". If you have

    WHERE ((Not ([Monthly %].[Suicide Risk])="blank"))

    ALL records that do not have (the word) "blank" in the field [Suicide Risk] will be returned, INCLUDING records where [Suicide Risk] is NULL or EMPTY.


    The problem is getting the number fields to add together ie: all the FP's adding together by date. so instead of 3 seperate fields I'll have one FP field.
    OK. What data types are the fields [FP (Dep)], [FP (PTSD)] & [FP(Both)]? Are they Yes/No, Text or Numbers?
    It makes a difference on how I do this calculation: [FP (Dep)]+[FP (PTSD)]+[FP(Both)] AS Expr1


    Also, is
    [Monthly %] a table or a query?

    So, short of having your mdb in hand (but I only have A2K3), I have to ask lots of questions to be able to give (or try to give) you a correct answer.

    Hang in there. I think we can solve this......

  7. #7
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    Believe me, I appriciate your patients and time. I know it's dificult when you can't see the db personally. I went ahead and changed it to Null, however blank was actually working just fine... IDK why, but where it was not Blank left out the null and pulled all of the ones marked L, M, H, N/A....

    Monthly % is the name of a table. The main table. What I'm trying to pull is a Suicide report, the numbers originally get entered into a form (as check marks/combo box) but the data is stored in the main table as -1 for a check.

    (I'm a bit sick, so I hope I'm making sence and helping/explaining what you're asking)

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Attached is a sample dB. I made a table with only a few fields. I created a query..... is the query close to what you want? (I changed Expr1: to SumFP: )

    It is a A2K3 mdb in A2K format........

  9. #9
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    Ok, yes and no. That's what I want, but I do not have check boxes in the actual table, it's -1's. the caluclation you used I've tried, it just doesn't work. I don't know why. Maybe it has to be check boxes.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ARickert View Post
    Ok, yes and no. That's what I want, but I do not have check boxes in the actual table, it's -1's. the caluclation you used I've tried, it just doesn't work. I don't know why. Maybe it has to be check boxes.

    OK, if you don't have check boxes in the actual table, then what data types are the fields [FP (Dep)], [FP (PTSD)] and [FP(Both)]? Your choices are Text, Yes/No, Long Integer, Integer.


    , it just doesn't work.
    What doesn't work??????? Be specific. Returns the wrong value? Always negative?

  11. #11
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    Quote Originally Posted by ARickert View Post
    The problem is getting the number fields to add together ie: all the FP's adding together by date. so instead of 3 seperate fields I'll have one FP field.
    Quote Originally Posted by ARickert View Post
    the numbers originally get entered into a form (as check marks/combo box) but the data is stored in the main table as -1 for a check.
    It is stored as a NUMBER (-1) INTEGER... in the table... therefore when it's pulled into the Query it is an INTEGER.

    Aside from that what I mean by doesn't work, is the column is blank. It's not returning anything...

  12. #12
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What about this one???

  13. #13
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    That's exactly it. II had tried to use the same expresion before, but when I put SumFP: Abs([FP (Dep)]+[FP (PTSD)]+[FP(Both)]) in my query, it returns nothing. all the information is there, but THAT column is blank. UGH, this is frusterating.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So the A2K3 query returns a value for the sum of the 3 fields, but in A2K7 summing the 3 fields displays nothing??

  15. #15
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I copied the A2K3 mdb to a co-worker's laptop that has A2K7 on it. The query ran and returned the same results as on my desktop. Then I converted the mdb to accmdb format. I ran my query and all columns returned the expected values. I recreated the query and the results were the same as the other two queries.

    Without having your accmdb to try, I am at a loss.... my cut down returns the correct results in both A2K3 and A2K7.

    Have you created a new blank dB and imported the objects? This would let you know if any objects were corrupt.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. handling nulls in mulitple parameter query
    By haggisns in forum Queries
    Replies: 5
    Last Post: 10-14-2010, 02:09 PM
  2. Query Can it be Done? Mulitple finds
    By Canadiangal in forum Queries
    Replies: 3
    Last Post: 02-28-2010, 03:45 PM
  3. Condense Mulitple Records
    By jquickuk in forum Queries
    Replies: 1
    Last Post: 08-10-2009, 08:43 AM
  4. Replies: 5
    Last Post: 08-06-2009, 11:47 PM
  5. Replies: 0
    Last Post: 02-26-2009, 03:34 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums