Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,
    I have a database for collecting data for a cardiac procedure. Once the procedure is done, the patient gets a follow-up phone call in 90 days, 180 days, and 360 days. It sounds simple enough.

    I have 2 problems with it:
    1. I need a date range report that lists all patients that need a follow-up call during a particular month. The report will be run monthly to see who falls within the 90Day, 180Day or 360Day. For example: which people have follow-up dates in May for those who fall in the 90, 180 or 360 day criteria.

    The follow-up dates are calculated from a [ProcedureDate] in a query. The Reports menu has Date Range text box controls. I use this a lot and am familiar with it. But, it does not seem to recognize that the calculation is a date? I do not get any results in my query when I use “Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate]” in the query criteria. I tried defining a parameter in the query for the 90Day and set it to a date/time – but then I get a parameter box asking for 90Day.

    2. Even without the date problem above, when I create this report above - I only get patients that already had follow-up one or two follow-ups. I do not get any patients that need follow-up. I need both. The ones that have had follow-up – I need the follow-up details so the follow-up caller has the info in front of them as they are calling.
    It is a simple relationship – tblPatients (one) – tblFollowup (many). I would like to add tblDrug to it as well but I can’t get it to work the way I want to yet with only the two. It would be a tblFollow-up (one) – tblDrug (many). I tried changing the relationship join properties in the query – but I only get the patients with follow-up already – or I get an error “no current record”.

    Any words of wisdom for me?
    I have attached a stripped down version.
    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='spider' post='774116' date='08-May-2009 02:57']Hi all,
    I have a database for collecting data for a cardiac procedure. Once the procedure is done, the patient gets a follow-up phone call in 90 days, 180 days, and 360 days. It sounds simple enough.

    I have 2 problems with it:
    1. I need a date range report that lists all patients that need a follow-up call during a particular month. The report will be run monthly to see who falls within the 90Day, 180Day or 360Day. For example: which people have follow-up dates in May for those who fall in the 90, 180 or 360 day criteria.

    The follow-up dates are calculated from a [ProcedureDate] in a query. The Reports menu has Date Range text box controls. I use this a lot and am familiar with it. But, it does not seem to recognize that the calculation is a date? I do not get any results in my query when I use "Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate]" in the query criteria. I tried defining a parameter in the query for the 90Day and set it to a date/time – but then I get a parameter box asking for 90Day.

    2. Even without the date problem above, when I create this report above - I only get patients that already had follow-up one or two follow-ups. I do not get any patients that need follow-up. I need both. The ones that have had follow-up – I need the follow-up details so the follow-up caller has the info in front of them as they are calling.
    It is a simple relationship – tblPatients (one) – tblFollowup (many). I would like to add tblDrug to it as well but I can't get it to work the way I want to yet with only the two. It would be a tblFollow-up (one) – tblDrug (many). I tried changing the relationship join properties in the query – but I only get the patients with follow-up already – or I get an error "no current record".

    Any words of wisdom for me?
    I have attached a stripped down version.[/quote]

    The database that you posted does not include the query qryAllPtsFU2 which is the recordsource of the report so it is hard to test this.
    I would expect that if you format the two date text boxes on frmDateRange in a Date format ( short date or mm/dd/yy) the first problem would be solved.

    To look at the 2nd problem I would need to see the query.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774213' date='07-May-2009 22:58']The database that you posted does not include the query qryAllPtsFU2 which is the recordsource of the report so it is hard to test this.
    I would expect that if you format the two date text boxes on frmDateRange in a Date format ( short date or mm/dd/yy) the first problem would be solved.

    To look at the 2nd problem I would need to see the query.[/quote]

    Sorry for the confusion, I did not have a report yet - (I stated that wrong) becauce I cannot get the query to work.
    I formatted the date like you said and it is now working. Thank you.

    When I run the query - I only get patients that already had follow-up one or two follow-ups. I do not get any patients that need follow-up that have had no previous follow-up. I need both. The ones that have had follow-up.
    Thaks,
    Vicky

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='spider' post='775043' date='14-May-2009 03:13']When I run the query - I only get patients that already had follow-up one or two follow-ups. I do not get any patients that need follow-up that have had no previous follow-up. I need both. The ones that have had follow-up.
    Thaks,
    Vicky[/quote]
    If you want help with the query that does not work properly, I need to see your attempt.

    How do records in the follow up table get created? Also I don't understand the structure. Do patients have multiple records in the Follow up table? It looks like it, but why then are there 3 yes/no fields Day-90, Day-180 and Day-360, none of which are ticked?
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='775306' date='15-May-2009 00:04']If you want help with the query that does not work properly, I need to see your attempt.

    How do records in the follow up table get created? Also I don't understand the structure. Do patients have multiple records in the Follow up table? It looks like it, but why then are there 3 yes/no fields Day-90, Day-180 and Day-360, none of which are ticked?[/quote]

    The query is called qryEPPtsForFU in the attachment.

    The records in the follow up table are created by entering data in a form. Yes, patients have multiple follow-up records in the Follow-up table.

    The 90, 180 and 360 yes/no fields are to be ticked while the user is making a follow-up call to the patient. It documents which follow up is being done - 90, 180 or 360 day. They are to answer the question "which follow up are you completing?"

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Instead of having three separate yes/no fields use a single field with three options: 90day, 180Day or 360Day (or whatever alternatives names you prefer.) Using three separate fields allows for data entry errors.

    I did not recognise the query qryEPPtsForFU as the right one as it does not refer to the form.

    In the query
    • remove the totals line from the query grid in which you have Group By for each field. This is not a Totals query.
    • In the Criteria line for each of the three follow up dates put this expression:
      Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate]
    • Put the three copies of the previous expression on different lines
    • Repeat the No for epClosed on all 3 lines.
    • The join between the two tables needs to be: "Included all records from tblEPPatient and recrods from tblfollowUp where the join fields are equal". I think you have this already but I mention this in case I changed this.
    [attachment=83773:epFollowUp.gif]
    Attached Images Attached Images
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='775419' date='15-May-2009 22:46']Instead of having three separate yes/no fields use a single field with three options: 90day, 180Day or 360Day (or whatever alternatives names you prefer.) Using three separate fields allows for data entry errors.

    I did not recognise the query qryEPPtsForFU as the right one as it does not refer to the form.

    In the query
    • remove the totals line from the query grid in which you have Group By for each field. This is not a Totals query.
    • In the Criteria line for each of the three follow up dates put this expression:
      Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate]
    • Put the three copies of the previous expression on different lines
    • Repeat the No for epClosed on all 3 lines.
    • The join between the two tables needs to be: "Included all records from tblEPPatient and recrods from tblfollowUp where the join fields are equal". I think you have this already but I mention this in case I changed this.
    [attachment=83773:epFollowUp.gif][/quote]


    Thank you so much for you help!
    Vicky

Posting Permissions

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