Company Phone Directory: Showing Relationships

T

TS in FL

I am making a phone directory for my company. It is currently set up showing
information on every employee. For the salespeople, it also shows who their
secretary is. Now I want to make it so that for each secretary, it shows the
salespeople they support. Each secretary can support two or three
salespeople. I created a query showing Employee table, and added another
instance of the Employee table (named Employee_1 by Access), with the
relationship set up to show all records from Employee, and only records from
Employee_1 where SecretaryID=EmployeeID. Surely there is a way to do this in
reverse, so I can show for each secretary, the salespeople's names she
supports. Any suggestions?
 
A

a a r o n . k e m p f

If you use Jet then you're stuck with a single relationships diagram.

Only by upsizing to SQL Server can you have a specific, built in ERD
diagram for a subset of a database
 
B

BruceM

First of all, disregard Aaron. Self-joins are quite workable in Jet (the
database engine that ships with Access), as you have seen. I'm not all that
familiar with the topic, but have you tried changing the join type?
 
A

a a r o n . k e m p f

Please explain to me how you can have a dedicated relationships
diagram for just a handful of tables?
Why don't you uh-- learn a real database.

Everything I say is the truth.

Maybe BruceM should take a class on SQL Server so you can get
certified like me!

-Aaron
 
B

BruceM

You seem to be OT again. I have acknowledged that I am not very familiar
with self-joins, so I cannot advise the OP about the details, but self-joins
are certainly possible with Jet. A groups search should produce more
information on the topic if you need to know more.

message
Please explain to me how you can have a dedicated relationships
diagram for just a handful of tables?
Why don't you uh-- learn a real database.

Everything I say is the truth.

Maybe BruceM should take a class on SQL Server so you can get
certified like me!

-Aaron
 
K

Ken Sheridan

I am making a phone directory for my company.  It is currently set up showing
information on every employee.  For the salespeople, it also shows who their
secretary is.  Now I want to make it so that for each secretary, it shows the
salespeople they support.  Each secretary can support two or three
salespeople.  I created a query showing Employee table, and added another
instance of the Employee table (named Employee_1 by Access), with the
relationship set up to show all records from Employee, and only records from
Employee_1 where SecretaryID=EmployeeID.  Surely there is a way to dothis in
reverse, so I can show for each secretary, the salespeople's names she
supports.  Any suggestions?

To show secretaries only and the salespeople supported by each
secretary an INNER JOIN will suffice, e.g.

SELECT E1.EmployeeID,
E1.Firstname AS SecrataryFirstname,
E1.LastName AS SecretaryLastName,
E1.PhoneNumber As SecretaryPhoneNumber,
E2.FirstName AS SalespersonFirstname,
E2.LastName AS SalespersonLastName,
E2.PhoneNumber As SalespersonPhoneNumber
FROM Employees AS E1 INNER JOIN Employees AS E2
ON E1.EmployeeID = E2.SecretaryID;

That would be fine as the source for a list of secretaries only, but
not as part of a general phone book. One way to do that would be to
use an OUTER JOIN:

SELECT E1.EmployeeID,
E1.Firstname,
E1.LastName,
E1.PhoneNumber,
E2.FirstName AS SalespersonFirstname,
E2.LastName AS SalespersonLastName,
E2.PhoneNumber As SalespersonPhoneNumber
FROM Employees AS E1 LEFT JOIN Employees AS E2
ON E1.EmployeeID = E2.SecretaryID;

These would both return multiple rows per secretary of course. In a
report you'd group by LastName then by FirstName and then by
EmployeeID, putting the names and phone number from E1 in the an
EmployeeID group header and those from E2 in the detail, making sure
the latter and its controls CanShrink. The reason for using the
EmpoyeeID as a header is that you could have two or more employees
with the same name (I once worked with two Maggie Taylors), so this
distinguishes them, but by having the last and first names as the
higher group levels the report is ordered by name.

Personally I'd adopt a different approach, which would work for both a
form or report, and include a subform or subreport in the form or
report to show the salespeople supported by each secretary. The main
form or report would be based on the Employees table (or in the case
of a form on a sorted query on the table); the subform or subreport
would be based on a query which returns only those employees supported
by secretaries:

SELECT *
FROM Employees
WHERE SecretaryID IS NOT NULL;

The subform or subreport would be linked to the main form or report by
having EmployeeID as its LinkMasterFields property and SecretaryID as
its LinkChildFields property. Set a subform's AllowAdditions and
AllowDeletions properties to False and for each control in it set the
Enabled property to False and the Locked property to True. Otherwise
it would be far too easy for users to inadvertently alter data or even
terminate an employee with extreme prejudice (well, maybe not quite
that, but they could possibly delete their record), thinking that they
are merely removing someone from a secretary's list of staff
supported. For that the SecretaryID column of the relevant employee
row should be made Null.

Ken Sheridan
Stafford, England
 
L

Larry Linson

a a r o n . k e m p f @ g m a i l . c o m said:
If you use Jet then you're stuck with a
single relationships diagram.

What are you talking about? There are several ways to define and show these
relationships and diagrams for each.
Only by upsizing to SQL Server can you
have a specific, built in ERD diagram for
a subset of a database

