fill field with data from another table by lookup

[tfxobrien]

I have a parameter query using date between to select records within that date from Table1. That works fine.
But there are 3 fields that need to lookup into another table and display the second field from that table rather than the original query field
EXample;
Table1 Name DOB, date, C1(number),C2(number),C3(number)
Table2 Price, Service
use the value in table1.C1 to match the value in table2 but then display Table2.Service rather than the number.
C1,C2,C3 could be 0 and then it should not dispaly any service.
I've played with it for a few days without any luck.
BTW: This is not my db, I'm trying to help a friend.
Thanks

[CJ_London]

rather vague description, but sounds like a) your data in table1 is not normalised and b) you would need to link using a left join between C1 and price in table2, c2 to price in table2 and c3 to price in table2 (3 joins, 2 aliases of table2, just drag them onto the query grid)

[Bob Fitz]

use the value in table1.C1 to match the value in table2 ....

which field

Can you post a copy of the db

[CJ_London]

which field

according to the OP, there are only 2 - price and service. And since he wants to bring back service, it has to be price

[tfxobrien]

Sorry for being vague.Yes, the data is not normalized. It is a medical db written in 2005 with Access 2003.
Table1 is the Patient record and Table2 is the services with the price
I understand your idea.
For some reason the creator used the price of the service to be the link between table1 and table2.
The c1,c2,c3 values are the price of the service and are stored in table1.
When c1,c2,c3 has a value other than 0 I need to lookup for that price in table2 and display the table2.service as a field in the table1 query.
How would I create the 3 left joins? Would I do that on table1 previous to the query or on the query?

[CJ_London]

on the query - to create a left join, make a 'normal' join then right click and select the option something like 'select all records from table1 and those records from table2 where they exist'

[tfxobrien]

Table1.ziptable2.zip
In table1 the field Colonoscopy is C1, Colonoscopy2 is C2, etc.
As you will see the price is 805 in table1.
I would like the query to show the Service from table2 which is "G0121 Colon SCR".
Thanks

[CJ_London]

what you have provided is the equivalent of saying - my car is not working, here is a picture of my car.

there is no table 1, and please provide your db if you want us to make suggestions - in a copy of your db, remove all unrequired tables, queries, forms reports, macros and modules. Then in the remaining tables remove or modify any confidential data.

Finally compact and repair the copy db, zip it and upload it

[tfxobrien]

Here is the mdb.
I followed your directions to create it.Patientdb.zip

[CJ_London]

copy and paste this code into a new query

SELECT table1.[Procedure ID], table1.[Patient ID], table1.Hospital, table1.[In/Out Patient], table1.[Procedure 1], table1.[Procedure 2], table1.SubProc, table1.SubPro2, table1.SubPro3, table1.SubPro4, table1.SubPro5, table1.SubPro6, table1.SubPro7, table1.SubPro8, table1.Comment, table1.Diagnosis, table1.[Diagnosis 2], table1.[Diagnosis 3], table1.[Diagnosis 4], table1.Date, table1.Colonoscopy, table1.EGD, table1.ERCP, table1.[Small Bowel], table1.MISC, table1.Colonoscopy2, table1.Colonoscopy3, table1.EGD2, table1.EGD3, table1.ERCP2, table1.ERCP3, table1.[Small Bowel2], table1.[Small Bowel3], table1.MISC2, table1.MISC3, Colonoscopy.Service, Colonoscopy_1.Service, Colonoscopy_2.Service
FROM ((table1 LEFT JOIN Colonoscopy ON table1.Colonoscopy = Colonoscopy.Price) LEFT JOIN Colonoscopy AS Colonoscopy_1 ON table1.Colonoscopy2 = Colonoscopy_1.Price) LEFT JOIN Colonoscopy AS Colonoscopy_2 ON table1.Colonoscopy3 = Colonoscopy_2.Price
WHERE (((table1.Date) Between [start] And [end]));

