Link Table records with antoher table collumns

[TONYWALKER]

First of all thank you all for helping me to solve my fisrt question.


Trying to desing a labtest result database, i made the above thinking (picture),sure not standard db structure.
-I wonder if there is a way to link records from one table to collumns to another tables.
For each test there is a record to tbltests, and each test represents a collumn to tblresults and tbltest_select.(see blue arrows)
Finaly each test collumn at tblresults refers to a value true or false to tbltest_select.
- If it can be done, how it is possible every time you add one test to tbltests, one collumn will be addet to the two "linked" tables.
Out of the box thinking, but ....

[ArviLaanemets]

This structure is starting from middle of nowhere! Try to describe the timeline of events!

You don't simply decide, that you need to make some tests, and then you start looking for patients who need them. A patient arrives and wants something. You need to register this patient and his visit somewhere. So at start you need patients table.
1. A table tbl_Patients - it looks like you have it OK;

You also need some table where all tests are defined.
2. A table tbl_Tests: Test_Code_PK, Test_Name, ...
Here all available tests are listed, along with all other test-specific info which never doesn't change. Analyzer obviously doesn't belong here - he/she belongs to table where you store results. And you have p.e. to decide, are test prices fixed forever, or do you need a separate table where the prices along with validity date are stored.;

Now all prerequisites are done, and you can start with patient's woes. as first thing, you have to register his/her visit, as a patient may visit you several times, and he/she may get several times some tests prescribed.
3. A table tbl_Visits: Visit_ID_PK, Patient_ID_FK, VisitDate, ...;

Now the patients gets some tests in current visit prescribed and you have to register them
4. A table tbl_Test_Select: TSID_PK, Visit_ID_FK, Patient_ID_FK, TestCode_FK, ...
NB! TestCode_FK is selected from table tbl_Tests,
NB! For every test prescribed, you add a new row with according test code ino tbl_Test_Select;

After tests are registered, analyzers can start their work. and they need a table to register the results.
5. tbl_Test_Results: TR_ID_PK, TSID_FK, Test_Result, Analyzer, Remarks, ...
Whenever you need some report/printout for results of test or visit, this table is from where you get most of data.
NB! For every test made, analyzer adds a new row with according test result into tbl_Test_Results. You may have here test code too, but TSID_FK determines it anyway, so it will be abundant here.
NB! It is possible, for some tests the result will be number and for some tests a text. When possible, try replace text values with numbers and read according texts from some special table. When this isn't possible, you may end up with 2 separate fields for result;

At last, you need a invoice/order table, where you collect data needed for printing it out.
6. tbl_Patient_Orders: POID_PK, Patient_FK, Visit_ID_FK, Order_Date, ...
To print out order, you have to construct a query based on tbl_Patient_Orders, tbl_Test_Select (to get the list of tests), tbl_Tests or tbl_Test_Prices (depending on where you keep info about test prices) to get prices for tests analyzed, tbl_Patient to get additional info about patient like name address, etc., and in case you want test results on order too, then tbl_Test_Results

[TONYWALKER]

Thank you for the reply!ArviLaanemets, if you start from the bottom of the picture you can see all the steps you describe, patient, select exams(= order), results of exams.
What you describe is the basic access structure, no question about that!!(you can see it to my previous thread, its exactly as you describe it except from tabl visits, the work can be done without it.)
This is a very different aproach, the only thing missing is to connect records from tbl_tests with the fields of table tblresults and tbltest_select. Its a litle bit " crazy " approach i know, but it has some logic.(...maybe only in my disordered mind) .

Lets put it academical ... or philosophical .. is there a way to link records from one table with the fields of another table (the grey ones- tests)

[June7]

Why are you showing structure on spreadsheet? This is not a normalized relational database. Your statement "every time you add one test to tbltests, one collumn will be addet to the two "linked" tables" makes no sense when considering the principles of RDBS. Dynamically 'adding' columns is not compliant with normalized structure.

Records are linked based on PK and FK values.

I have designed a laboratory database (not patients, construction materials testing). Even though it does not fully comply with normalized principles, columns (fields) are not added dynamically.

[TONYWALKER]

Respectful June7, thank you for the reply. The spreadship is just to show the relation, a draft. Its another way of thinking, probably not applied to access. Even though a dynamicly creation of fields can not be applied, if there was a way to link fields with records, changes the way we are all think about db. I sure do not dissagree with all of you, what you discribe are the normalized principles... but the possibiltity changes the way and the dynamics of db as we all know and work with, adds a new dimention.(mho ... i think you can dynamicaly ... create tables with make table queries or VB). Anyway, this is just a thought, i know that the most valuable thing for all of us is time and i dont want to spend anyone time. Just see it as another way of thinking about db.
With Respect, Thank you for the reply!!
Take a look at this i 've found it searching ... ( https://www.codeproject.com/Articles...mns-at-runtime)

[June7]

Of course it can be done, just can't say it's a good idea.