F
fjbuch
I am creating a pedigree table for genetics research. I have about 150
patients and more coming in all the time. I created a query to work out
which patients in the study are not yet listed in the pedigree table. I
used an unmatched query to do that. It looks what is in the table of
specimens (laboratory holdings) and selects that patient if it does not
find. It is quite easy then to use that query as the basis of an append
query to add the relevant details to the pedigree table. EASY. It
worked. But now comes the difficult part. I also have to create a
separate row (record) in the pedigree table for the mother and another
one for the father - even if I do not have a specimen from them. How
does one do it.
The method I thought of was to write the patient to the pedigree table
and then to run a query against that table to see which families did
not have a mother record. The query does this by "group by" family ID
number and noting which family ID's have no mother record. That query
then gets used as the basis of an append query. However, I keep getting
key violations and I have checked for the usual suspects: duplicates,
null values, correct data type etc.
Am I allowed to do what I am trying to do? Or am I creating a vicious
cycle: Pedigree Table > Select Query > Append Query > Pedigree Table.
Could it be that Microsoft Access is smart enough and quick enough to
realize that it is writing a record for the mother that it should not
be writing since the record already exists albeit only for the past
microsecond. In other words by writing a record for the mother it no
longer shows up in the select query of patients who have no mother
listed.
If I am not allowed to do that then I could workaround it by using my
original unmatched query as the seed for the mother append, then for
the father append and only then for the patient append. One would have
to leave the patient append for the end because once it got written it
would no longer show up in the unmatched query.
The bottom line question here is " How does one make an append query
write 3 similar but slightly different records rather than just one
record per seed?"
Farrel Buchinsky
patients and more coming in all the time. I created a query to work out
which patients in the study are not yet listed in the pedigree table. I
used an unmatched query to do that. It looks what is in the table of
specimens (laboratory holdings) and selects that patient if it does not
find. It is quite easy then to use that query as the basis of an append
query to add the relevant details to the pedigree table. EASY. It
worked. But now comes the difficult part. I also have to create a
separate row (record) in the pedigree table for the mother and another
one for the father - even if I do not have a specimen from them. How
does one do it.
The method I thought of was to write the patient to the pedigree table
and then to run a query against that table to see which families did
not have a mother record. The query does this by "group by" family ID
number and noting which family ID's have no mother record. That query
then gets used as the basis of an append query. However, I keep getting
key violations and I have checked for the usual suspects: duplicates,
null values, correct data type etc.
Am I allowed to do what I am trying to do? Or am I creating a vicious
cycle: Pedigree Table > Select Query > Append Query > Pedigree Table.
Could it be that Microsoft Access is smart enough and quick enough to
realize that it is writing a record for the mother that it should not
be writing since the record already exists albeit only for the past
microsecond. In other words by writing a record for the mother it no
longer shows up in the select query of patients who have no mother
listed.
If I am not allowed to do that then I could workaround it by using my
original unmatched query as the seed for the mother append, then for
the father append and only then for the patient append. One would have
to leave the patient append for the end because once it got written it
would no longer show up in the unmatched query.
The bottom line question here is " How does one make an append query
write 3 similar but slightly different records rather than just one
record per seed?"
Farrel Buchinsky