Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16

    Explain what this code does inside ACCESS SQL Query

    It has been a long time since I used access can someone explain to me what this code is doing? Someone previous to me created it.

    AND ((HRA_DATA_ALL_03092012.HospAdmitPastSixFlag) Like "*y*")
    AND ((Format([NumDailyMeds],"General Number"))<>" "And (Format([NumDailyMeds],"General Number"))<>"0"


    And (Format([NumDailyMeds],"General Number"))<>"1" And (Format([NumDailyMeds],"General Number"))<>"2"
    And (Format([NumDailyMeds],"General Number"))<>"3" And (Format([NumDailyMeds],"General Number"))<>"4"
    And (Format([NumDailyMeds],"General Number"))<>"5" And (Format([NumDailyMeds],"General Number"))<>"6"
    And (Format([NumDailyMeds],"General Number"))<>"7" And (Format([NumDailyMeds],"General Number"))<>"8"
    And (Format([NumDailyMeds],"General Number"))<>"9")

    the NumDailyMeds field in the database is only one field where they put how many medications a person is on. Also I have fields that are questions with drop down list of 4 possible answers I need t create a code that can count each answer. ex: Possible Answers: "Not much, A little, Never, Not at all" I want to know how many of each of these answers are in the database. Is that possible? Please help Thanks!!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like filter criteria that is excluding all records where NumDailyMeds field is a single digit value.

    I don't know why someone felt they needed to apply Format function. Format function results in a string (text) value.

    Why not just:

    ... AND HRA_DATA_ALL_03092012.HospAdmitPastSixFlag Like "*y*" AND NumDailyMeds>9 ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    The actual field says NumberDailyMeds:Format([NumDailyMeds],"General Number")
    then on 4 lines below it has this repeating in the Creteria Field:
    <>" " And <>"0" And <>"1" And <>"2" And <>"3" And <>"4" And <>"5" And <>"6" And <>"7" And <>"8" And <>"9"

    but I believe it is because there are different conditions that answer like*y* and can be a Yes or No Answer like a DiabetersFlag, CancerFlg, COPDFlag etc.
    Attached Thumbnails Attached Thumbnails daily med field.JPG  

  4. #4
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    But your right seems like they could have just put >9 for each condition!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Same question applies to the constructed field expression, why the Format function?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    You know I don't know I am acquiring reports that were created by someone who no longer works here, Im more familiar with SQL than Access they created everything using Access Query Wizards so when I look at the SQL, I see these long strings of Creteria that repeat over and over Im really not sure about the field as it comes from a table someone imported into the database and not one that we are linked to. They imported the flat file table and then linked it to a Table we have in the warehouse. It's all confusing so and no one to show me why they did this only that it works right now but we may need to make some changes. So Im trying to see what the parameters are on these reports and why they did them they way they did. Sorry1

  7. #7
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    I assume that field is in a query? The reason that long string appears 5 times is that for each of the 5 occurances there will be some other field in the query with criteria in that same line; what you have is 5 "AND" conditions joined by "OR", like this:

    (A AND X) OR (B AND X) OR (C AND X) OR (D AND X) OR (E AND X) , where "X" is that long string.

    Take a look at the query and see if that is the case. If you feel ambitious, look at the SQL the query generates - it won't be pretty!

    HTH

    John

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    To answer your second question - counting the responses - yes it is easily done, but how easy depends on the structure of your table(s). Can you give some information on that, please?

    John

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, and simpler SQL structure would be, although Access probably makes it look more complicated:

    X AND (A OR B OR C OR D OR E)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    We have several Questions that can be answered in 4 or so ways some of the questions are simple yes or no answers.
    Example questons: Highest Level of Education Answers are in the same field "Elementary, Middle School, High School/GED, College, 2 years, 4 years" These are the possible answers someone can give for this one question. How is your overall health: "Poor, Good, Very Good, None Selected".

    So now the answers to these questions are going to be scored each answer will have a point value associated with it from 0 to 5. However the school one Elementary, Middle and High School are all worth a point per answer excep if they go above that and those have a 0 score. The information is coming from a table that has been imported into the Access DataBase. It is linked to a table in our warehouse so we can retrieve other information to add to the report from our Member Table. I really don't think this can be done the way the table is structured. I also don't know how we can tell well there were 3 Middle School answers that's 3 points 5 High School Answers that is 5 points! I am befuddled!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A table in 'warehouse' - what is warehouse?

    We don't know your data structure so can't say how can be done. If you want to provide project for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    A data warehouse is where tables relational data sits on an Oracle Server for Reporting Purposes. The Access Database has an ODBC connection to this data. However, the Questionaire is sent in via a Flat File or excel spreadsheet then uploaded into the Access database in the particular Report we are using we have a Member Table Linked to the flat file on MemberID. An no I do not need it analized I am just trying to find out if there is a way to Do an If then Else Statement in Access ex:
    If Highest_Level_Educaton 'Middle_School' Then 1
    If Overall_Health "Good" then 3 so that we can assign each answer the point value. But since the Field is How is your Overall_Health? And the answers are in a Dropdown selection If you assign the Point to the answer and you assign the same point to several Answers in the dropdown like they did here I cannot say How many Middle School? HowMany Elementary ? Because they are in the same field and they have the same point value.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One possible expression:
    Switch(IsNull(Overall_Health),0, Overall_Health="None Selected",1, Overall_Health="Poor",2, Overall_Health="Good",3, Overall_Health="Very Good",4)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    Would I create that in the Creteria line for that field or do I create a new column? based on Overall Health Field and put that in the creteria line?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Expression not structured as criteria. It could be used to create field in query or used in ControlSource of a textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Could someone explain why this code isnt working please?
    By shabbaranks in forum Programming
    Replies: 7
    Last Post: 10-29-2011, 09:14 AM
  2. TimeDiff calculations inside Query.
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 09-15-2011, 09:27 AM
  3. Filtering and using First() inside a query
    By Gilligan in forum Queries
    Replies: 17
    Last Post: 03-10-2011, 05:52 PM
  4. Replies: 3
    Last Post: 12-20-2010, 09:22 AM
  5. Can access have a table view inside a form
    By mossman in forum Access
    Replies: 5
    Last Post: 01-17-2010, 12:19 PM

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