Multiple copies of table in one query design view?

[Cheese_whiz]

Hi all,

I've got an application with what amounts to the main query being based on
two main tables and several other smaller tables. The two main tables are
tblPeople and tblEvents, with the app being built around tblevents.

Because there are three people associated with three events (I guess), there
are three copies of the tblPeople in the query design view: one for each role
a person plays in an event. The first tblperson is joined tblPerson.ID to
tblEvents.Person1 and the second tblpersonCopy2.ID to tblEvents.Person2 and
so on for the third table.

Isn't that wrong? Shouldn't it just be one copy of the tblPersons with
three joins coming from it to the tblEvents and each join being all records
from events with just the matching from Persons.

I'm trying to clean this app up and I tried setting it up with one
tblPersons in that query as described above. The query returns all the
records (like it does when three tables are used), but when I try to switch
the main form over to using the new query (the one with only one copy of
tblPersons in design view) and then open the form, I get an error message
saying it can't go to the specified record.

Well, the specified record is simply:

DoCmd.GoToRecord , , acNewRec

Does the query need three copies of that table to handle three different
joins and if not, is there some reason that line above wouldn't work given
the way I setup the 'new' query (the one with only one copy of tblPersons).

Even if I don't get an answer to the second question it would be invaluable
to know the answer to the first.

As always, TIA,
CW

 

[Cheese_whiz]

Correction:
Paragraph Two: "Because there are three people associated with EACH event....

CW

 

[Michel Walsh]

Sounds really unusual, unless this is a kind of 'backup' that maintenance
did, from previous crash, to be able to analyze the data, when they will
have time... or some other reason like that. But it should not be.


Now, about not being able to move to a new record, that can be due to the
form, or to the query, To know who is likely the problem, open the query in
the query data view, and try to add a record, there! If the query do not
want to add a new record, then the query is either not updateable, either
does not allow the addition of new row (have to be sure the primary key of
the tables are in the SELECT list). On the other hand, if it does allow you
to add a new record, there, then, there is probably some 'evil' VBA code at
work under your form: maybe it is modifying a record, and one of the
validation rule (quantity <0, or relational reference, or whatever) is NOT
respected, so, Access does not allow you to move to another record, the
actual one being modified, but not save-able.



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

If you have three fields in tblEventsthat connect to one field in tblPeople
then you would need three "copies" of tblPeople. The table design is wrong
since someone is trying to model a many to many relationship. There should
be another table involved that would a junction table between the tblEvents
and tblPeople.

tblEventWorkers
EventID
PersonID
Role (possibly needed if the tblEvents fields specify in the name of the
field what this person is doing for the event)

Then your query would join tblEvents to the new table and tblPeople to the
new table.

This will also mean that you will probably have to redesign the form to use
a subform for adding people to an event.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Cheese_whiz]

Thanks for the replies,

Michel:
I looked at the queries (both old and new) and the new one doesn't all
adding new records in datasheet view, but the old one does. I haven't looked
further at correcting it. Thanks for the direction!

John:

It seems to me the original design is attempting to model THREE one-to-many
relationships instead of a many-to-many relationship, but I fully admit I'm
no expert. I've created many-to-many relationships in the past and it never
seemed to be a big deal but they were always one field in one table through
the junction table to one field in another table. This is one field in one
table (tblPeople) to one field in a second table (that's one join), the same
field in tblPeople to ANOTHER field in the second table (second join), and
the same field in the first table to yet ANOTHER field in the second table
(join 3).

I don't know what I'm saying...i'm just still confused. It doesn't look
like any other many-to-many situation I've ever seen and the original setup
(with three copies of the tblPeople in the design view and, if it matters,
three copies in the relationships' window in the app as well) actually works
without any noticeable ramifications NOW. Of course, I want to understand
the current design and, if necessary, bite the bullet and change it now if it
will help with long-term functioning of the app.

Anyway, thanks a lot for the replies.
CW