Return only certain fields/records from multiple tables

P

penake

I have 27 tables. Each table has 40+ fields. For this problem, each table
will have only one or two fields that have any responses, but I don't know
which fields they will be. I only want to show, in a report, fields from
those tables that have responses. I have tried building several queries but
that is becoming far to complex. Is this a case for using 'loops'...I have
never done that.
thanks,
mpenkake
 
K

KARL DEWEY

Post an example of the table-field structures. Then give an example of data
in them and what you expect the output product to look like so a suggestion
might be made.
 
P

penake

OK. ClientTbl is primary table; some secondary tables include:
MotherHistoryTbl, FatherHIstoryTbl, SiblingHistoryTbl are 1 to many
relationships to ClientTbl. Some fields include: includes 'married',
'currently living', 'currently employed', 'currently driving car', 'owns
home', 'attends church', etc. I am looking only for the "yes" answers.
Again, it's the extreme number of fields that I'm looking at that is
creating this complex problem.
Thank you.
 
K

KARL DEWEY

MotherHistoryTbl, FatherHIstoryTbl, and SiblingHistoryTbl can be all in one
table and just use a field to indicate what their relation is to client.
This way you can easily pull a list of relatives.

I would use one field for married, single, or divorced – M, S, or D. In
you query use an IIF([MartialStatus] =â€Mâ€, “Marriedâ€,
IIF([MartialStatus]=â€Sâ€,â€Singleâ€,
IIF([MartialStatus]=â€Dâ€,â€Divorcedâ€,â€Unknownâ€)))

For other field use IIF([Employed]=-1,â€Employedâ€,â€â€) and the same for other
one choice Yes/No fields. Then in the report do not use the labels but
concatenate them together so as to display a string of one or two word
descriptions separated with a comma and a space.
 
K

KARL DEWEY

Here you need to build a table of aliments with a primary key. Then build a
junction table that has ClientID field and AilmentID field. Set a one-to-many
relation from client to the junction table and a one-to-many relation from
aliment to the junction table.
On your data entry form use a subform in datasheet to display the list of
ailment they have had. Your junction table could also have OccurDate field.
Make a unique index of all the fields as one could have multiple dates for
same vacination.

penake said:
Ok, I get that, but this is even more difficult as there are hundreds of
questions like: Had mumps, had chicken pox, had measles, OR was vacinated for
polio, was vacinated for small pox, was vacinated for typhoid, etc. where,
again, each field needs to be independent and recorded only if it is answered
'yes'.

KARL DEWEY said:
MotherHistoryTbl, FatherHIstoryTbl, and SiblingHistoryTbl can be all in one
table and just use a field to indicate what their relation is to client.
This way you can easily pull a list of relatives.

I would use one field for married, single, or divorced – M, S, or D. In
you query use an IIF([MartialStatus] =â€Mâ€, “Marriedâ€,
IIF([MartialStatus]=â€Sâ€,â€Singleâ€,
IIF([MartialStatus]=â€Dâ€,â€Divorcedâ€,â€Unknownâ€)))

For other field use IIF([Employed]=-1,â€Employedâ€,â€â€) and the same for other
one choice Yes/No fields. Then in the report do not use the labels but
concatenate them together so as to display a string of one or two word
descriptions separated with a comma and a space.


penake said:
OK. ClientTbl is primary table; some secondary tables include:
MotherHistoryTbl, FatherHIstoryTbl, SiblingHistoryTbl are 1 to many
relationships to ClientTbl. Some fields include: includes 'married',
'currently living', 'currently employed', 'currently driving car', 'owns
home', 'attends church', etc. I am looking only for the "yes" answers.
Again, it's the extreme number of fields that I'm looking at that is
creating this complex problem.
Thank you.

:

I have 27 tables. Each table has 40+ fields. For this problem, each table
will have only one or two fields that have any responses, but I don't know
which fields they will be. I only want to show, in a report, fields from
those tables that have responses. I have tried building several queries but
that is becoming far to complex. Is this a case for using 'loops'...I have
never done that.
thanks,
mpenkake
Click to expand...
Click to expand...
Click to expand...
 
P

penake

Ok, I get that, but this is even more difficult as there are hundreds of
questions like: Had mumps, had chicken pox, had measles, OR was vacinated for
polio, was vacinated for small pox, was vacinated for typhoid, etc. where,
again, each field needs to be independent and recorded only if it is answered
'yes'.

KARL DEWEY said:
MotherHistoryTbl, FatherHIstoryTbl, and SiblingHistoryTbl can be all in one
table and just use a field to indicate what their relation is to client.
This way you can easily pull a list of relatives.

I would use one field for married, single, or divorced – M, S, or D. In
you query use an IIF([MartialStatus] =â€Mâ€, “Marriedâ€,
IIF([MartialStatus]=â€Sâ€,â€Singleâ€,
IIF([MartialStatus]=â€Dâ€,â€Divorcedâ€,â€Unknownâ€)))

