Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Adding Columns in a query (XP)

    Hi,

    I have a query that is made up of multiple columns containing yes/no values. I set the "total" field to sum and am able to obtain the total for each individual column. My overall goal is to obtain the average for the row - in otherwords one average for the sums of each of the columns combined. I am not sure if this is possible, and of course if it is, I don't know how to write it.

    Thanks,
    Leesha

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding Columns in a query (XP)

    If I understand correctly, if you use an expression in a query like:<font color=blue><font face="Georgia">
    RowTotal = Column1 + Column2 + Column3 + ...</font color=blue></font face=georgia>
    Then if you had 5 columns, and three of them were yes, you would get a RowTotal = -3. If you then divide that by the number of columns, you would get the average, or the fraction of those that were Yes. In the example, it would be 0.60. I'm not sure you can use that expression in a Totals query, but you could try it. If it doesn't work, then do a Totals query based on the query that gives you the RowTotal. If I've misunderstood what you are tyring to do, post back - otherwise I hope it helps.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Wendell,

    You have a clear picture of what I want to do. I'm open to any suggestions on the best way to do this and am thrilled it can be done. So far putting in a formula such as =[column1]+[column2] and so on has not worked as I get a variety of errors. I've tried every combination of brakets, parentheses etc. I've tried it using the totals approach by summing everything first and still no luck. I've never done a query based on row total and didn't find much to help me in the help section of Access. Could you point me in the right direction?

    Thanks,
    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding Columns in a query (XP)

    Sorry, I mislead you with the way I formed the expression. In SQL terms, you create what is called an Alias by putting a colon ":" after the name, so the expression, which you simply type in the query grid where there would normally be a field name, should look like
    <font color=blue><font face="Georgia">
    RowTotal: [column1] + [column2] + [column3] . . . </font face=georgia></font color=blue>

    You will have to substitute your field names for the column1, column2 . . .. of course. Again, sorry for not getting it right last night - it was rather late my time.
    Wendell

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Columns in a query (XP)

    Here is another example, using test table (Table3) with two fields, Office (text) and Action (Yes/No), using the Abs() function to get "conditional" totals. Query SQL:

    SELECT Sum(Abs([Office]="A")) AS OfficeA, Sum(Abs([Office]="B")) AS OfficeB, Sum(Abs([Office]="C")) AS OfficeC, [OfficeA]+[OfficeB]+[OfficeC] AS [Office Total], ([OfficeA]+[OfficeB]+[OfficeC])/3 AS [Office Avg], Sum(Abs([Action]=True)) AS Expr4, Sum(Abs([Action]=False)) AS Expr5, [Expr4]+[Expr5] AS [Action Total]
    FROM Table3;

    When query opened, correctly sums the number of each Office and the number of Yes/No values, with totals for each field. See attached pic of example of query results.

    HTH
    Attached Images Attached Images

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Mark,

    Whew..................this is going to be a test if I can pull this one off but I'll give it a shot! Question, can this be done with more than one "action". For example, I need to get the totals for 5 or more fields/per office.

    Thanks,
    Leesha

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Columns in a query (XP)

    If you mean the other fields are Yes/No fields, and you need to know the totals (for each), grouped by Office??? It may be possible but would have to know more about how the table & fields are defined to provide a useful example.

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Mark,

    To explain better.................I have a database that contains three offices. Each office has "catagories" that contain multiple yes/no fields. For ease we could call them field1, field2, field3, field4 etc. The catagories are not actually a control source but simply a titles. The detailed report that I've built (thanks to formulas from Han's) do an abs(sum) for each field. What I now need to do its take the fields within a catagory and add them all together so I can get the average response rate for ALL the items in a catagory. This piece will act as the summary of the detail portion of the report.

    Leesha

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Columns in a query (XP)

    Leesha,

    Still not sure I totally comprehend setup - I had seen the other thread about the report which made me think of using "Office" and "Action" in example query. Not sure what this means:

    "The catagories are not actually a control source but simply a titles."

    Does that mean there are Categories of some kind, but there is no "Category" field in the table (or tables) used for the query/report??? Are the categories the same for each Office, or are they different? What are the Yes/No fields used to determine? It might be easier to figure out how to do this (if possible) if you can post a stripped-down copy of the database, with ONLY the table (or tables) used by query/report in question, the query or queries used for report, and the report. The tables should have some dummy data representative of the actual data. If this is not practical, plz provide a more detailed description of the table (or tables) and fields involved in this.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Columns in a query (XP)

    Thanx, got attachment, will take a look at it when get chance & advise further. (I forgot to mention I'm still using ACC 2000, but had no problem opening db file.) That form (.PDF file) does look convoluted - now I see why trying to describe all these fields, etc would not be practical....

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Mark,

    Now you see why I'm so nuts trying to do this! I much prefer Excel for this stuff but as it would be so much easier but then the user needs to be able to depend on someone knowing how to use excel vs pushing a button!

    BTW, I'm thinking the the Access version if Office XP is still 2000. I think I read that somewhere.

    Thanks,
    Leesha

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Mark,

    Thank you so much for your patience. I've enclosed a stripped down database with the table and query that has some of the yes/no items in it. I've also enclosed a copy of the data entry form used to gather the information so that it might make it clearer as to what the we are trying to acheive. The information in the query is for the section on the tool titled "Physician's Orders". The detailed report (not included) gives the # and % "yes" answers for each of the items under the "catagory" Physician's Orders. What I need to get to is the overall % complaince for the catagory physician orders. I could send it to Excel to analyze but the goal is that user is able to get all the data from Access.

    The report would look something like this:

    Norwich Old Lyme Willimantic Total
    Physician's Orders x% x% x% x%

    And yes, I will need to do this for each of the sections on the form I've enclosed but I can copy the concept for the other secions.


    Thanks,
    Leesha

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    The default file format of Access XP is 2000, but you can change that to 2002. XP can read 2000 files just fine, but 2000 sometimes has problems with 2000-format files created in XP.
    Charlotte

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Columns in a query (XP)

    Revised copy of QIP database (with dummy data) attached. Made some changes. Did not change main table (tblQuarterlyClinicalRecordReview) though I would've designed it w/o all the long field names, etc. Also would use a numerical ID field, not text field, for the lookups like OfficerName and Reviewer. One change, I did rename "OFFICE" as "OfficeName" to correspond to same field in lookup table to simplify things. Created dummy tblOfficeName & tblReviewer lookup tables. Added 2 new tables: tblCategories and tblCategoriesLookup. The tblCategories table enumerates Categories that correspond to the different sections in the (paper) form that you want to summarize. I added Descriptions for the fields in the main table to indicate which Category they fall under. These field descriptions were then used to populate the tblCategoriesLookup table for the Yes/No fields (see code module). This simplified things when attempting to design the queries to summarize data, as described below:

    - One query for each category, listing the Yes/No fields in that category, with totals for each Office (Count, Total, Avg, Percentage). The Percentage is actually same as Avg, only formatted as a percentage. The expressions use to derive totals are similar to those posted previously. These queries are named "qry" + name of Category.

    - Each of the Category queries described above is source for a crosstab query summarizing the percentages only, with the OfficeNames as column headings. These are are named "qry" + name of Category + "_XTAB".

    - The crosstab queries for each Category are "consolidated" into one result set by Union query, see qryCategoryUnion. If you open this query you'll see the percentages are dsiplayed as numbers, since you can't design Union query using query grid,and therefore cannot specify display format. So 2nd query based on Union query is used, see qryCategoryPercentageByOffice, which lists each Category (row) and Office (columns) and percentages as the value summarized.

    - Also see qryOfficeTotals - this query joins all the individual Category queries into one result set to display the data the other way around: the rows are the Offices, the columns are the Categories. Again, the values are the percentages.

    It'd probably be possible to create one real, big, query to summarize all the categories at once, instead of individually, but the long field names, etc would make for a very cumbersome design, that's why I used individual queries for each category. You may be able to adapt some of this for your project, using similar queries for other types of summary data. Note: Recommend disable "Name Autocorrect" (Options, General tab) - this socalled "feature" can cause all kinds of problems. Also recommend set Subdatasheet property for each table to "[None]" - this is another "feature" that causes slow performance & other problems. In this case, the data being summarized requires nestling of queries, keeping the Subdatasheet property set to [Auto] could slow things down. Plz advise if questions.

    HTH
    Attached Files Attached Files

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding Columns in a query (XP)

    Hi Mark,

    OMG, I am so amazed at what you did and it is so beyond anything I've ever done! I'm going to have to spend a great deal of time studing this to learn from it! I can't tell you how much I appreciate it!

    BTW, I agree with the names in the tables. Normally I would not set them up that way but there is so much data to gather that I decided to make the names something that was recognizable and then I decided to leave the spaces inbetween words vs string them together to simply save myself timeon building the reports.

    Thank you again!! You are wiz!

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •