Question about table structure

[Brandon Cheal]

I have a project table that keeps info about projects.
There is one record for each project, but there can be a
project lead and a project assistant.
I have a table of employees, but an employee could be a
lead or an assistant on any project. So I can't do a one
to many relationship from the EmployeeID to both fields on
the project table obviously.
But I want to keep this as simple as possible for
reporting purposes because I'll want to sort by both
fields.
My proposed solution:
Have two more tables, tblLead and tblAssistant.
tblLead has two fields, LeadID and EmployeeID
tblAssistant has two fields, AsstID and EmployeeID
I can have a one to many from tblEmployee.EmployeeID to
both tables, and then have a one-to-many from
tblLead.LeadID to tblProject.Lead. Also a one-to-many
from tblAssistant.AsstID to tblProject.Assistant.

I have a pretty good handle on understanding relationships
for the most part, but for some reason this situation is
throwing me for a loop. Is this the BEST solution, or am
I opening myself up for problems? Thanks
brandon

 

[Dirk Goldgar]

I have a project table that keeps info about projects.
There is one record for each project, but there can be a
project lead and a project assistant.
I have a table of employees, but an employee could be a
lead or an assistant on any project. So I can't do a one
to many relationship from the EmployeeID to both fields on
the project table obviously.

Why not? Or rather, why not have *two* such relationships -- one to the
Lead field in the Projects table, and one to the Assistant field? Each
of these fields could store an EmployeeID. In the Relationships window,
you can add the tblProjects table twice -- the second time it will be
called tblProjects_1, but that doesn't matter. Add the table twice,
then connect the Lead field to the EmployeeID field in tblProjects, and
the Assistant field to the EmployeeID in tblProjects_1.

But I want to keep this as simple as possible for
reporting purposes because I'll want to sort by both
fields.
My proposed solution:
Have two more tables, tblLead and tblAssistant.
tblLead has two fields, LeadID and EmployeeID
tblAssistant has two fields, AsstID and EmployeeID
I can have a one to many from tblEmployee.EmployeeID to
both tables, and then have a one-to-many from
tblLead.LeadID to tblProject.Lead. Also a one-to-many
from tblAssistant.AsstID to tblProject.Assistant.

I have a pretty good handle on understanding relationships
for the most part, but for some reason this situation is
throwing me for a loop. Is this the BEST solution, or am
I opening myself up for problems? Thanks

Unless there's special information that has to be stored for the Lead or
for the Assistant, there's no need to create secondary tables tblLead
and tblAssistant.

 

[Douglas J. Steele]

That would seem to be the most appropriate solution.

 

[Dirk Goldgar]

Why not? Or rather, why not have *two* such relationships -- one to
the Lead field in the Projects table, and one to the Assistant field?
Each of these fields could store an EmployeeID. In the Relationships
window, you can add the tblProjects table twice -- the second time it
will be called tblProjects_1, but that doesn't matter. Add the table
twice, then connect the Lead field to the EmployeeID field in
tblProjects, and the Assistant field to the EmployeeID in
tblProjects_1.

Oops, I got that backward. It's the tblEmployees table you'd be adding
twice; once for each related field in the tblProjects. I'm sorry if I
confused you.

 

[Brandon]

-----Original Message-----


Why not? Or rather, why not have *two* such relationships -- one to the
Lead field in the Projects table, and one to the Assistant field? Each
of these fields could store an EmployeeID. In the Relationships window,
you can add the tblProjects table twice -- the second time it will be
called tblProjects_1, but that doesn't matter. Add the table twice,
then connect the Lead field to the EmployeeID field in tblProjects, and
the Assistant field to the EmployeeID in tblProjects_1.

So I did what you suggested in the relationships. Then I
pulled tblProject and tblEmployee into a query. It
automatically draws a one-to-many to both fields in
tblProject from tblEmployee. Should I delete one of the
relationships and then add the table again to reflect what
the relationships show?
Then, I take it, in every query I run, I will have to do
this step to get both names to display properly.
Also, Doug Steele seemed to like my original idea just
fine. However, this solution is much easier and I have
fewer tables. Are there any disadvantages to building it
this way and can you briefly explain how my queries should
work as far as pulling in project leads and assistants
using a copy of the table in the relationship? I assume
this must be considered a sound practice since Access lets
you bring in copies of the table in the first place.
Thanks so much for your time.

 

