Finding a field value based on a value in another table

[winston01]

Thank you in advance for your help.

I have a query that displays positive or negative in a field based on a calculation. There are four fields that display positive or negative. They are all attached to the same record number.

example:
record Value
123 Positive
123 Negative
123 Negative
123 Negative

In table 2, I need one field to show positive if any of the five fields in the above table display "Positive". The Record 123 field in table 2 should display "Positive".

Can this be done?

Thank you!

 

[JerryKlmns]

Assuming that your Table2 has that RecID field you have in your query

SELECT Table2.RecID, 'Positive'
FROM Table2 Left Join Query1 As Q On
Table2.RecID = Q.RecID
WHERE Q.Field2 & Q.Field3 & Q.Field4 & Q.Field5 Like "*Positive*"

 

[lespaul]

Do you mean you want to UPDATE the field in table2 based on the results in the query you show?

Why would you want to store that information when you can just get it in a query on the fly as shown by Jerry?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[winston01]

Thank you for you time.

The field results are actually malignant or benign. You have 5 test that show a result. I have these is a query like you(Leslie) told me so that each choice is an individual record tied to the same reportid.

ID Code Def Result
123 AB XXXX Benign
123 CC XXXX Malignant
123 CF XXXX Benign
123 LH XXXX Benign

You can choose up to five codes but do not always need all five. There is a field on the pathology report that gives the ended result based on the possible 5 records. This is the field used for all reports. If any of the 5 records show malignant, this field should say malignant. IF not, the field should state "Benign". The doctor wants this to pop up automatically once the codes are put into the query without the tech being responsbile for viewing the codes and making the determination themselves. Go figure!

Help, I am not getting any results.

Thank you.

 

[lespaul]

well, good, I'm glad you set it up this way!

Now, you said that

There is a field on the pathology report that gives the ended result based on the possible 5 records.

So, you are writing a query that is the source for the report? And you need a particular field to say either "malignant" or "benign" based on if ANY of the records in the PatientDiagnosisExam table Malignant in the Result field?

Is that right? So we're really not dealing with updating another table, you just need a single result on the pathology report?

 

[winston01]

Correct.

The field will live in a query of course, but will be used to display on a form and on reports. The main issue is having one place that determines an ending result (One field) of malignant or benign based on the 5 chosen codes on the exam table.

Thank you.

 

[lespaul]

What information do you need on the report? Do you need all the diagnosis codes as well as the overall benign or malignant finding?

 

[winston01]

Actually, I have not worked on the report. I am just trying to get the field to display this information on my form. I will use this field for reports later, but right now I just need to find the right code to pull the information correctly.

Thank you for your time.

 

[PHV]

What is the actual SQL code of the RecordSource property of your form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[winston01]

I do not have anything. I don't know where to start.

I have up to five records in a query, linked to the table, which show the different results per code chosen.

I have a result field in the table which should represent malignant if any of the five codes were malignant and benign if all codes were benign.

I was thinking i needed to build a query and add a field. Where would I find the appropriate property on the field in the table and what code would I use?

Thank you for your help.

 

[JerryKlmns]

winston01

Your last post says : Store a calculated field in a table.
Breaking normalization rules?
Use the query I've posted, and use it as the RecordSource property of your form and don't store that value in the table, since it could change any time.

 

[winston01]

I really appreciate your help. I have created a field on the form and put the code in the record source of the form, but I am not getting a result. There is not a field 1,2,3,4,5. Each code is in a seperate record in a query looking at table 2 attached to the the recordid on the table 1. Based on the code, Malignant or Benign appears in another field on each record in table 2. So we are looking at a field on five seperate records in table 2 to determine if the field on the form looking at table 1 will be "Malignant" or "Benign". With the above code, isn't it looking for 5 fields in one record? How does it know to place benign if none of the records show "Malignant" in the type field?

I am obviously not very good when it comes to sql language. I appreciate anything else you can give me to point me in the right direction.

Thank you.

 

[winston01]

Here is a code I am working on for the field on the form:

SELECT Form!pathologyreport![result]
WHERE IIF(Forms!pathologyreport!pathologyexamcodesqry subform.Form![type]="malignant",Form!pathologyreport![result]="malignant",Form!pathologyreport![result]="Benign")

Am I anywhere close to the right thought process?