Only Append Records Needed and Requery a combobox on a change

[nakamichie]

Hello,

I have been working on my free time helping a friend build a tracking data base for some medical tracking of her staff. I have basically wrapped it up but when I thought I was done I realized a couple things that will cause a problem in the future. I have attached a stripped down version of the data base and the table I am trying to work out a solution to and have now spent several hours searchign the internet and trying every possible way to make it work and just can't figure out such a simple solution. The problems I am trying to solve are:

I have the Main form Called T_CrewEditMaster Form and this holds all the basic data on the person (DOB, etc). From this form you open a Form Called TestEntry Input. This form holds all the key Medical examinations that need to be tracked by person to make sure all exams are being done based on the age of the person, gender and if they are a smoker (The Key Categories I care about are the Male/Female Examinations and the Contacts/Glasses Exams). I have figured out how to make the append query append only the records it needs to append based of the above criteria. I now am trying to figure out that if the person now hits a certain age and now needs a couple more tests added and other tests removed that I can now use a script or action query to do this does it without wiping out the information already in there or making duplicate records of the basic tests already listed. I don't care that it deletes tests that have no dates in it as long as all tests for that age group area always there. As I have another form that when they go see a DR or something it will automatically update the test fields if they exist. I have only been able to delete the record and load it a new and cannot for the life of me create an append query that will only append the records that do not exist currently and not touch any records that have dates in it.

Another thing I can't figure out is how to make the "Test Input" Combobox update to the new setting and automatically change the Frequency of test time>. Basically If I am 35 years old then there is 10 tests that I need to do at certain intervals. But Now I am 40 years old and there is now 13 tests I need to do at different intervals. How do I make the frequency of the tests change with the combobox after it has already been set. The only way it currently works is I have to go to the combobox reclick the already selected test (it is already set to this test) and then the frequency changes to it.

Any help on the above would be much appreciated as I am at a serious trial and error and can't work this out.

 

[vbaInet]

Just came across your thread. Have you fixed the issue? I struggled to understand your requirement.

 

[nakamichie]

Hello! no I still can't figure it the &%$#@ out! I am basically trying to figure out how to append a list of Tests that need to be done by Age/Gender and if the tests already exist it just appends the needed ones. I can only figure out how to delete all tests and append new ones and can't figure it out any other way.

Basically I have someone who is 56, Smoker and Female based on the criteria they will have to do a set of 12 tests. But now the person is 56 and there is two more test that needs to be added based on their age increasing. I can't figure out an append query that will just run and add only those tests. I can only create an append query that goes from:

T_Test Schedule is (left or right joined) to TestResults and is then the appropriate tests are appended into T_TestTracking depending on the criteria - but it keeps showing all the tests or none of the tests. I have messed around for several hours and can't seem to grip it.

The data base basically tracks Medical examinations that are required to be done at a certain interval of time and it all depends on age / gender / Smoker and depending on these the tests are different and are added to or subtracted from.

I hope this makes sense. I added an empty data base to show what I am talking about.

The second question was how to update the "frequency" field when the person's age changes by running a re-querry or something but this doesn't do anything. I am only able to change the frequency when age changes by reclicking the drop down bar for "Test" and selecting the same test that is already showing - example:
CURRENTLY INPUT:
AGE: 25
Test: BLOOD TESTS
FREQUENCY: 3 YEARS

The guy is now Age 30 and the Frequency for Blood Tests is now 2 years. Currently I have to go to the "Test" Drop Down box reselect "Blood test" and then frequency will change to "2" years.

I hope all this makes sense. I am pulling my hair out on these two issues.

Thanks for responding!!!!!!!

 

[JHB]

After I read your explanation occurs to me that something is missing but maybe you have it already in the database.
As I see it, there is no follow-up/ check whether the required test is conducted or not.
Likewise, if the tests done is outdated.
The same applies when a person reaches the age where there is dictated more or other tests.
And how do individual get message that now is required new/ other tests?
Now to one of you question:
You can create/run an unmatched query where you, on the one side have all of the tests required for the person and, on the other side, all of the test person has been made.
The link gives a explanation for the unmatched query.
https://support.office.com/en-sg/ar...-records-e64c117e-2022-49a0-adf5-f53f62dbaada
I don't get your question about the "frequency" field , could you show where it is and also what you want.

 

[vbaInet]

Sorry I haven't looked at your db but I'm sure that JHB has raised some good points there.

With regards the unmatched query, there will need to be existing records somewhere for it to work. Let's assume your Tests table has two of these fields as one of its many fields and in this format:

