Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Return only certain fields/records from multiple tables

Discussion in 'Information Technology' started by penake, Nov 10, 2005.

  1. penake

    penake
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Ontario Police Review Arrest Of Sikh Man Whose Turban Wasn't Returned - CTV News Breaking News Jul 16, 2016
    Movies Ashdoc's short movie review---Tanu weds Manu returns Theatre, Movies & Cinema Jun 6, 2015
    Zoroastrianism Parsis pray for return of the scavenger bird Interfaith Dialogues Aug 28, 2013
    Steve Jobs' Return to Apple Business, Lifestyle & Leisure Mar 28, 2013
    Opinion Campaign to reward homeless man who returned diamond engagement ring hits $175,000 - and that total Breaking News Mar 7, 2013

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    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.

    "penake" wrote:

    > 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
     
  4. penake

    penake
    Expand Collapse
    Guest

    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.

    "penake" wrote:

    > 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
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    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" wrote:

    > 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.
    >
    > "penake" wrote:
    >
    > > 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
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    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" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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.
    > > >
    > > > "penake" wrote:
    > > >
    > > > > 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
     
  7. penake

    penake
    Expand Collapse
    Guest

    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" wrote:

    > 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" wrote:
    >
    > > 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.
    > >
    > > "penake" wrote:
    > >
    > > > 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
     
  8. penake

    penake
    Expand Collapse
    Guest

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

    "KARL DEWEY" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > > "penake" wrote:
    > > > >
    > > > > > 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
     
  9. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    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" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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.
    > > >
    > > > "penake" wrote:
    > > >
    > > > > 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
     

Share This Page