For other field use IIF([Employed]=-1,â€Employedâ€,â€â€) and the same for other
one choice Yes/No fields. Then in the report do not use the labels but
concatenate them together so as to display a string of one or two word
descriptions separated with a comma and a space.


penake said:
OK. ClientTbl is primary table; some secondary tables include:
MotherHistoryTbl, FatherHIstoryTbl, SiblingHistoryTbl are 1 to many
relationships to ClientTbl. Some fields include: includes 'married',
'currently living', 'currently employed', 'currently driving car', 'owns
home', 'attends church', etc. I am looking only for the "yes" answers.
Again, it's the extreme number of fields that I'm looking at that is
creating this complex problem.
Thank you.
Click to expand...
Click to expand...
 
P

penake

Okay, thanks for your help on this. I loved your turn-around time on the
responses.

KARL DEWEY said:
By the way the relatives should also be in the client list even if not being
treated so that you would have contact information.

penake said:
Ok, I get that, but this is even more difficult as there are hundreds of
questions like: Had mumps, had chicken pox, had measles, OR was vacinated for
polio, was vacinated for small pox, was vacinated for typhoid, etc. where,
again, each field needs to be independent and recorded only if it is answered
'yes'.

KARL DEWEY said:
MotherHistoryTbl, FatherHIstoryTbl, and SiblingHistoryTbl can be all in one
table and just use a field to indicate what their relation is to client.
This way you can easily pull a list of relatives.

I would use one field for married, single, or divorced – M, S, or D. In
you query use an IIF([MartialStatus] =â€Mâ€, “Marriedâ€,
IIF([MartialStatus]=â€Sâ€,â€Singleâ€,
IIF([MartialStatus]=â€Dâ€,â€Divorcedâ€,â€Unknownâ€)))

For other field use IIF([Employed]=-1,â€Employedâ€,â€â€) and the same for other
one choice Yes/No fields. Then in the report do not use the labels but
concatenate them together so as to display a string of one or two word
descriptions separated with a comma and a space.


:

OK. ClientTbl is primary table; some secondary tables include:
MotherHistoryTbl, FatherHIstoryTbl, SiblingHistoryTbl are 1 to many
relationships to ClientTbl. Some fields include: includes 'married',
'currently living', 'currently employed', 'currently driving car', 'owns
home', 'attends church', etc. I am looking only for the "yes" answers.
Again, it's the extreme number of fields that I'm looking at that is
creating this complex problem.
Thank you.

:

I have 27 tables. Each table has 40+ fields. For this problem, each table
will have only one or two fields that have any responses, but I don't know
which fields they will be. I only want to show, in a report, fields from
those tables that have responses. I have tried building several queries but
that is becoming far to complex. Is this a case for using 'loops'...I have
never done that.
thanks,
mpenkake
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

By the way the relatives should also be in the client list even if not being
treated so that you would have contact information.

penake said:
Ok, I get that, but this is even more difficult as there are hundreds of
questions like: Had mumps, had chicken pox, had measles, OR was vacinated for
polio, was vacinated for small pox, was vacinated for typhoid, etc. where,
again, each field needs to be independent and recorded only if it is answered
'yes'.

KARL DEWEY said:
MotherHistoryTbl, FatherHIstoryTbl, and SiblingHistoryTbl can be all in one
table and just use a field to indicate what their relation is to client.
This way you can easily pull a list of relatives.

I would use one field for married, single, or divorced – M, S, or D. In
you query use an IIF([MartialStatus] =â€Mâ€, “Marriedâ€,
IIF([MartialStatus]=â€Sâ€,â€Singleâ€,
IIF([MartialStatus]=â€Dâ€,â€Divorcedâ€,â€Unknownâ€)))

For other field use IIF([Employed]=-1,â€Employedâ€,â€â€) and the same for other
one choice Yes/No fields. Then in the report do not use the labels but
concatenate them together so as to display a string of one or two word
descriptions separated with a comma and a space.


penake said:
OK. ClientTbl is primary table; some secondary tables include:
MotherHistoryTbl, FatherHIstoryTbl, SiblingHistoryTbl are 1 to many
relationships to ClientTbl. Some fields include: includes 'married',
'currently living', 'currently employed', 'currently driving car', 'owns
home', 'attends church', etc. I am looking only for the "yes" answers.
Again, it's the extreme number of fields that I'm looking at that is
creating this complex problem.
Thank you.

:

I have 27 tables. Each table has 40+ fields. For this problem, each table
will have only one or two fields that have any responses, but I don't know
which fields they will be. I only want to show, in a report, fields from
those tables that have responses. I have tried building several queries but
that is becoming far to complex. Is this a case for using 'loops'...I have
never done that.
thanks,
mpenkake
Click to expand...
Click to expand...
Click to expand...
 
Top