[Dirk Goldgar]

So I did what you suggested in the relationships. Then I
pulled tblProject and tblEmployee into a query. It
automatically draws a one-to-many to both fields in
tblProject from tblEmployee. Should I delete one of the
relationships and then add the table again to reflect what
the relationships show?

Yes, you must. Note that, in the query designer, those lines represent
joins that will be carried out in the query, and if you have the same
employee joined to both the project lead and the project assistant, that
would be tantamount to saying that the employee is both the lead and the
assistant. So you have to have two copies of tblEmployee in the query,
each copy joined to one of the two fields in tblProject.

Then, I take it, in every query I run, I will have to do
this step to get both names to display properly.

Yes, that's right -- although you could store one basic query that does
theses join and selects the desired fields from all the tables,
assigning them suitable aliases, and then base all sorts of subsequent
queries on that basic query. That would save yourself having to worry
about setting up the joins properly over and over again.

Also, Doug Steele seemed to like my original idea just
fine. However, this solution is much easier and I have
fewer tables. Are there any disadvantages to building it
this way [...]

You can certainly do it the way you originally described; it's just
that, to me, it seems you would be adding tables to your design
unnecessarily, based on the information you've given so far. Doug may
be looking ahead to the possibility that you will need to store special
information that pertains only to project leads, or to project
assistants. For example, if you need to store the date on which someone
becomes the project lead -- and especially if you need to keep track of
different project leads over the lifetime of the project, and know "who
was the lead when" -- then you need a table to store that information
and a tblLead would be the only logical place, as each record in that
table represents a "lead" relationship between an employee and a
project.

My suggestion is based on the assumption that you don't -- and won't --
need to store that kind of information. In that case, I believe the
structure I suggested is simpler and more efficient. However, Doug may
well have some other reason for not suggesting it, that I have
overlooked; if so, I'll leave it for him to explain.

[...] and can you briefly explain how my queries should
work as far as pulling in project leads and assistants
using a copy of the table in the relationship?

I think I've done that, above. Let me know if it isn't clear.

I assume
this must be considered a sound practice since Access lets
you bring in copies of the table in the first place.

Yes, the extra copies of the table that appear in these design windows
are necessitated by the graphical representation of the queries and
relationships. You aren't actually creating a second copy of anything,
in any physical sense.

 

[Douglas J. Steele]

Also, Doug Steele seemed to like my original idea just
fine. However, this solution is much easier and I have
fewer tables. Are there any disadvantages to building it
this way [...]

You can certainly do it the way you originally described; it's just
that, to me, it seems you would be adding tables to your design
unnecessarily, based on the information you've given so far. Doug may
be looking ahead to the possibility that you will need to store special
information that pertains only to project leads, or to project
assistants. For example, if you need to store the date on which someone
becomes the project lead -- and especially if you need to keep track of
different project leads over the lifetime of the project, and know "who
was the lead when" -- then you need a table to store that information
and a tblLead would be the only logical place, as each record in that
table represents a "lead" relationship between an employee and a
project.

My suggestion is based on the assumption that you don't -- and won't --
need to store that kind of information. In that case, I believe the
structure I suggested is simpler and more efficient. However, Doug may
well have some other reason for not suggesting it, that I have
overlooked; if so, I'll leave it for him to explain.

