form to table - index problem?

[jzwp11]

The observation table has a record for each observation time.

baseline, +1h, +2h, +4h, +6h, +10h...

So you would have records with 0, 1, 2, 4, 6,
These I assume are all in reference to the date of the visit. What are the 5 other observation points?



You make the connection in this table so there will be 9 records for each patient visit record in tblPatientVisit

tblPatientVisitObs
-pkPatientVisitObsID primary key, autonumber
-fkPatientVisitID foreign key to tblPatientVisits


-fkObsID foreign key to tblObservations

[freeskier81]

The observation table has a record for each observation time.

baseline, +1h, +2h, +4h, +6h, +10h...

So you would have records with 0, 1, 2, 4, 6,
These I assume are all in reference to the date of the visit. What are the 5 other observation points?

screen, baseline (0), +1h (1), +2h (2), +4h(4), +6(6), +10h(10), +24h(24), day8(168)



You make the connection in this table so there will be 9 records for each patient visit record in tblPatientVisit

tblPatientVisitObs
-pkPatientVisitObsID primary key, autonumber
-fkPatientVisitID foreign key to tblPatientVisits
-fkObsID foreign key to tblObservations

one basic comprehension problem:what exactly is meant by "record(s)"?


and a 2nd point regarding the tests and patientvisitobstest tbl:
in the test tbl I enter the nomenclature of my lab tests (e.g.: rbc for red blood cells) and in the patientvisitobstest tbl I enter the results of the lab tests (e.g.: 4.0). so mail the same variables but in one tbl defined as txt and in the other as number?


and a 3rd point: the relations between the tbls (foreign keys) are done using the relations-manager? If so, which specifications? 1/2/3 and w or w/o referential integrity?

thanks so much!

[jzwp11]

one basic comprehension problem:what exactly is meant by "record(s)"?

A row of data in a table is a record. Each record can have data in several fields.

So tblObservation has 2 fields (pkObsID and longhour), the data in the table would look like this. Each row is a record

pkObsID|longHour
1|-1
2|0
3|1
4|2
5|4
6|6
7|10
8|24
9|168

When does the screening occur relative to the baseline? Are there tests associated with the screening? I just put in -1 above for the screening until I know a little more.

The join types in this case should all be type 1 and referential integrity should be enforced on all joins.

(long integer number, baseline=0, then just the hours since baseline after that)

so mail the same variables but in one tbl defined as txt and in the other as number?

The name of the test is text. As to the parameter, what type of data will you have? Will it be strictly numeric? alphabetic? or both?

[freeskier81]

A row of data in a table is a record. Each record can have data in several fields.

So tblObservation has 2 fields (pkObsID and longhour), the data in the table would look like this. Each row is a record

pkObsID|longHour
1|-1
2|0
3|1
4|2
5|4
6|6
7|10
8|24
9|168

When does the screening occur relative to the baseline? Are there tests associated with the screening? I just put in -1 above for the screening until I know a little more.

screening occurs days before BL. and there are tests associated with the screening.

The join types in this case should all be type 1 and referential integrity should be enforced on all joins.

i used my existing patients tbl - adding a new patientID variable (autonumber). now I'm not able to make a join with referential integrity. access states that there's data in the patientvisists tbl that interfere with the rules for referential integrity....although there's no data in there, exempt for the fk patientID and the pk visitID....

(long integer number, baseline=0, then just the hours since baseline after that)




The name of the test is text. As to the parameter, what type of data will you have? Will it be strictly numeric? alphabetic? or both?

numeric data

[jzwp11]

access states that there's data in the patientvisists tbl that interfere with the rules for referential integrity....although there's no data in there, exempt for the fk patientID and the pk visitID....

Just to confirm, there are no rows (records) in the patientvisit table? Or are there records without a value in the fkpatientID field?

Do you have the fkPatientID field's datatype set to a long integer number?

[freeskier81]

I've redone the tbl - reentered all values and now the join works.

I still do not get how this will work out in the end.

1 - I got my patients tbl (tbl_1) with entered patient characterizations (name, age, weight, height,...) and a pk patientID. I think i get this table.

