X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Create Additional Tables Or Include Data In Existing Tables, Access 2007    
post Jan 22 2018, 11:37 AM

Posts: 104
Joined: 14-September 10

I have a database for planning joint field trips for many summer camps. One table is the "Camp" table, and it is in numerous 1-to-many relationships, including (for the purposes of this question) a "Conversation" table (notes on all phone conversations) and "Contact" table. The Contact table has 1-to-many relationships with ContactAddress and ContactPhone. This database has been used for about 7 years successfully with no problems. Now we want to include a transportation component, i.e. not only arrange and record trips but also the bussing to get there. We have a table "BusCompany", and we need to record Conversations, Contacts, ContactAddresses, and ContactPhones for the BusCompanies just as we do for the Camps. Should I create four additional tables (BusConversations, BusContacts, etc.), or should I add a foreign key for the bus company to those existing tables (which would mean that in any given record either ...CampID or ...BusCompanyID would be blank)? Somehow the latter doesn't seem right, but the former also seems redundant. Or is there another way to go about this? I've been reading tutorials on database design and studied some models, but I can't seem to find a model for this.
Go to the top of the page
post Jan 22 2018, 12:18 PM

Posts: 883
Joined: 25-April 14

you already have a contact table. Keep it and put bus people in it. They are contacts with maybe a 'bus' ContactType.
and youd have tBuses table if you are tracking the bus# etc.

Go to the top of the page
post Jan 22 2018, 01:42 PM

Posts: 104
Joined: 14-September 10

I need to know who each contact "belongs" to. So currently I have a field in the contact table ContactCampID. Do I make a separate field ContactBusCompanyID or do I put the BusCompanyID in the ContactCampID field and have a separate field that tells me whether this is a camp contact or a bus contact?
Go to the top of the page
tina t
post Jan 22 2018, 01:48 PM

Posts: 5,618
Joined: 11-November 10
From: SoCal, USA

pls list the fields in the bus company table, and the fields in the camp table. explain any field where the fieldname does not make the use apparent to people who are not familiar with the setup.

Go to the top of the page
post Jan 22 2018, 03:22 PM

UtterAccess VIP
Posts: 12,915
Joined: 6-June 05
From: Dunbar,Scotland


I think what ranman means is that you need a field in your Contacts table named "ContactTypeID"

Then you need another table named "tbluContactTypes" which would list all of the ContactTypes required ie Main Contact, Camp Contact & Bus Contact.

You would then link tblContacts to tbluContactTypes using ContactTypeID

Go to the top of the page
post Jan 23 2018, 12:48 AM

Posts: 104
Joined: 14-September 10

Thank you all for responding. I'm working on this "in spare time" so I don't always get to check the answers right away.
If I understand this correctly, then I now have a new problem. I already have many reports and forms that use tblContact. So if I'm adding a field "ContactTypeID" to allow the possibility of camp contacts or bus contacts, I need to go to all those existing places and add this field and its lookup table so I can set criteria to only show camp contacts. (Because there will certainly be overlap between the PK's of the camp table and the buscompany table - they are both autonumbers that start at 1.) This is not really a "problem", just a "nuisance". But is this the right way to go? Also, currently the field that joins tblCamp to tblContact is ContactCampID. Would it be correct to rename it something more generic like "ContactAssociationID" to include tblCamp, tblBusCompany, or anything else that might come up? That's also a headache. Is it recommended?
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th December 2018 - 02:08 PM