Many to many relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to produce a report that lists all the personnel from our area
offices in a list and then list all of the branches that report up to that
area office. I tried subreports, but it produces a new page for each area
employee. Is there a way to do this?
 
Bacchus

Your subject line mentioned relationships. Your post didn't. Neither
mentioned your underlying data structure.

How you generate a report depends on what data you are working from.
 
I have an Area table that lists all of the area personnel with the AREAID as
the relating field to the branch table. The branch table lists branches that
report up to a specific area and it also has AREAID as a field and that is
how I combine the two tables. What I want is a report that will list the
Area at the top, then all of the personnel that work in that area office,
then I want all of the branches that report up to that area to be listed
below.
 
Sorry, but I'm still not clear ...

Are you saying you have one row per person in your [Area] table? If so, why
is there an "AREAID"? Doesn't that ID refer to an AREA, rather than a
person?

Your [Branch] table lists branches that report to AREAs ... does this mean
you have one row per branch, and each branch has an AREAID? Can a branch
report to more than one AREA?

You can create a query that joins the two tables on AREAID, and shows area,
person and branch.

You can use that query as the source for your report.

In design mode of your report, you'll need to use the Sort/Group toolbar
button to make AREA the "outermost" group. It sounds like you might need to
include the persons at the area as detail records, and a second report,
embedded as a subreport, to display the related branch offices. But I can't
be sure, as I don't yet understand your data structure/relationships.
 
Sorry again, but yes in my area table I have one person per row. The AreaID
tells me which area that person works in. I am trying to list all of the
people who work in a certain area Office. Then underneath I want a list of
all the branches that report up to that area office. Yes each branch has an
areaID. I think what is confusing you is that the area personnel do not work
at a branch.

You are correct in that the design of my report I need the outmost grouping
to show the area, then I want a listing of all the people that work in the
area office. I tried to do a subreport that then listed all the branches
that report to that area office. That is where I was having problems. For
each area person, I would get a repeated list of branches.

Jeff Boyce said:
Sorry, but I'm still not clear ...

Are you saying you have one row per person in your [Area] table? If so, why
is there an "AREAID"? Doesn't that ID refer to an AREA, rather than a
person?

Your [Branch] table lists branches that report to AREAs ... does this mean
you have one row per branch, and each branch has an AREAID? Can a branch
report to more than one AREA?

You can create a query that joins the two tables on AREAID, and shows area,
person and branch.

You can use that query as the source for your report.

In design mode of your report, you'll need to use the Sort/Group toolbar
button to make AREA the "outermost" group. It sounds like you might need to
include the persons at the area as detail records, and a second report,
embedded as a subreport, to display the related branch offices. But I can't
be sure, as I don't yet understand your data structure/relationships.

--
Regards

Jeff Boyce
<Office/Access MVP>

Bacchus said:
I have an Area table that lists all of the area personnel with the AREAID as
the relating field to the branch table. The branch table lists branches that
report up to a specific area and it also has AREAID as a field and that is
how I combine the two tables. What I want is a report that will list the
Area at the top, then all of the personnel that work in that area office,
then I want all of the branches that report up to that area to be listed
below.
Click to expand...
Click to expand...
 
Check the properties of your "repeating" controls -- you can hide
duplicates.

--
Regards

Jeff Boyce
<Office/Access MVP>

Bacchus said:
Sorry again, but yes in my area table I have one person per row. The AreaID
tells me which area that person works in. I am trying to list all of the
people who work in a certain area Office. Then underneath I want a list of
all the branches that report up to that area office. Yes each branch has an
areaID. I think what is confusing you is that the area personnel do not work
at a branch.

You are correct in that the design of my report I need the outmost grouping
to show the area, then I want a listing of all the people that work in the
area office. I tried to do a subreport that then listed all the branches
that report to that area office. That is where I was having problems. For
each area person, I would get a repeated list of branches.

Jeff Boyce said:
Sorry, but I'm still not clear ...

Are you saying you have one row per person in your [Area] table? If so, why
is there an "AREAID"? Doesn't that ID refer to an AREA, rather than a
person?

Your [Branch] table lists branches that report to AREAs ... does this mean
you have one row per branch, and each branch has an AREAID? Can a branch
report to more than one AREA?

You can create a query that joins the two tables on AREAID, and shows area,
person and branch.

You can use that query as the source for your report.

In design mode of your report, you'll need to use the Sort/Group toolbar
button to make AREA the "outermost" group. It sounds like you might need to
include the persons at the area as detail records, and a second report,
embedded as a subreport, to display the related branch offices. But I can't
be sure, as I don't yet understand your data structure/relationships.

--
Regards

Jeff Boyce
<Office/Access MVP>

Bacchus said:
I have an Area table that lists all of the area personnel with the
Click to expand...
AREAID
as
the relating field to the branch table. The branch table lists
Click to expand...
branches
that
report up to a specific area and it also has AREAID as a field and that is
how I combine the two tables. What I want is a report that will list the
Area at the top, then all of the personnel that work in that area office,
then I want all of the branches that report up to that area to be listed
below.

:

Bacchus

Your subject line mentioned relationships. Your post didn't. Neither
mentioned your underlying data structure.

How you generate a report depends on what data you are working from.

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

I am trying to produce a report that lists all the personnel from
Click to expand...
our
area
offices in a list and then list all of the branches that report up
Click to expand...
to
that
area office. I tried subreports, but it produces a new page for
Click to expand...
each
area
employee. Is there a way to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top