Query with 3 tables with 1 to many relationship

[Tina Hudson]

Good day!

I'm trying to figure out how to set up a query. Here's the scoop:

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

I tried a Union query, but I would like to be able to differentiate between
a "factor" and a "finding".

Any suggestions and/or recommendations?

 

[KARL DEWEY]

Try this --
SELECT tblPersonChild.*, [Factor], [Finding]
FROM (tblPersonChild LEFT JOIN tblPersonChild_Factors ON
tblPersonChild.[Person_ID] = tblPersonChild_Factors.[Person_ID]) LEFT JOIN
tblPersonChild_Findings ON tblPersonChild.[Person_ID] =
tblPersonChild_Findings.[Person_ID];

 

[Marshall Barton]

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

O think this kind of thing is best handled by outtin the
factors and findings in separate subreports. The record
source queries for the main and subreports should be pretty
straightforward and using ssparate subreports makes keeping
the data separate trivial.

 

[Tina Hudson]

Karl,

That still gives me the same problem - if 2 factors and 1 finding, the
finding is repeated on the row with the 2nd factor, i.e.,

Person_ID Factor Finding
305 SKills Improper Discipline
305 Sub Abuse Improper Discipline

I don't think it's going to be possible without my getting really creative
and it's just too darn late in the day for that.

I want to be able to select from the list box on the report menu, a list of
factors and then a list of findings, and find those children who match both
of my list selections, and the children can be duplicated as we are counting
the children that have each factor and each finding and grouping them
together. Geez, does this make any sense?

--
Thanks,
Tina Hudson

Try this --
SELECT tblPersonChild.*, [Factor], [Finding]
FROM (tblPersonChild LEFT JOIN tblPersonChild_Factors ON
tblPersonChild.[Person_ID] = tblPersonChild_Factors.[Person_ID]) LEFT JOIN
tblPersonChild_Findings ON tblPersonChild.[Person_ID] =
tblPersonChild_Findings.[Person_ID];

--
Build a little, test a little.

Good day!

I'm trying to figure out how to set up a query. Here's the scoop:

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

I tried a Union query, but I would like to be able to differentiate between
a "factor" and a "finding".

Any suggestions and/or recommendations?

 

[Tina Hudson]

Marshall,

Do you think if I make the subreports invisible and have the text boxes on
the main report showing the demographics and counts be calculated expressions
from the subreports?

Will I be able to do this from a report menu with list boxes for user to
select just certain factors and/or findings? Well, not will I be able to do
this, is Access capable of doing this? lol

What I want is a report menu for the user to select certain parameters.
Say, they select 2 Factors and 1 finding, and then show an aggregated count
of the children by race, age, gender, etc.

What I'm trying to do is see the different demographic characteristics for
different combinations of factors and findings.

I can envision it better than I can explain it.

Am I on the right track?

Thanks!



--
Thanks,
Tina Hudson

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

O think this kind of thing is best handled by outtin the
factors and findings in separate subreports. The record
source queries for the main and subreports should be pretty
straightforward and using ssparate subreports makes keeping
the data separate trivial.

 

[KARL DEWEY]

In report design view properties for these field set Hide Duplicates to Yes.
--
Build a little, test a little.

Karl,

That still gives me the same problem - if 2 factors and 1 finding, the
finding is repeated on the row with the 2nd factor, i.e.,

Person_ID Factor Finding
305 SKills Improper Discipline
305 Sub Abuse Improper Discipline

I don't think it's going to be possible without my getting really creative
and it's just too darn late in the day for that.

I want to be able to select from the list box on the report menu, a list of
factors and then a list of findings, and find those children who match both
of my list selections, and the children can be duplicated as we are counting
the children that have each factor and each finding and grouping them
together. Geez, does this make any sense?

--
Thanks,
Tina Hudson

Try this --
SELECT tblPersonChild.*, [Factor], [Finding]
FROM (tblPersonChild LEFT JOIN tblPersonChild_Factors ON
tblPersonChild.[Person_ID] = tblPersonChild_Factors.[Person_ID]) LEFT JOIN
tblPersonChild_Findings ON tblPersonChild.[Person_ID] =
tblPersonChild_Findings.[Person_ID];

--
Build a little, test a little.

Good day!

I'm trying to figure out how to set up a query. Here's the scoop:

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

I tried a Union query, but I would like to be able to differentiate between
a "factor" and a "finding".