You also need to correct the datatype for the Colonoscopy field - it is type text and should be type number (long integer).

Other things:

1. using non alpha numeric characters in field names is a bad idea (In/Out Patient). , it is prone to creating misleading errors
2. using reserved words such as Date is also a bad idea - Date is a function that returns today as a date , it is prone to creating misleading errors
3. although you can have spaces in field names, it is prone to creating misleading errors
4. You really should look at normalising your data - Procedures, SubProc, Diagnosis, Colonoscopy, Small Bowel, Misc are all indicative of a poorly designed database which requires much more complex queries, form and report design etc, perhaps so complex they cannot even be created.

[tfxobrien]

I want to thank you for your help. Unfortunately, it is not my db. I was asked to see if I could generate some specific reports for a doctor friend. His son wrote the system in 2004 with Access 2003 and is long gone. What was most helpful were your 4 comments. I live in a linux (Centos) world and now will probably make csvs of all the tables and bring them over to my system.
Also, unfortunately, the code above gave type mismatch errors. I tried to make repairs but no luck. Fields such as Colonoscopy_1.Price, I think, should be Colonoscopy1.Price.
At any rate, I'm still interested in knowing how to present the Service field on the query for at least one simple instance to understand how to present a field from another table in that manner, if you are up for that?
I did a lot of seaching on the web prior to putting the request on the forum. I found many discussions of creating a calculated field but none that match my needs which were creating a field and getting the data form another table based on a lookup. I'm sure I'm not using the proper Access lingo here. Even if you could point me to a link that shows this idea. It certainly does not have to be the tables I sent. I realize that it would be automatic if the tables were linked.
Thanks again for your time. I will pay it forward in my world.

[CJ_London]

the code above gave type mismatch errors

because you haven't fixed this

You also need to correct the datatype for the Colonoscopy field - it is type text and should be type number (long integer).

Fields such as Colonoscopy_1.Price, I think, should be Colonoscopy1.Price.

No, Colonoscopy_1 is an alias for the Colonoscopy table - because you have to refer to it three times for your three fields. Having copy and pasted the code I provided into the query sql window you can go back to the query view to see 'what it looks like'

[CJ_London]

if you can't change the field type for the Colonoscopy field type then this will work - just copy and paste it since it is based on your original query

SELECT table1.[Procedure ID], table1.[Patient ID], table1.Hospital, table1.[In/Out Patient], table1.[Procedure 1], table1.[Procedure 2], table1.SubProc, table1.SubPro2, table1.SubPro3, table1.SubPro4, table1.SubPro5, table1.SubPro6, table1.SubPro7, table1.SubPro8, table1.Comment, table1.Diagnosis, table1.[Diagnosis 2], table1.[Diagnosis 3], table1.[Diagnosis 4], table1.Date, table1.Colonoscopy, table1.EGD, table1.ERCP, table1.[Small Bowel], table1.MISC, table1.Colonoscopy2, table1.Colonoscopy3, table1.EGD2, table1.EGD3, table1.ERCP2, table1.ERCP3, table1.[Small Bowel2], table1.[Small Bowel3], table1.MISC2, table1.MISC3
FROM ((table1 LEFT JOIN Colonoscopy ON val(table1.Colonoscopy) = Colonoscopy.Price) LEFT JOIN Colonoscopy AS Colonoscopy_1 ON table1.Colonoscopy2 = Colonoscopy_1.Price) LEFT JOIN Colonoscopy AS Colonoscopy_2 ON table1.Colonoscopy3 = Colonoscopy_2.Price
WHERE (((table1.Date) Between [start] And [end]));

The change is in red. Note that as a result of this change, the query cannot be viewed in the query builder

[tfxobrien]

Changed the Colonoscopy field type from text to Number and it worked.
Thanks for being so patient.

[CJ_London]

no problem - you can see the query view to see the visualisation?