Sequence	PatientID
-------		---------
1		XYZ
2		XYZ
3		XYZ
4		XYZ
5		XYZ
6		XYZ
7		XYZ
8		XYZ
9		XYZ
10		XYZ
11		XYZ		
12		XYZ

You will then have another table with the just one Sequence field (depending on your setup) with values ranging from say 1 to 30... where 30 is the maximum number of tests for any age variation.
Follow the explanation in the link JHB provided and find unmatched records between both tables where:
1. the Sequence field in the table with values 1-30 don't exist in your Tests table
AND
2. the Sequence field in the table with values 1-30 is <= 14 (i.e. 12+2 more tests)
AND
3. the Sequence field in the Tests table IS NULL

View the result, switch the SELECT query to an APPEND query and reference the PatientID control in your form to be used as the PatientID in the two new records.

 

[nakamichie]

Thanks for answering. I am currently reading the article and trying to do the steps on the unmatched query.

On JB's questions. I may have set it up wrong but the program is made for the medical individual to do the tracking and they are the one that gets alerts on tests that are overdue, about to be overdue or not done. Currently I have an input form that the medical Liaison person fills in when they take the individual to the Doctors or to the lab for blood tests, x-rays etc (separate table that tracks with the day to day medical visits or updates). I then have a link on this form that takes them to the "Test Entry Form" and here they (if not done so) load the tests based on their age/gender/smoker. The append query I currently have set up will then load the specific tests relating to the age/gender/smoker. The tests will now be appended to the subform in the Test Entry form the medical staff inputs if the person got these tests or when they do get them by filling in the data in subform. I have a field in the subform that calculates when the next test needs to be done or if it hasn't been done using the a Frequency field. I then have a report that gets created only showing the tests that are overdue or never done.

The query I use to get this data is:

SELECT T_TestTracking.TestID, T_TestTracking.IllnessID, T_TestTracking.CrewID, T_TestTracking.DateTest, [DateTest]+[Frequency] AS DueDate, [DateTest]+[Frequency]-14 AS AdvWarning, IIf(IsNull([DateTest]),"No Exam Recorded",IIf([DateTest]+[Frequency]<=DateValue(Date()),"Overdue",IIf([AdvWarning]<=DateValue(Date()),"Overdue Soon","In PT"))) AS Status
FROM T_TestTracking
WHERE (((T_TestTracking.Frequency) Is Not Null));




I have a Table that only holds the test schedules and I append this into the T_TestTracking Table using a left join to a a Table called TestResults that holds the patientID, Age, Smoker, Gender linked by IllnessID and based on criteria, so my test schedule basically reads like:

TEST AGEGRPS GENDER SMOKER FREQUENCY

Physical 20-29 MALE,FEMALE YES, NO 3 YEARS
Pap Test All Ages FEMALE YES,NO 3 YEARS
Chest Xray All Ages Female, Male Yes 1 year
Bld&Urin 40+ Female, Male Yes, No 3 years

These tests are then appended individually into each persons Test Entry and it only loads the tests based on their age, gender and smoker or not. But it is all or nothing.

Code for append I currently use and couldn't get any other way to work with filtering it,this works fine when first appending tests to a new person but not when the person is now on the lines for years:

INSERT INTO T_TestTracking ( CrewID, ILLNESSID, TESTID, FREQUENCY, TestEntryID )
SELECT TestResults.CrewID, T_TestSchedule.ILLNESSID, T_TestSchedule.TESTID, T_TestSchedule.FREQUENCY, T_TestSchedule.TID
FROM TestResults LEFT JOIN T_TestSchedule ON TestResults.TreatmentID = T_TestSchedule.ILLNESSID
WHERE (((TestResults.CrewID)=[Forms]![TestEntry Form]![CrewID]) AND ((T_TestSchedule.ILLNESSID)=[Forms]![TestEntry Form]![TREATMENTID]) AND ((TestResults.ID)=[Forms]![TestEntry Form]![ID]) AND ((T_TestSchedule.GENDER.Value)=[Forms]![TestEntry Form]![Gender]) AND ((T_TestSchedule.[Age Group].Value)=[Forms]![TestEntry Form]![TxtAge]) AND ((T_TestSchedule.SMOKER.Value)=[Forms]![TestEntry Form]![Smokers]));


Update Query I use for updating the Physical and Blood Test is:

