Rolling Up 3 Separate Tables

[oftenconfused]

Here's what I'm trying to do.

I have separate tables for Office, Branch and Department (moving up the
ladder). For most other tables, I link to just one of those tables, but
there are several instances where the choice could be any combination and
multiple numbers of Offices, Branches and Departments. My question is, is
there a way to a "roll" this up, to make selection (on the Form) and output
(on the Report) easier?

Thanks!

 

[KARL DEWEY]

I assume that you have these tables setup in a one-to-many relationship.
Use left joins in your select query.
In design view select the three tables. Click on Office.OfficeID and drag
to Branch.OfficeID, doubleclick on the connecting line and select the option
that say show all record from Office and only those from Branch that match.
Do the same for Branch to Department (Branch.BranchID - Department.BranchID).

In your report select 'Hide Duplicates' - Yes for Office and Branch.

 

[John W. Vinson]

Here's what I'm trying to do.

I have separate tables for Office, Branch and Department (moving up the
ladder). For most other tables, I link to just one of those tables, but
there are several instances where the choice could be any combination and
multiple numbers of Offices, Branches and Departments. My question is, is
there a way to a "roll" this up, to make selection (on the Form) and output
(on the Report) easier?

Thanks!

If the three tables have the same structure (same number of fields of matching
datatypes), and if by "roll up" you mean to combine them "end to end" (42
records in Offices, 24 records in Branches, 34 records in Departments giving
100 records in all), then you can use a UNION query.

You may also want to reconsider your design, with one larger table containing
all of these table's data, with one additional field to indicate whether this
record is from an office, a branch or a department.

 

[oftenconfused]

Karl,

Thanks for your reply. I think we're almost there.

Specifically, this is what I'm looking to do.

I have a table called PRODUCT. Now, sometimes products are produced by one
or more offices, by one or more organizations, or by one or more agencies, or
by a combination of offices, organizations and agencies. I have separate
tables called OFFICE, ORGANIZATION and AGENCY. One solution would be be
separately link (on the many end of a one-to-many relationship) the OFFICE,
ORGANIZATION and AGENCY tables to the PRODUCT table. But, I thought if I
could create a table called ENTITY (that linked to OFFICE, AGENCY and
ORGANIZATION) I could simply things. So, I would end up linking to ENTITY
from PRODUCT.

Does this sound crazy?

Andrew

 

[KARL DEWEY]

Yesterday it was Office, Branch, and Department but today it is OFFICE,
ORGANIZATION and AGENCY with Product.
Do you mean to say that all levels produce the Product? This is not the
usual way companies are setup. What are the relationships between OFFICE,
ORGANIZATION and AGENCY?
What do you plan to use the data for? How will the database be of benefit?

 

[oftenconfused]

The database definitely has a use...an important one. There are three
different echelons:

Level 1: Department or Agency
Level 2: Branch or Organization
Level 3: Office

Sorry for the confusion in referring to it once as Organization and Agency,
when I referred to it previously as Branch and Department.

Naturally, a branch (or organization) can have multiple offices; a
department (or agency) can have multiple branches (or organizations).

Some information is unique to office, branch or department. Some
information is common to all three. I'm trying to find an easy way to
accommodate those instances where I have information common to all three --
for example, an office, branch or department could produce one or more
publications. I'd like to be able to select from the PUBLICATIONS table, but
want to avoid having to separately select through links OFFICE, BRANCH,
DEPARTMENT.

Andrew

 

[KARL DEWEY]

UNTESTED ---
Still not sure how you want to use this but let take a swag at it. I
assume you want to track what company elements that a document applies to.
Create your Publication table and possibly Version table in a one-to-many
relationship using PubID and PubVerId as primary keys.
Then create a PubUser table with fields PubID or PubVerId (based on your
needs) and User.
Set a one-to-many relationship from Publication or Version table to PubUser
and one-to-many from a union query that joins the three tables.
Use a form/subform for Publication/PubUser with the Master/Child links set.

 

[Fred]

Regarding developer stuff, I'm several levels below Karl and John, which
might be a plus in this case.

You need to start with table structure.....decide what is the data and
relationships that you are going to database. Sounds like you decided on the
main entities that you are databasing, but not the relationships.
Relationships are something that exist in the real world which you record in
your database. Sounds like the candidartes for the relationships to be
recorded are:

- This office is underneath the following Branch/Organization
- This Department/Organization underneath the following Department/Agency
- This publication is directly a product of the following entity:



While drawing lines between tables etc. (or writing SQL statement) is a part
of linking, the real linking is record to record, and the real work of
linking is done by:

- putting a FK into a table for the purpose of that particular link
- link two records by placing a value that matches one table int the FK
field of another record in the other table.

I'm sure folks would be happy to help with those fundemantal, and knowing
what you are trying to get to would help give that advice, but you'd also
have to clarify some of what you said on the latter including:

- What do mean by "roll up?"
- when you say "I'd like to be able to select from the PUBLICATIONS table, but
want to avoid having to separately select through links OFFICE, BRANCH,
DEPARTMENT." What do you mean? You don't have to go through other tables
to look at your publications table.

Hope that helps a little.