adding and deleting to another table(s)

[johnqzsmith]

I am struggling with a solution to this problem involving maintaining a calendar or central list of Events.

I have 5-6 "Event" tables ( e.g., Social, Meetings, Programs, etc.) each of which I would like to take the date and time from, along with a brief description, and add those fields to a simple Calendar (EventsList) table. Conversely, if the event is cancelled -- done through the particular event form and table (once again: Social, Meetings, etc.) -- I would like to have the corresponding record in the Calendar (EventList) table be deleted as well. Note that each of the Event tables are different from each other.

I didn't think it would be that difficult, but find myself struggling with this beyond a reasonable time. Any help in accomplishing this or "kick-starting" me would be greatly appreciated.

Thanks in advance from a brand new member.
JohnQ

 

[neileg]

I don't think your design is normalised. Why do you have diferent tables, why not just one table with a field to indicate which type of event each record represents?

 

[johnqzsmith]

not normalized

I don't think your design is normalised. Why do you have diferent tables, why not just one table with a field to indicate which type of event each record represents?

Neileg:
The different event tables were constructed because, aside from dates and times, the events all have different characteristics (fields). The fields of the tables Programs, Meetings, Social et al are significantly different, reflecting the different characteristics of each event.
I had thought about keeping all events in one table originally but the diversity of each event kept me from doing so. I haven't cast that idea aside entirely yet, but I don't see it as the main option at present.
Perhaps there is yet something I am missing and will eventually have to go that route. I appreciate your suggestion and may have to re-think it again.
Gratefully,
JohnQ

 

[neileg]

OK, I've not a lot to go on but I suspect you are thinking about this in spreadsheet terms and not as a relational database. You think you need separate tables because that's the only way you could do it in Excel.

You really need to keep the key data about each event in one table so that you can manage the calendar requirements. Deatiled data should be held in a separate table. You will need to be able to restrict the type of detail according to the type of event. This might be a start to your structure

tblEvent Holds main data about each event
EventID Autonumber PK
EventDescription
EventDate
EventType FK link to tblEventType

tblEventType Holds an ID for each type of event you have
EventTypeID Autonumber PK
EventType

tblEventDetail Holds the detailed data for each event. One record per detail
EventDetailID Autonumber PK
EventID FK link to tblEvent
DetailID FK link to tblDetailType
DetailNotes
DetailQty
DetailCost

tblDetailType Categorises what type of detail you need to hold
DetailTypeID AutonumberPK
DetailTypeDesc

tblEventDetailJunction Links what type of detail applies to which event type
EventTypeID FK link to tblEventType
DetailTypeID FK link to tblDetailType

 

[johnqzsmith]

Thanks fr your well thought out answer. It happens yesterday I had a day of outdoor projects that gave me a chace to think about your previous reply. I now have to agree with you and will concentrate on that solution today.

With your last reply (attached) it gives me a little more to think about. It seems to add a table or two more than I thought I would need. I examine this approach and report back to you later.

Thanks again for the great helping hand comments.
Gratefully,
JohnQ

OK, I've not a lot to go on but I suspect you are thinking about this in spreadsheet terms and not as a relational database. You think you need separate tables because that's the only way you could do it in Excel.

You really need to keep the key data about each event in one table so that you can manage the calendar requirements. Deatiled data should be held in a separate table. You will need to be able to restrict the type of detail according to the type of event. This might be a start to your structure

tblEvent Holds main data about each event
EventID Autonumber PK
EventDescription
EventDate
EventType FK link to tblEventType

tblEventType Holds an ID for each type of event you have
EventTypeID Autonumber PK
EventType

tblEventDetail Holds the detailed data for each event. One record per detail
EventDetailID Autonumber PK
EventID FK link to tblEvent
DetailID FK link to tblDetailType
DetailNotes
DetailQty
DetailCost

tblDetailType Categorises what type of detail you need to hold
DetailTypeID AutonumberPK
DetailTypeDesc

tblEventDetailJunction Links what type of detail applies to which event type
EventTypeID FK link to tblEventType
DetailTypeID FK link to tblDetailType

 

[johnqzsmith]

Events; referential integrity