Any suggestions and/or recommendations?

 

[Marshall Barton]

Do you think if I make the subreports invisible and have the text boxes on
the main report showing the demographics and counts be calculated expressions
from the subreports?

Will I be able to do this from a report menu with list boxes for user to
select just certain factors and/or findings? Well, not will I be able to do
this, is Access capable of doing this? lol

What I want is a report menu for the user to select certain parameters.
Say, they select 2 Factors and 1 finding, and then show an aggregated count
of the children by race, age, gender, etc.

What I'm trying to do is see the different demographic characteristics for
different combinations of factors and findings.

If you only want to see aggregated results and do not want
to see the individual records, then I think you should try
to do it all in a single report's recor source query. Maybe
something like what Ken posted?

 

[Tina Hudson]

Ken,

You are absolutely without a doubt a genius! Many thanks!

One thing, Access didn't like the fact that I used one of its "reserved"
words, that being "value", so I changed that to "choice" and it worked
perfectly.

Now to figure out what to do with all this power!

Again, you made my week. Have a good one!

--
Thanks,
Tina Hudson

Your idea of a Union operation would work, and you can differentiate between
factors and findings with appropriate constants in a column, e.g.

SELECT tblPersonChild.Person_ID, "Factor" AS Category, Factor AS Value
FROM tblPersonChild INNER JOIN tblPersonChild_Factors
ON tblPersonChild.Person_ID = tblPersonChild_Factors.Person_ID
UNION ALL
SELECT tblPersonChild.Person_ID, "Finding", Finding
FROM tblPersonChild INNER JOIN tblPersonChild_Findings
ON tblPersonChild.Person_ID = tblPersonChild_Findings.Person_ID
ORDER BY tblPersonChild.Person_ID, Category;

This would return the following:

ID Category Value
301 Factor Substance Abuse
301 Factor Parental Skills
301 Finding Improper Discipline

Or you could return the factors and findings in separate columns with:

SELECT tblPersonChild.Person_ID, Factor, NULL AS Finding
FROM tblPersonChild INNER JOIN tblPersonChild_Factors
ON tblPersonChild.Person_ID = tblPersonChild_Factors.Person_ID
UNION ALL
SELECT tblPersonChild.Person_ID, NULL, Finding
FROM tblPersonChild INNER JOIN tblPersonChild_Findings
ON tblPersonChild.Person_ID = tblPersonChild_Findings.Person_ID
ORDER BY tblPersonChild.Person_ID, Finding;

which would return:

ID Factor Finding
301 Substance Abuse
301 Parental Skills
301 Improper Discipline

In either case you can add other columns, e.g. names, from the tblPersonChild
table to the SELECT clause of each part of the UNION ALL operation but they
must be in the same positions in each.

Ken Sheridan
Stafford, England

Good day!

I'm trying to figure out how to set up a query. Here's the scoop:

I have 3 tables (tblPersonChild, tblPersonChild_Factors, and
tblPersonChild_Findings).

The first table contains [Person_ID] and demographic info I want to include
on a report. No duplicate children in this table.

The other 2 tables each contain: [ID], [Person_ID], [Factor] for
tblPersonChild_Factors; and [FInding_ID],[Person_ID],[Finding] for
tblPersonChild_Findings

There can be more than one factor and more than one finding for each child,
so duplicate children in this field.

The relationship is a 1 to many from tblPersonChild to the other 2 tables.

I have created a form that is a report menu with a list box for factors and
a list box for findings. The user can select however many factors and/or
findings they want to show on the report, and then the user clicks a cmd
button to open the report. The report shows the factors and findings
selected, and aggregated data for the basic demographic information,such as
age, race, gender, for the children who had the selected factors and/or
findings. It is okay that there is a duplicated count of the children.

However, I can't seem to figure out the query to do this. I've tried
including the 3 tables in a select query, but the relationship part is
messing things up (I think) because when I chose the join for All records
from the tblPersonChild table to the tblPersonChild_Factors table, I can't
chose the same join for the Findings table.

The query will run, but if there are more factors than findings, then the
finding is repeated. For example, if a child has 2 factors, and 1 findings,
this is the result:

ID Factor Finding
301 Substance Abuse Improper Discipline
301 Parental Skills Improper Discipline

I tried a Union query, but I would like to be able to differentiate between
a "factor" and a "finding".

Any suggestions and/or recommendations?