Even if the relationships are one-to-many now, I always like allowing for
the possibility of time making them many-to-many, as Dirk alluded to. It's
just second nature to me, so I was remiss in not mentioning that. Also, it
might be possible that someone has to serve as both project lead and project
assistant for a single project. (Yeah, I know that's a stretch!)

To be honest, I started posting a response suggesting exactly what Dirk
suggested (and if I hadn't changed my mind, mine would have been the first
response! <g>)

 

[James Hahn]

Creating a second copy of the employee table in Relationships won't work,
because Access optimizes the relationship and redraws both links to the
original table. Instead, create a query which is the employee table. Name
it with the _1 variation. Add it to the relationships table, and create the
links how you want them.
--

 

[Dirk Goldgar]

Creating a second copy of the employee table in Relationships won't
work, because Access optimizes the relationship and redraws both
links to the original table. Instead, create a query which is the
employee table. Name it with the _1 variation. Add it to the
relationships table, and create the links how you want them.

I'm not sure what you mean. I'm looking at it in my Relationships
window right now.

 

[Dirk Goldgar]

message [...]

Also, it might be possible that someone has to serve as both
project lead and project assistant for a single project.

I don't see a problem with that, in either design.

 

[Douglas J. Steele]

message [...]

Also, it might be possible that someone has to serve as both
project lead and project assistant for a single project.

I don't see a problem with that, in either design.

You are, as usual, correct wrt to your design (it was implicit in mine...).

I plead lack of sleep: I'm in a hotel in Dallas this week (and next), and
I'm waking up at 2:30 or 3:00 each morning.

 

[Dirk Goldgar]

I plead lack of sleep: I'm in a hotel in Dallas this week (and next),
and I'm waking up at 2:30 or 3:00 each morning.

You have my sympathy. You're waking up about the time I'm going to bed.

 

[James Hahn]

Did you try it to see? You can add the employee table as many times as you
like to a query and ACCESS will ignore the relationship you set up and
create links only to the first instance of the employee table. Although you
can have multiple copies of a table in the relationship diagram, the
additional relationships are ignored ('optimized' is one way of putting it).
Create the 'dummy' query so that you can use it in the relationship table
and use it in the query, and the relationships will be honoured.

 

[Brandon]

You answered my question perfectly! and no, we do not plan
on storing any information specific to the lead and/or
assistant, so i prefer to go the simpler way.
One final question for then before my database will be
humming thanks to the MVPs!
1. I have my project table and each project can be
assigned a subcategory, which is part of a category
(tblCategory has a one to many to the subcategory, which
has a one-to-many to the project table). For instance a
main category is Administrative Expenses, with
subcategories of Travel Expenses, IT Expenses and Other
Expenses. Another main category is Direct Materials (i.e.
Materials we use to manufacture product) with several
subcategories as well.
Each Project can also be assigned a project type, a yes/no
field which designates whether it is a reverse auction or
not.
I want to have a report which lists each individual record
for some of the project types, like Transportation and
Direct Materials, but then list the sum of the project
records for categories like Administrative Expenses. The
kicker is that I would also want to split out the auction
projects, so my report would look something like this

Individual Project Name Direct Materials Savings
Amount Auction-Yes
Individual Project Name Direct Materials Savings
Amount Auction-Yes
"Sum of Projects" Admin Expenses Sum of
Savings Auction-Yes
"Sum of Projects" Admin Expenses Sum of
Savings Auction-No
Individual Project Name Transportation Savings
Amount Auction-No
"Sum of Projects" Energy
Sum of Savings Auction-Both

So I am showing that I could want to break out the auction
savings for admin expenses between yes and no, but for
Energy I would want all projects in one line regardless of
auction being yes or no. I also wonder what would be
displayed in the Individual Project Name field for the
summarized entries.
My initial thought on how to do this would be that I need
to create several queries that return one line for each of
the things I would like to summarize and then add it to a
main query of the project table that excludes the things I
put in my other queries. But my question is, what field
on the project table would I join my other queries to so
that my individual and summary records display once?
Am I clear at all in what I'm trying to do or can I
explain further? Thanks!

 

[Brandon]

James,
I see your point, it's easier to create the query and Join
it in the relationships, but what concerns me is that I
cannot enforce referential integrity by adding a query to
the relationships window. That option is grayed out.
Will that hurt me at all?
Brandon

 

[Dirk Goldgar]

Did you try it to see? You can add the employee table as many times
as you like to a query and ACCESS will ignore the relationship you
set up and create links only to the first instance of the employee
table. Although you can have multiple copies of a table in the
relationship diagram, the additional relationships are ignored
('optimized' is one way of putting it). Create the 'dummy' query so
that you can use it in the relationship table and use it in the
query, and the relationships will be honoured.

Yes, of course I tried it. In the Relationships window I have the
Projects table, Employees, and Employees_1, with enforced relationships
shown between Projects.Lead and Employees.EmployeeID, and between
Projects.Assistant and Employees_1.EmployeeID. I also have a query to
which I've added Projects, Employees, and Employees_1, and I have join
lines -- *which are not relationship lines* -- between Projects.Lead and
Employees.EmployeeID, and between Projects.Assistant and
Employees_1.EmployeeID. Here's the SQL of the query:

SELECT
Projects.ProjectID,
Employees.LastName AS LeadLN,
Employees.FirstName AS LeadFN,
Employees_1.LastName AS AsstLN,
Employees_1.FirstName AS AsstFN
FROM
Employees
RIGHT JOIN
(Projects
LEFT JOIN
Employees AS Employees_1
ON Projects.Assistant = Employees_1.EmployeeID)
ON Employees.EmployeeID = Projects.Lead;

Yes, I had to correct the join lines initially proposed by Access as I
built the query, because by default the Access query designer attempts
to create joins between every pair of related fields. Maybe that's what
you're talking about. But that's just guessing on Access's part, and we
are by no means obliged to accept it. The joins are not the
relationships, and the relationships are not the joins.

 

[Dirk Goldgar]

One final question for then before my database will be
humming

I'll try to look at this in the morning.

 

[Dirk Goldgar]

You answered my question perfectly! and no, we do not plan
on storing any information specific to the lead and/or
assistant, so i prefer to go the simpler way.
One final question for then before my database will be
humming thanks to the MVPs!
1. I have my project table and each project can be
assigned a subcategory, which is part of a category
(tblCategory has a one to many to the subcategory, which
has a one-to-many to the project table). For instance a
main category is Administrative Expenses, with
subcategories of Travel Expenses, IT Expenses and Other
Expenses. Another main category is Direct Materials (i.e.
Materials we use to manufacture product) with several
subcategories as well.
Each Project can also be assigned a project type, a yes/no
field which designates whether it is a reverse auction or
not.
I want to have a report which lists each individual record
for some of the project types, like Transportation and
Direct Materials, but then list the sum of the project
records for categories like Administrative Expenses. The
kicker is that I would also want to split out the auction
projects, so my report would look something like this

Individual Project Name Direct Materials Savings
Amount Auction-Yes
Individual Project Name Direct Materials Savings
Amount Auction-Yes
"Sum of Projects" Admin Expenses Sum of
Savings Auction-Yes
"Sum of Projects" Admin Expenses Sum of
Savings Auction-No
Individual Project Name Transportation Savings
Amount Auction-No
"Sum of Projects" Energy
Sum of Savings Auction-Both

So I am showing that I could want to break out the auction
savings for admin expenses between yes and no, but for
Energy I would want all projects in one line regardless of
auction being yes or no. I also wonder what would be
displayed in the Individual Project Name field for the
summarized entries.
My initial thought on how to do this would be that I need
to create several queries that return one line for each of
the things I would like to summarize and then add it to a
main query of the project table that excludes the things I
put in my other queries. But my question is, what field
on the project table would I join my other queries to so
that my individual and summary records display once?
Am I clear at all in what I'm trying to do or can I
explain further? Thanks!

Sorry it took me a few days to get back to this.

If I understand you correctly, your question boils down to a decision,
as the report is formatted, whether to print the detail section or not.
I think I would I would handle this using code in the Format event of
the report's Detail section. You'd have to ensure that the report's
recordsource query contains the fields necessary to make that
determination; given that, you could have an event procedure along thse
lines:

'---- start of example code ----
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me!ProjectCategoryShowDetail = True _
Or Me!ProjectAuction = True _
Then
Cancel = True
End If

End Sub
'---- end of example code ----