UPDATE Q_TestTrackingExtended INNER JOIN T_CrewList ON Q_TestTrackingExtended.CrewID = T_CrewList.[MLO ID] SET Q_TestTrackingExtended.DateTest = [Forms]![Q_History]![T_TestNotes subform2].[Form]![RptDate], T_CrewList.[Last Physical] = [Forms]![Q_History]![T_TestNotes subform2].[Form]![RptDate], Q_TestTrackingExtended.DoctorID = [Forms]![Q_History]![T_TestNotes subform2].[Form]![CboDR], Q_TestTrackingExtended.Pass = [Forms]![Q_History]![T_TestNotes subform2].[Form]![PassDone], Q_TestTrackingExtended.Result = [Forms]![Q_History]![T_TestNotes subform2].[Form]![Notes], Q_TestTrackingExtended.DateScheduled = [DateScheduled1]
WHERE (((Q_TestTrackingExtended.DateTest) Is Null) AND ((Q_TestTrackingExtended.CrewID)=[Forms]![Q_History]![CrewID]) AND ((Q_TestTrackingExtended.TestID)=4 Or (Q_TestTrackingExtended.TestID)=2)) OR (((Q_TestTrackingExtended.DateTest) Is Not Null) AND ((Q_TestTrackingExtended.CrewID)=[Forms]![Q_History]![CrewID]) AND ((Q_TestTrackingExtended.TestID)=4 Or (Q_TestTrackingExtended.TestID)=2));[/I]


I can send you my stripped down Data Base with the Day to Day input and this linking to the patient Test Entry form. I would have more wanted to be able to have the day to day test entry form be able to append and update the Test Entry form but I could only figure out the updating of tests already loaded.

I have no idea if anything I am saying is making sense or if I have totally over complicated this!!


On the frequency question:

Well as you see a Physical test based on age changes to be done either by 1 year or 2 years or 3 years. So if a person is 39 and now they are 40 the Physical exam now needs to be done every 2 years instead of 3 years for them. I currently have the Frequency field in the Test Entry subform programmed to pull the data from the TestID combo box on an after update event with:

Me.Frequency = Me.TestID.Column(3)

But when their age now hits 40 The frequency doesn't requery the TestID combobox and I instead have to go to the testID combobox click on physical and then the Frequency changes to 2 years from 3 years. I have tried using other events in the subform or main form but can't get it to do this requery.

 

[vbaInet]

That's a rather lengthy explanation nakamichie

One thing after the other, let's get the unmatched query and updates working then we can look into your other problems.

 

[nakamichie]

Okay i have uploaded some photo that might help on seeing what I am working with.

 

[nakamichie]

Okay I feel like a complete idiot here! I have followed the link JHB gave and it makes sense but it returns no records on unmatched or records of categories I don't care for. So must be doing something wrong.

SELECT T_TestSchedule.ILLNESSID, T_TestSchedule.TESTID, T_TestSchedule.FREQUENCY, T_TestSchedule.GENDER.Value, T_TestSchedule.[Age Group].Value, T_TestSchedule.SMOKER.Value
FROM T_TestTracking LEFT JOIN T_TestSchedule ON T_TestTracking.TestID = T_TestSchedule.TESTID
WHERE (((T_TestTracking.TestID) Is Null));

On your helpful tip I couldn't figure this one out and as my Test Schedule table is not in sequence like you laid it out.

T_TestTracking has related fields of CrewID IllnessID and TestID.
T_TestSchedule has related fields of IllnessID and TestID. Meaning TestID.

The illnessID category I really care about is 1 (Female/Male Exams) and (Contacts/Glasses Exam) as these are main key tests for all.

But I have just spent the last hour working with the above examples and the link and I am starting feel like a dumbo the elephant. Cause it can't be this hard and maybe I put my tables wrong or something or need to create a new table to do this.

I appreciate your help and I am sorry for my long winded responses

 

[vbaInet]

So have a think, you want to view those test types that haven't been allocated to the crews... the fields that would contain data based on the criteria would be from which table? Drop some fields from your other table.

 

[nakamichie]

Okay I am sorry, I did what you suggested and I only get all tests based on criteria or no tests based on criteria. I changed around the joins, I added fields in all the ways the things says to do it. I can't figure this out and maybe it is not possible with the tables I am using. I saw how it works by using the northwind DB example and that worked but for some reason this is not working and don't know if it is because I have multiselect boxes or something. I am at a loss and maybe will just figure something out different on this.

 

[vbaInet]

How are you getting on with this?

Upload a cut down version of your db so that I can see what you've done, then I can advise.

 

[nakamichie]

Not so good. I haven't figured it out and have just left it with what I have. I can upload what I have as soon as am back at computer with program. I appreciate the help!

 

[vbaInet]

No problem!