Noob problem: query and multiple tables

[PaulCW]

Many moons ago I did simple flat file databases with success. Now I'm working with Access 2010 on what should be a fairly simple task and have hit a snag. While the actual database has more fields and records, I've created a simplified version that shows my problem:



I generate 3 tables: Contacts, WorkEvents and PlayEvents. Using Access's wizards, I generate individual simple queries for each table and I can input data. I generate a simple query for Contacts and either WorkEvents or PlayEvents and I can enter data. If I generate a query that includes Contacts, WorkEvents and Playevents, no data shows and I can't enter data. What am I doing wrong here?

And for a given person I'll need to be able to enter multiple event records.

I've Googled, checked tutorials, a book... I'm missing something here. Thanks in advance for your help.
Paul

[June7]

First of all, I would have Work and Play events in one table, not two. Have another field to indicate if the event is work or play. Use this field to filter records by this category. Because of the many-to-many relationships, these tables will not join nicely in one query. Try this and you might get my point:
SELECT Contacts.ID_Contacts AS Contacts_ID_Contacts, Contacts.LastName, Contacts.FirstName, Contacts.Email, PlayEvents.ID_PlayEvent, PlayEvents.ID_Contacts AS PlayEvent_ID_Contacts, PlayEvents.PEvent_Date, PlayEvents.PEvent_Name, PlayEvents.PEvent_Note, WorkEvents.ID_WorkEvents, WorkEvents.ID_Contacts AS WorkEvents_ID_Contacts, WorkEvents.WEvent_Date, WorkEvents.WEvent_Name, WorkEvents.WEvent_Note
FROM (Contacts LEFT JOIN WorkEvents ON Contacts.[ID_Contacts] = WorkEvents.[ID_Contacts]) LEFT JOIN PlayEvents ON Contacts.ID_Contacts = PlayEvents.ID_Contacts;

You need another table, a 'junction' that will relate Contacts with Events attended.
Attendance
ContactID (foreign key)
EventID (foreign key)

[PaulCW]

Thanks for the suggestion of the junction table. Now I can span tables.

Re consolidating the events tables, sorry that I wasn't clear. The actual database is much more complex- the simple database here demonstrated the problem.

2 questions remain:
1. If I pull up the junction query, I can enter data so long as data goes to all the tables. If I ignore a table, Access balks, saying it can not find a record in the left out table. How does one enter partial information here?

2. How do I handle multiple entries? In this case, one person with multiple WorkEvent entries?

The attached database is revised to include the junction table.

Again, thanks for the help. I've been banging my head off and on on this problem for weeks.
Paul

[June7]

That still isn't proper data structure. A record in the junction table should be for either work or play, not both. You will continue to bang your head if you don't get data structure correct.

Proper design would be:

table for Contacts
ContactID (primary key)
ContactName
ContactAddress
ContactPhone
etc

table for Events
EventID (primary key)
EventName
EventDate
EventType (work, play)

table for Attendance
ContactID (foreign key)
EventID (foreign key)

If there is more to the project, you should post the full project for analysis. Review the 'sticky' tutorial threads here http://forums.aspfree.com/microsoft-access-help-18/

[PaulCW]

Thanks for your input.

So how would this work:
The question being answered is What Are the Critcial Events in a person's life.

We look at three general categories
Growing up
Personal
Work

To make the database easy to use the three categories each have look up tables with 15-20 options.

Next is date of the occurence, a 1-10 look up table describing the impact, and finally a memo field for details.

Would you recommend something like this:
OPTION A
Contacts
ID_Contacts
name
address
etc
Critical Events
ID_Critical Events
ID_Contacts
Category (Lookup: Growing up, Work, Personal)
Cat-Growing (Lookup to separate table with 15-20 options)
Cat-Personal (Lookup to separate table with 15-20 options)
Cat-Work (Lookup to separate table with 15-20 options)
CritEvents_Impact (1-10 look up table)
CritEvents_Date
CritEvents_Memo


or this:
OPTION B
Contacts
ID_Contacts
name
address
etc
Critical_Events_Growing up
ID_Critical_Events_Growing_Up
ID_Contacts
Cat-Growing (Lookup to separate table with 15-20 options)
CritEvents_Impact (1-10 look up table)
CritEvents_Date
CritEvents_Memo
Critical_Events_Personal
ID_Critical_Events_Personal
ID_Contacts
Cat-Personal (Lookup to separate table with 15-20 options)
CritEvents_Impact (1-10 look up table)
CritEvents_Date
CritEvents_Memo
Critical_Events_Work
ID_Critical_Events_Work
ID_Contacts
Cat-Work (Lookup to separate table with 15-20 options)
CritEvents_Impact (1-10 look up table)
CritEvents_Date
CritEvents_Memo

From my research, normalization says one fact per table with no empty fields. Option A is more compact, but arguably has more than one fact and two fields that will be empty for any given record (2 of the 3 lookups for growing, personal and work). Option B looks to this untrained eye as more normalized, but then I have the problem of spanning tables.

So which of the two would you recommend, or is there an option "c"?

Again, thanks for your expertise. Much appreciated.
Paul

[June7]

Understand better now. I originally thought of events as conferences, training, workshops, meetings; although the 'play' category seemed odd for that scenario.

Multiple tables with same field structure is sign of improper design. If you wanted one report to show contacts' Growing/Personal/Work events, would have to UNION the tables or have 3 subreports on a main report. UNION essentially results in a structure like the one table I recommend you start with. Filtering is easier than UNION.

Contacts
ID_Contacts
name
address
etc
Critical Events
ID_Critical Events
ID_Contacts
Category (Growing, Work, Personal - doubt a lookup table is needed)
Cat (Lookup to separate table with 15-20 options)
CritEvents_Impact (1-10 look up table)
CritEvents_Date
CritEvents_Memo

I don't know what the data in the Cat field is about; however, I recommend one lookup table for whatever this is. If the choices vary depending on the Category, have a field in the lookup to identify that. Then on form can restrict the choices presented to user depending on what Category is selected. This is dependent or cascading combo/list boxes. Again, that's what filter criteria is for. Same goes for Impact if the choices are dependent on Category. If any options are same for more than one Category, that can be dealt with.

[PaulCW]

Thanks for the great feedback. Thanks especially for the info that look up tables can be filtered. In this example I'm thinking the table could well have 30-40 entries split into three groups. Am now researching how that works. That would simplify matters greatly.
Again, your help is much appreciated,
Paul