Bafflegab?
 
T

TS in FL

Wow! Thank you so much for your time, Ken! I will try this out and see what
happens. I appreciate your clear, concise instruction.

____________________________________________
 
A

a a r o n . k e m p f

so what you're saying is that you can have multiple 'Relationships
Diagrams' in Jet?

I don't see what you're talking about, I only see one Relationships
window.

In SQL Server, we have the Diagrams tab, so we can have 10 diagrams if
we need to.
Imagine that-- ERD tools that were built into the database, and always
in sync, automagically?

Sounds to good to be true?

With SQL Server it's an everyday practice.
Meanwhile this guy needs to build an ERD diagram in Jet and he gets
confused, because it only has space for one diagram.

and you blame this on me??
 
T

Tony Toews [MVP]

Please explain to me how you can have a dedicated relationships
diagram for just a handful of tables?

It's called the "relationships window".

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
A

a a r o n . k e m p f

Bob;

Maybe you should take a class on SQL Server so you can be a useful
contributor to this group.
Again- sQL Server is the worlds most popular database
 
A

a a r o n . k e m p f

again, you can have one diagram for the whole database.

not one diagram for dedicated areas of a schema

what a joke. this diagrams limit is worse than the 1gb limit; or
should I say 25mb limit?
 
G

George Hepworth

Aaron, you do realize, do you not, that "automagically" is a made up word?
Nope, it is not a real word, although lots of folks who don't really
understand complex topics like to use it to account for things that just
"happen as if by magic". It's not magic, dude. It's software.



message
so what you're saying is that you can have multiple 'Relationships
Diagrams' in Jet?

I don't see what you're talking about, I only see one Relationships
window.

In SQL Server, we have the Diagrams tab, so we can have 10 diagrams if
we need to.
Imagine that-- ERD tools that were built into the database, and always
in sync, automagically?

Sounds to good to be true?

With SQL Server it's an everyday practice.
Meanwhile this guy needs to build an ERD diagram in Jet and he gets
confused, because it only has space for one diagram.

and you blame this on me??
 
B

BruceM

Every query can have its own relationship diagram. Works for me.

message
again, you can have one diagram for the whole database.

not one diagram for dedicated areas of a schema

what a joke. this diagrams limit is worse than the 1gb limit; or
should I say 25mb limit?
 
L

Larry Linson

Entity Relationships Diagramming is data modeling, not table documentation.
If you don't know the difference between modeling items of information via
ERD and documenting Table Relationships, you need something more basic than
the training for the Certification you claim to have, but which you seem
unable to provide a link to confirm.

The original poster asked about Table Relationships, not ERD.

Again your misunderstanding of something so basic helps us understand why
you don't dare participate in the SQL Server newsgroups.

Larry Linson
Microsoft Office Access MVP


message
so what you're saying is that you can have multiple 'Relationships
Diagrams' in Jet?

I don't see what you're talking about, I only see one Relationships
window.

In SQL Server, we have the Diagrams tab, so we can have 10 diagrams if
we need to.
Imagine that-- ERD tools that were built into the database, and always
in sync, automagically?

Sounds to good to be true?

With SQL Server it's an everyday practice.
Meanwhile this guy needs to build an ERD diagram in Jet and he gets
confused, because it only has space for one diagram.

and you blame this on me??
 
A

a a r o n . k e m p f

Table Relationships (plus labels available in ADP) is ERD, thanks
dipshit.

You can display all sorts of details in ADP diagrams that are not
available in Jet.

Maybe if you knew how to use an enterprise-ready platform that is the
'worlds most popular database' then maybe just maybe, I would have to
defend my decision to reccomend the worlds most popular database over
an admittedly obsolete database without a future.

You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
You wrecklessly insist that every database person in the world uses
your SHIITTTTTTY database-- just because you're too fucking stupid to
learn a real database.
 
A

a a r o n . k e m p f

you know, it's funny..
_I_AM_NOT_THE_ONE_THAT_INSISTS_THAT_PEOPLE_USE_MY_FAVORITE_DATABASE_.
This distinction goes do the Jet Retards that build applications that
are _THREE_TIERS_MORE_COMPLEX_THAN_THEY_NEED_TO_BE_.

Screw you and your linked tables, you fucking crybaby.
You're the ones that are insisiting that everyone only use jet for
every database-- even though Microsoft insists that Jet is not ready
for prime time.

----------------------------------------------------------------------------------------
Access, however, is not perfect. Performance degrades significantly as
the database size increases. The database is also prone to corruption.
Finally, starting with an Access database has tempted many developers
to do a dangerous thing. Sometimes a single-user application becomes
popular enough that there's a desire for it to be used by multiple
simultaneous users. The temptation is to just move the Access database
file to a network share, copy the application to multiple machines,
and let many users connect simultaneously. Access performance drops
off quickly with multiple users, and it's highly unlikely that an
application that was designed for a single user will work reliably
with concurrent users.

Http://msdn.microsoft.com/en-us/library/aa730870(VS.80).aspx
 
A

a a r o n . k e m p f

dude that's the dumbest thing I've ever heard in my life.

Can you write _LABELS_ under tools, relationships in Jet?

I can with SQL Server / ADP!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top