2 - the visit tbl (tbl_2). I have an empty tbl with visitID as pk and 2 additional variables, namely patientID (joined with tbl_1, RI, option 1) and dte_visit.
Here my problems in understanding are kicking off...what shall go in this tbl? and how? just the visit dates (4 per patient)? and if, how do they connect with tbl 1? should the likes patientID appear in this tbl_2 or am I supposed to look it up in tbl_1 and reenter it in tbl_2 ? do I need more variables in tbl_2? like dte_d1, dte_d2, dte_scrn, dte_d8?

Right now I'm totally confused....no clue at all.......distressed...

could you provide me with an example how the data should be entered in the different tbls?
e.g: patID: 01, name: mayer, dob: 08.09.83, scrn: 01.01.10, d1: 10.01.10, plt_count_BL: 322, d_dim_BL: 0.5, d_dim_24h: 0.1

just a very shortened data set for one patient....

[jzwp11]

Here my problems in understanding are kicking off...what shall go in this tbl? and how? just the visit dates (4 per patient)? and if, how do they connect with tbl 1?

You would have a record in tblPatientVisit for each visit made by the patient.

The visit is contected to tbl 1 (the patient table) through the primary key ---foreign key relationship shown in red below

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatients
-dteVisit (date of visit)

What I envisioned was that a patient comes in on a certain date (dteVisit) and you start to monitor them over the course of 168 hours--this constitutes 1 visit. Is that how you define a visit? If not, you will have to clarify. If a patient undergoes 4 visits are the 3 later visits handled any differently than the first in terms of observations/testing?

When you mentioned the screening that kind of confused me, but I think we can work that into the current structure as just a separate visit. You will have to explain more how you define a screening in terms of your visits.

[freeskier81]

You would have a record in tblPatientVisit for each visit made by the patient.

The visit is contected to tbl 1 (the patient table) through the primary key ---foreign key relationship shown in red below

tblPatients
-pkPatientID primary key, autonumber
-txtFName
-txtLName

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatients
-dteVisit (date of visit)




What I envisioned was that a patient comes in on a certain date (dteVisit) and you start to monitor them over the course of 168 hours--this constitutes 1 visit. Is that how you define a visit? If not, you will have to clarify. If a patient undergoes 4 visits are the 3 later visits handled any differently than the first in terms of observations/testing?

When you mentioned the screening that kind of confused me, but I think we can work that into the current structure as just a separate visit. You will have to explain more how you define a screening in terms of your visits.

the structure of the study:
1 patient, 1 screening date/day, 3 study dates/days (d1 with blood collections on 6 different time points, d2 one time point, d8 one time point).
at every time point a number (more or less 15) lab tests are performed with the drawn blood.
the definition of the visit is more or less meaningless in my eye - one visit for 8 days or 3 different visits...where's the difference in the data set?

cleared things up?

[jzwp11]

Since the 3 study dates are linked via the various observation points I do not think that we would need 3 separate date records in the visit table. As to the screening, is there a time lapse between the screening date and the date the study begins?

Can a patient participate in the screening/study more than once?

[freeskier81]

there is a gap between screening and day1.(varying from about 2 to 14 days)
every patient can only participate once - screening and study.

[jzwp11]

I assume that it is important that a study be conducted only on a person who has been screened, so I would still use tblPatientVisits for that purpose. So for each patient there will be 2 records in tblPatientVisits, one for the screening and one for the start date of the study. BTW, I would include the time component in addition to the date for the date field in tblPatientVisits because we can use that in conjunction with the observation points if you really want to determine the exact date and time of an observation (dteVisit+hourly observation point).

Since you still have multiple tests related to the screening, we need essentially 1 observation point in tblPatientVisitObs for the screening and from there you would have the multiple related tests

could you provide me with an example how the data should be entered in the different tbls?
e.g: patID: 01, name: mayer, dob: 08.09.83, scrn: 01.01.10, d1: 10.01.10, plt_count_BL: 322, d_dim_BL: 0.5, d_dim_24h: 0.1

You would enter data via forms not by entering data directly into the tables.

I have attached an example database. Just open the form: frmPatients. I entered some example data that you will see in the in the form and various subforms.