I have 3 databases. All have one field in common, the contract number. If the contract number is the primary in one table and the foreign in the other two, can I link the primary key in the one table to both of the common foreign key in the others?
I have 3 databases. All have one field in common, the contract number. If the contract number is the primary in one table and the foreign in the other two, can I link the primary key in the one table to both of the common foreign key in the others?
If the contract is common to all tables ,then you can join them in queries.
I interpret you are trying to establish a relationship between tables in different databases - that cannot be done.
The tables are in the same database, just contain different information. BTW, I want to do a "one to one" relationship between the tables but can't seem to figure out how to do that. When I set up the relationship it seems to default to "one to many". What am I doing wrong?
Tell us about your tables in simple business terms (simple, plain English).
Relationships in database are related to "business rules/facts".
Good luck.
Okay, I'll give it a shot. At the outset, I will confess that as a rookie I made one of the tables much larger/more complicated than it needed to be but rather than start from scratch I am trying to work around, so here it goes.
Table 1 contains basic info. Contract number, names, addresses, etc. Contract number there is the primary key. Table 2 is for attachments of documents specific to the contract in table 1. It has only three fields; auto number, contract number and attachments. Contract number there is the foreign key.
Table 3 is where I should have done things differently. The contract number is the foreign key. I wanted to create a table to list any activity (aka "many") associated with each contract by date. In hindsight, I should have created a table with fields for contract number, date and event and then done a "one to many". Instead, being even less adept than I am now, I created a table with a separate field for each date and for each event. Date 1, date 2, date 3, etc. Event 1, event 2, event 3, etc. Lot's of fields, long queries! Unnecessary, I know...now. The bottom line is that since I set it up this way I assume I need to let the tables know one record in the primary table relates to only one record in the foreign tables.
Thanks.
I was able to find the answer. Go to the foreign table(s). Select the foreign key and where it says "indexed" under filed properties change to Yes (No Duplicates). That will then allow a one-to-one to be created.
Thank you all.
Patrick,
I recommend you take a little diversion-- 45-60 minutes and work through 1 or 2 of these tutorials from RogersAccessLibrary.
Class info system
Catering Business
Widgets
Work through the tutorial - you'll learn by experiencing a process that will work with any database.
There is more info at this link re Database Planning and Design.
It sounds to me you have a Contract (probably between your Company and a Client/Supplier..) and you store related information about each Contract.
Client-->Contract-->RelatedInfo
which represents these facts:
there are 1 or more Clients
Each client can have 0,1 or more Contracts
Each contract can have 0,1 or more attachments.
Thanks. Will do.