davidatlamont
asked on
How do I link two databases?
I know this should be straightfoward but cannot figure this out. Close, but not quite . . . Don't quite understand the "File References"/ "Relationships" functions.
I have two databases.
1. Events (currently contains about 55 events with each record containing everything from audio/video needs to venues, etc.
2. Contacts (a list of some 18,000 persons, many of which receive and attend events)
To this point these DB's have existed independent of each other with no tracking on who is invited/attends each event.
My goal is the following:
I want to be able to flag those in contact DB that are invited and/or attend an event.
Event DB users will not have any edit access to contact information, only view access; however they will be able to flag which events invited to/or attended.
I'm unclear whether "Event ID" & "Event Name" (Event DB) fields should be linked to contact DB and allow Event DB users access to these fields (from Event DB) or whether 'Contacts' (Contact DB) should be linked to Event DB or a mixture of both. Also, how is the information updated in linked DB (I know this has something to do with relational vs lookup).
Anyway, I hope this is enough to get the idea. I can answer further questions.
Thanks,
David
I have two databases.
1. Events (currently contains about 55 events with each record containing everything from audio/video needs to venues, etc.
2. Contacts (a list of some 18,000 persons, many of which receive and attend events)
To this point these DB's have existed independent of each other with no tracking on who is invited/attends each event.
My goal is the following:
I want to be able to flag those in contact DB that are invited and/or attend an event.
Event DB users will not have any edit access to contact information, only view access; however they will be able to flag which events invited to/or attended.
I'm unclear whether "Event ID" & "Event Name" (Event DB) fields should be linked to contact DB and allow Event DB users access to these fields (from Event DB) or whether 'Contacts' (Contact DB) should be linked to Event DB or a mixture of both. Also, how is the information updated in linked DB (I know this has something to do with relational vs lookup).
Anyway, I hope this is enough to get the idea. I can answer further questions.
Thanks,
David
ASKER
Thanks Lesouef, however I'll need more detail on how to implement.
are you ok with the 3rd table principle and contents?
can you give me the fields you have in the existing tables to let me tell you how to link that 3rd table to the existing stuff,
can you give me the fields you have in the existing tables to let me tell you how to link that 3rd table to the existing stuff,
ASKER
I'm not sure. Key fields in Events DB- Event ID, Event Name
I have a Contat ID field in Contact DB but have not really used to this point. I'm assuming this would be key in 3rd DB?
I have a Contat ID field in Contact DB but have not really used to this point. I'm assuming this would be key in 3rd DB?
ok, so you would create an EventID field in the 3rd table and use it to link it to the events db.
and yes, the contact ID would the right one to do it for the other table. You could do it using names as well, but this is a bit dangerous as you can always expect duplicates on names, so using them for keys is not adequate.
and yes, the contact ID would the right one to do it for the other table. You could do it using names as well, but this is a bit dangerous as you can always expect duplicates on names, so using them for keys is not adequate.
Lesouef, I seem to remember you once posted a link to an example database showing a many-to-many relationship...
Do you still have that?
Do you still have that?
I have kept 34 exmaples, but none looks like that. I don't even remember having done this!
Never mind, it would not apply here, as he will probably deals with several events and several contacts, so a 3rd table is required unless you stack events in an event text field of the contacts database, then seach for events in this field, but that is too dirty to be recommanded I think.
Never mind, it would not apply here, as he will probably deals with several events and several contacts, so a 3rd table is required unless you stack events in an event text field of the contacts database, then seach for events in this field, but that is too dirty to be recommanded I think.
>so a 3rd table is required
That's what I meant by many-to-many relationship, a link table carrying the keys from the two other tables.
That's what I meant by many-to-many relationship, a link table carrying the keys from the two other tables.
ok then! I thought you were using this expression for the new fm7 links which can be multi-fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
- event field (or eventID)
- contact name or ID
- invited flag
- attented flag
to flag who should attend an event can be done in the contacts DB + a portal to contacts::events_contacts
to see who attend/is invited to an event can be done in the events DB + a portal to events::events_contacts
it's getting late for me, I am not sure this is 100% clear, I'll add more to-morrow if you need.