UtterAccess.com
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    
 
   
DeborahSV
post Jan 22 2018, 11:37 AM
Post#1



Posts: 99
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
 
ranman256
post Jan 22 2018, 12:18 PM
Post#2



Posts: 861
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
 
DeborahSV
post Jan 22 2018, 01:42 PM
Post#3



Posts: 99
Joined: 14-September 10



Thanks.
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
Post#4



Posts: 5,424
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.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Jan 22 2018, 03:22 PM
Post#5


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


HiDeborah

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


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DeborahSV
post Jan 23 2018, 12:48 AM
Post#6



Posts: 99
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    23rd June 2018 - 09:10 AM