none
Looking for Input on best way to create cancelation option

    Question

  • I apoligize for the vauge question, but I am looking for some insight to the best way I can accomplish my goal. I'll see if I can explain this properly.

    I have one main form (Flights) with a continuous subform (Patient) and many reports, queries etc. dependant on them. The two tables are linked by FlightID

    It has been requested that I add a cancelation function since we can't delete any records from the database. I would like to give the user the option to check canceled, then CANCELED will pop up on the form. (Easy enough). As well, the data from that record will no longer be used in any calculations / queries / reports. I have been able to accomplish this for the values from main form, but have not been able to do this for the values from the subform. I have tried the following:

    -Referencing the main form in the DLookup criteria for calculations involving the subform (recieved a message that the form could not be located)

    -Added a field to the table of the subform for canceled, and Set an invisible check box on the subform with VBA to set as true on FormCurrent and when main form's checkbox is clicked (this would only set the checkbox of one record of the subform, so if I had two only one was marked as canceled. Also, it was not changing the canceled value in the table of the subform)

    I know that I have made some errors on these, but I thought before I get too far into any one method it might be best to get some insight on what the best way to do this is.

    If anyone has any ideas please let me know!

    Katie

    Wednesday, July 03, 2013 10:49 PM

Answers

  • Yes, It would be much more efficient to use a query.

    Air code, but base your report on queries something like:
    Flight info-

    Select sum(IIF(Authorization IS NULL),1,0) as AuthsNull, Sum(IIF(FlightNbr is Null,1,0) as FlightNbrNulls
    FROM Flight_Info
    Where Flight_Info.Cancelled = False

    You could do something similar with the patient table, depending on how you want it grouped.
    If you want it by flight:

    Select Flight_Info.FlightID, Sum(IIF(Patient_Info.PatientName IS NULL,1,0) AS PatientNameNulls,
    Sum(IIF(Patient_Info.PatientPhone IS NULL,1,0) AS PatientPhoneNulls
    FROM Flight_Info INNER JOIN Patient_Info on Flight_Info.FlightID = Patient_Info.FlightID
    Where Flight_Info.Cancelled = False
    GROUP BY Flight_Info.FlightID

    If you don't want it by flight:

    Select Sum(IIF(Patient_Info.PatientName IS NULL,1,0) AS PatientNameNulls,
    Sum(IIF(Patient_Info.PatientPhone IS NULL,1,0) AS PatientPhoneNulls
    FROM Flight_Info INNER JOIN Patient_Info on Flight_Info.FlightID = Patient_Info.FlightID
    Where Flight_Info.Cancelled = False

    Of course substitute your correct field names.

    Thursday, July 04, 2013 4:04 PM

All replies

  • Is 'Cancelled' supposed to apply to 'Flights' or 'Patients'?
    Thursday, July 04, 2013 12:46 AM
  • Personally, I think the Cancelled flag in the One/Parent Table (tblFlight) is enough since if the Flight is cancelled, then there are no actual Patients on the Flight!

    The problem with having the Cancelled flag on both the Flight record and the Flight_Patient records is that you can end up with inconsistencies in the data-set which can give incorrect results in subsequent processing.  For example, you may have a cancelled Flight but somehow the Flight_Patient records are not flagged as cancelled. If subsequent processing relies on the Cancelled flag values in Flight_Patient records, the processing will give incorrect results.

    It is actually extremely hard to keep the data consistent if duplicated/derived values are stored, especially if the consistency processing is done by code in the Front-End.  Thus, it is better not to store duplicated/derived data, i.e. in this case, rely solely only the single Cancelled flag on the Flight record rather than having this flag on both the Flight record and related Flight_Patient records.


    Van Dinh





    • Edited by Van DinhMVP Thursday, July 04, 2013 1:14 AM Typos
    Thursday, July 04, 2013 12:47 AM
  • It is actually extremely hard to keep the data consistent if duplicated/derived values are stored, especially if the consistency processing is done by code in the Front-End.  Thus, it is better not to store duplicated/derived data, i.e. in this case, rely solely only the single Cancelled flag on the Flight record rather than having this flag on both the Flight record and related Flight_Patient records.

    This does not account for the - in my opinion - more common situation - that the Flight is NOT cancelled, but one or more Patients cancelled their flight.

    I think you need both flags in Flight and Flight-Patient records, with the implicit "AfterUpdate" relation that if a Flight is cancelled, all Flight-Patients are cancelled too.

    Depending on the information you want to retrieve, you may want to distinguish whether a Flight-Patient is cancelled by the Patient himself, by external circumstances (Flight cancel) or both.

    Imb.

     

    Thursday, July 04, 2013 7:37 AM
  • I agree that the canceled flag in only the flights form should be sufficient (In our case the patient cannot cancel the flight with the flight continuing to go, and we do not require information about why flight was canceled in this database)

    The only problem this leaves me with is a report I generate with total number of records by each form (Flight/Patient) and total number of null records by each field of each form. This works fine for the FLight form using Dcount with criteria specific to the field, then additional Is not Canceled criteria, but I can't get it to work for my patient form.

    Would it work to have a Dcount for patient form with criteria specific to the field AND Flight ID Patient = FlightID Flight AND Flight is not canceled?

    Or is there a better way to do this?

    Katie

    Thursday, July 04, 2013 3:23 PM
  • Your data is stored in tables, not forms.

    Post the SQL of the query that you are using for the report, as well as  the dcounts and where you are using the dcounts.

    Also, what do you mean by number of null records by each field. A record cannot be null. Only the fields in the record can be null. Records with all fields null should not be allowed in your table, as they are of no use.

    Thursday, July 04, 2013 3:36 PM
  • Thanks for your reply, I'm afraid my terminology was definitely a little off when describing my situation

    I am using a form to display a count of the number of time each field is null. It is seperated into a Flight and Patient section. I am using the code similar to below for each field off the flight table.

    Dim Auth As Integer
    Auth = DCount("*", "Flight_Info", "IsNull(Authorization)AND [Canceled] = 0")
    Me.Auth = Auth

    I am just not sure how to write this for fields of the patient table.

    Your comment made me realize that I should change this to a report instead as it will be much easier to use a query to get this information.

    Thursday, July 04, 2013 3:43 PM
  • Yes, It would be much more efficient to use a query.

    Air code, but base your report on queries something like:
    Flight info-

    Select sum(IIF(Authorization IS NULL),1,0) as AuthsNull, Sum(IIF(FlightNbr is Null,1,0) as FlightNbrNulls
    FROM Flight_Info
    Where Flight_Info.Cancelled = False

    You could do something similar with the patient table, depending on how you want it grouped.
    If you want it by flight:

    Select Flight_Info.FlightID, Sum(IIF(Patient_Info.PatientName IS NULL,1,0) AS PatientNameNulls,
    Sum(IIF(Patient_Info.PatientPhone IS NULL,1,0) AS PatientPhoneNulls
    FROM Flight_Info INNER JOIN Patient_Info on Flight_Info.FlightID = Patient_Info.FlightID
    Where Flight_Info.Cancelled = False
    GROUP BY Flight_Info.FlightID

    If you don't want it by flight:

    Select Sum(IIF(Patient_Info.PatientName IS NULL,1,0) AS PatientNameNulls,
    Sum(IIF(Patient_Info.PatientPhone IS NULL,1,0) AS PatientPhoneNulls
    FROM Flight_Info INNER JOIN Patient_Info on Flight_Info.FlightID = Patient_Info.FlightID
    Where Flight_Info.Cancelled = False

    Of course substitute your correct field names.

    Thursday, July 04, 2013 4:04 PM
  • Thank you very much for your suggestions! Your prespective was just what I needed to save me some wasted time going down the wrong direction on this. Thanks!
    Thursday, July 04, 2013 7:24 PM
  • This does not account for the - in my opinion - more common situation - that the Flight is NOT cancelled, but one or more Patients cancelled their flight.

    Hi Imb

    No doubt that is true in most cases but the question is definitely setting the Cancelled flag for the related Flight_Patient records in conjunction with setting the Cancelled flag of the Flight which is certainly incorrect.

    In addition, Katie also mentioned that patients cannot cancel later...

      


    Van Dinh




    • Edited by Van DinhMVP Friday, July 05, 2013 9:17 AM Typos
    Thursday, July 04, 2013 11:42 PM
  • No doubt that is true im most cases but the question is definitely setting the Cancelled flag for the related Flight_Patient records in conjunction with setting the Cancelled flag of the Flight which is certainly incorrect.

    In addition, Katie also mentioned that patients cannot cancel later...

    Hi Van,

    Yes, sure. It became clear to me after Katie's answer.

    Imb.

     

    Friday, July 05, 2013 7:14 AM