Neil:
I'm trying to understand the psuedo-solution you proposed to me as shown below. I am doing fine with tblEvent, tblEventType, tblEventDetail. I am a little confused however with tblDetailType and tblEventDetailJunction. I recognize that this is meant to provide different levels of detail to different events but not exactly sure how it does.
I will prepare some test tables to send to you, if you don't mind. Perhaps that will help. I'll send these along in a few minutes after sending this.
I went back and refreshed myself on the concept of referential integrity. Is referntial integrity enforced in your table layout example?
Once again, thank you,
--JQ

OK, I've not a lot to go on but I suspect you are thinking about this in spreadsheet terms and not as a relational database. You think you need separate tables because that's the only way you could do it in Excel.

You really need to keep the key data about each event in one table so that you can manage the calendar requirements. Deatiled data should be held in a separate table. You will need to be able to restrict the type of detail according to the type of event. This might be a start to your structure

tblEvent Holds main data about each event
EventID Autonumber PK
EventDescription
EventDate
EventType FK link to tblEventType

tblEventType Holds an ID for each type of event you have
EventTypeID Autonumber PK
EventType

tblEventDetail Holds the detailed data for each event. One record per detail
EventDetailID Autonumber PK
EventID FK link to tblEvent
DetailID FK link to tblDetailType
DetailNotes
DetailQty
DetailCost

tblDetailType Categorises what type of detail you need to hold
DetailTypeID AutonumberPK
DetailTypeDesc

tblEventDetailJunction Links what type of detail applies to which event type
EventTypeID FK link to tblEventType
DetailTypeID FK link to tblDetailType

 

[Mile-O]

John, is there any chance that, if you don't intend to quote another forum user, that you can hit the Post Reply button at the bottom left of the thread rather than the Reply button within another user's post? Thanks.

 

[johnqzsmith]

Calendar/Referential Integrity

I am attaching a sample DB to view in hopes of conquering the problem first posed in this thread.
Again, my intent is to post all items to my calendar (from tbl Events). The calendar does not allow room for much, so only a short description of the event and the time will be posted to calendar.
The rest of the proposed tbls will assist with entering detail about each event. Different event types can use their own forms to input data and will link to their own reports. Each event is very diverse and requires significantly different fields from each other.
Neileg has been extremely helpful and proposed a solution that I don't 100% understand.
Anyway, I am plugging away at making this calendar work alongside the other (expanded) event forms and reports.
I appreciate the fine assistance I have gotten from Neileg and hope to conquer this issue shortly. Thanks for a fine forum, folks!
--JohnQ

 

[neileg]

Sorry, very busy. Will try and respond next week.

 

[neileg]

Do you still need help? I can't open your file. I use Access 2000.

 

[johnqzsmith]

calendar/referential integrity

Sorry I have not responded sooner, Neileg. Thanks for your interest.
I have spent the last week and a half re-doing my tables. I have added a/n primary keys, re-did some foreign keys, and generally buffed up the "backbone" structure of my application. This has helped me solvethe problem that I had originally written about.

However, one thing remains a bit mysterious to me. I have my program as the front-end with data residing in a back-end .mdb. In constructing the relationship for two main tables I find that I cannot declare them the way I want to in the front -end, but I can in the back-end.

Specifically, I have
tblContacts (a table for all people in and outside of the organization)
ContactID a/n PK

and

tblMembers (member only additional information)
MemberID as a/n PK
ContactID as long, FK to tblContacts.ContactID

In the back-end I can declare a 1:1 relationship (tblContacts:tblMembers) and can enforce R.I. However, I can not do this relationship in the front-end. I had cleared all back-end relationships before trying to establish same in front-end.

Any clues or tips as to what might be happening here? I can find nothing that says there are problems with relationships across linked tables.

Thanks again for your help and your willingness to assist further.

JohnQ

 

[neileg]

To be honest, I don't think I've ever noticed this problem. I readily admit that I'm not a formally trained db programmer and I can be a bit lax in the use of RI. Thinking about this logically, though, I think relationships and RI must be implemented at a table level, ergo, these will exist in the be and not the fe.

If this doesn't convince you, then I'd start a new thread on this subject. I'm sure Pat Hartman would have the answer!