 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / January 2006Many to many relationships
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.  SignatureMore info, please ... Jeff Boyce <Office/Access MVP> > 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? 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. > Bacchus > [quoted text clipped - 7 lines] > > area office. I tried subreports, but it produces a new page for each area > > employee. Is there a way to do this? 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. SignatureRegards Jeff Boyce <Office/Access MVP> > 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 [quoted text clipped - 15 lines] > > > area office. I tried subreports, but it produces a new page for each area > > > employee. Is there a way to do this? 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. > Sorry, but I'm still not clear ... > [quoted text clipped - 41 lines] > area > > > > employee. Is there a way to do this? Check the properties of your "repeating" controls -- you can hide duplicates. SignatureRegards Jeff Boyce <Office/Access MVP> > 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 [quoted text clipped - 54 lines] > > area > > > > > employee. Is there a way to do this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.