Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I am trying to create a query that will allow me to find the latest entry into a table, in this case a prescription refill, but only if the last refill was not done within a certain time period. In the attached file's Sutent Alert query, I want to find the patients who have not had their med refilled in the last 42 days. I'm thinking I need to use the DMax function to find the lastest entry, but I'm having trouble setting up the query to make it the lastest, but not to show it if within 42 days. In the current database, patient "7" should appear on the list, but "17" not.

    Anyone have any suggestions?
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='kyhawkeye' post='792179' date='05-Sep-2009 07:01']I am trying to create a query that will allow me to find the latest entry into a table, in this case a prescription refill, but only if the last refill was not done within a certain time period. In the attached file's Sutent Alert query, I want to find the patients who have not had their med refilled in the last 42 days. I'm thinking I need to use the DMax function to find the lastest entry, but I'm having trouble setting up the query to make it the lastest, but not to show it if within 42 days. In the current database, patient "7" should appear on the list, but "17" not.[/quote]

    In general these things are handled by building multiple queries. So first build a query that retrieves records where the date filled is within he last 42 days, then base the second query on that one. Find the max date for for each person.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='kyhawkeye' post='792179' date='04-Sep-2009 13:01']I am trying to create a query that will allow me to find the latest entry into a table, in this case a prescription refill, but only if the last refill was not done within a certain time period. In the attached file's Sutent Alert query, I want to find the patients who have not had their med refilled in the last 42 days. I'm thinking I need to use the DMax function to find the lastest entry, but I'm having trouble setting up the query to make it the lastest, but not to show it if within 42 days. In the current database, patient "7" should appear on the list, but "17" not.

    Anyone have any suggestions?[/quote]
    In such situations, you do want the most recent prescription for each patient, and the DMax function will do that. The trick is to do another query on that then and find the records where it has been more than 42 days. This sort of thing usually requires a query on a query. So take the date Criteria <Date()-41 off from the Filled field in your Stutent alert query and use the DMax function on the Filled Field, and then do another query on that query giving you only the records where the DMax of Filled is 42 or more days in the past.
    Wendell

  4. #4
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='792208' date='04-Sep-2009 19:12']In such situations, you do want the most recent prescription for each patient, and the DMax function will do that. The trick is to do another query on that then and find the recors where it has been more than 42 days. This sort of thing usually requires a query on a query. So take the date Criteria <Date()-41 off from the Filled field in your Stutent alert query and use the DMax function on the Filled Field, and then do another query on that query giving you only the records where the DMax of Filled is 42 or more days in the past.[/quote]


    I took your advice and tried it, and it seems to be working...except that the initial query (DMax ('Filled", Rxns") is not showing the DMax for all the sunitinib patients, just the 2 with the most recent fills. The second query seem to be doing fine, for it does not show those two patients, since they have fill dates for today.

    Here is a screen capture of the query. Any reason why it would exclude the other two patients? Will it not show a DMax if there is only one entry for that field?
    Attached Images Attached Images

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='kyhawkeye' post='792220' date='05-Sep-2009 11:48']I took your advice and tried it, and it seems to be working...except that the initial query (DMax ('Filled", Rxns") is not showing the DMax for all the sunitinib patients, just the 2 with the most recent fills. The second query seem to be doing fine, for it does not show those two patients, since they have fill dates for today.

    Here is a screen capture of the query. Any reason why it would exclude the other two patients? Will it not show a DMax if there is only one entry for that field?[/quote]
    I think you are confused by this.
    Check out your database i have changed, in particular look at the qMax query which gets the last filled med for each patient.
    Then check qSutent alert query wuich uses the qMax query as a check in the last 42 days.

    This is what both John and Wendell were referring to, although rather than a DMax function i used a query instead.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've attached a modified version of your database. One of the powers of Access is to be able to use a query as a source of data for another query. That is what John was alluding to as well in his response. So I created a new query called qryLastScriptFilled, which gives the most recent Rx record for each patient and drug. I then used that in a modified version of your query Sutent alert, and called it Sutent alertV2 to get the data you are looking for - and I threw in a calculated field that shows the number of days it has been. Which shows that 17 has gone 45 days today.

    A couple of other general comments about your design. You are using a Lookup Table in the table design of table Rxns - that is generally not a good practice for two reasons. First there is a good deal of overhead in doing that, and second, it is confusing as other people don't realize that is going on and try to do joins when they shouldn't. In addition, you should probably come up with a standard naming convention and stick to it. Do a web search for database naming concentions and you should find several. There is no one right choice - rather adopting one and using it is the most important issue. For example, in your Rxns table you have a field called tblDrug - tbl is commonly used as a prefix to table names so they can be distinguished from queries (and other objects). Also note that in your original query Sutent alert you have a join line between ID in Rxns and ID in Chemo - that is one reason you were getting odd results.
    Attached Files Attached Files
    Wendell

  7. #7
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt' post='792225 View Post
    I've attached a modified version of your database. One of the powers of Access is to be able to use a query as a source of data for another query. That is what John was alluding to as well in his response. So I created a new query called qryLastScriptFilled, which gives the most recent Rx record for each patient and drug. I then used that in a modified version of your query Sutent alert, and called it Sutent alertV2 to get the data you are looking for - and I threw in a calculated field that shows the number of days it has been. Which shows that 17 has gone 45 days today.

    A couple of other general comments about your design. You are using a Lookup Table in the table design of table Rxns - that is generally not a good practice for two reasons. First there is a good deal of overhead in doing that, and second, it is confusing as other people don't realize that is going on and try to do joins when they shouldn't. In addition, you should probably come up with a standard naming convention and stick to it. Do a web search for database naming concentions and you should find several. There is no one right choice - rather adopting one and using it is the most important issue. For example, in your Rxns table you have a field called tblDrug - tbl is commonly used as a prefix to table names so they can be distinguished from queries (and other objects). Also note that in your original query Sutent alert you have a join line between ID in Rxns and ID in Chemo - that is one reason you were getting odd results.
    For some reason, when I looked at yours, the Sutent alertV2 still gave me a listing for patient "17", which should not happen, since it was refilled on 9/4/2009. Not sure why, but then again, I'm still quite the rookie when it comes to these things.

    I'll look over the chart in the back of my book (see above) and try to rename things to make them clearer. I don't know how I got that join to occur, for that was not one I remember intentionally creating.

    What, in your opinion would be the better way to design this database? I'm always willing to learn from those more skilled than I. Anything you can think of that will save me headaches later would be respectfully applied.

    Thank you both very much for your time and assistance, for it is highly appreciated! I and my VA patients thank you!

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='kyhawkeye' post='792228' date='05-Sep-2009 14:29']Confused? What was the first clue? LOL I'm trying to learn by doing and using a copy of the Access 2003 Bible, which has helped me get started but seems to not have all the info I must need. Either that or I'm trying to create more than I can chew...

    I think I follow the logic behind your set-up of the queries. How do I get the 'Total' line to show up on the grid that I build the query on? It is not showing up by default on my PC (Access 2003). When I look at the qSutent query, it gives me the correct final result. I can now build my weekly reports from there.



    For some reason, when I looked at yours, the Sutent alertV2 still gave me a listing for patient "17", which should not happen, since it was refilled on 9/4/2009. Not sure why, but then again, I'm still quite the rookie when it comes to these things.

    I'll look over the chart in the back of my book (see above) and try to rename things to make them clearer. I don't know how I got that join to occur, for that was not one I remember intentionally creating.

    What, in your opinion would be the better way to design this database? I'm always willing to learn from those more skilled than I. Anything you can think of that will save me headaches later would be respectfully applied.

    Thank you both very much for your time and assistance, for it is highly appreciated! I and my VA patients thank you![/quote]
    Wendell's will work if you take the ID column out of the query qryLastScriptFille.

  9. #9
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='792229' date='05-Sep-2009 01:11']Wendell's will work if you take the ID column out of the query qryLastScriptFille.[/quote]


    I used stuff from both yours and Wendell's and now have it working the way I was aiming for, thanks to the help of both of you. Again, it has been appreciated!

Posting Permissions

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