Assigning Primary Keys

[Tony Williams]

I have a table that I want to make two fields Primary Keys. However when I
try to close the design view of the table I get a message that says that
this action would create duplicate values yet I have set the index to accept
duplicate values. What can I do?
Thanks
Tony

 

[BruceM]

Primary keys are by definition unique, so they do not accept duplicate
values. What do you hope to do?

 

[Tony Williams]

I wanted to use them in relationships to ensure that the data from one table
matched the equivalent data in another table. Is that OK?
Tony

 

[BruceM]

In non-database terms, what is your objective? Why do you wish to compare
two tables? Are you comparing the entire record, or one field? There isn't
enough information in your question for me (or anybody else, I suspect) to
come up with a targeted suggestion.
All I can say based on the limited information provided is that matching
records suggests duplication, and that using a relationship to check for
matching records is almost certainly not the way to proceed.

 

[Tony Williams]

Sorry to be so vague Bruce. Maybe I don't need the primary keys? What I want
to do is I have three tables, one has company information, one has date
information and the third has numeric data for each company and for each of
the dates in the date table. I want to make sure that when I produce a form
or a report then the numeric data applies to the correct company and for the
correct date. Is that any help?
Tony

 

[BruceM]

You should have a table for company information, as you do. This should be
for company name, address, phone, etc. It needs a primary key field.
Autonumber is fine for that. What do you mean when you say you have a table
for date information? Do you mean that at intervals you make a new record
containing financial or other numeric data?
Here is a suggested layout based on some guesswork. Tables start with the
prefix tbl. Fields are listed below the table names.

tblCompany
CompanyID (primary key, or PK)
CompanyName
Address, etc.

tblCompanyReports
ReportID (PK)
CompanyID (foreign key, or FK)
ReportDate

tblReportDetails
DetailID (PK)
ReportID (FK)
Money stuff, or whatever

This assumes that there are periodic reports, and that the reports contain a
varying number of details. There is a one-to-many relationship between the
PK fields and their namesake FK fields in other tables. Note that if the PK
is Autonumber, the FK must be Number.
Build a form (frmCompany) based on tblCompany, with a subform
(fsubCompanyReports) based on tblCompanyReports. fsubCompany reports will
have a subform based on tblReportDetails. Because of the relationships you
established, each subform record "belongs" to a single record in the main
form. The FK field is populated automatically.
If this is close, provide some more details. If not, explain what you mean
by a date table and by "numeric information".

 

[Tony Williams]

Yes Bruce that's close to what I'm doing. I'm not sure about the "foreign
key" Is that created in the same way without Autonumber? I have tried
creating two keys but still don't understand why I get a message about
duplicate data when I do when I have set the index to allow for duplicates?
Any ideas?
Tony

 

[BruceM]

Forget about tblReportDetails for the moment. Open tblCompanyReports in
design view. If it does not have a CompanyID field, create the field. Set
its Data Type to Number. Save and close tblCompanyReports. Click Tools >
Relationships. Add tblCompany and tblCompanyReports to the Relationships
window. Drag CompanyID from one table and drop it on CompanyID in the other
table. In the dialog box that appears, click Enforce Referential Integrity.
tblCompanyReports will probably need to be empty for this to work. Now you
have a one-to-many relationship between the two tables. For each record on
the "one" side there can be any number of records on the "many" side. Each
company may have many reports (weekly or monthly or whatever). There should
be no other relationships for now.
Create frmCompany and fsubCompanyReports as described in the previous post.
For the subform, just create it as you would any other form. Use the wizard
if it makes it easier. Once you are done, save it and close it. Open
frmCompany in design view, and drag the icon for fsubCompanyReports onto it.
Now for each Company you can create any number of records in the Reports
table.
In order to create a Details subform for the Records subform, repeat the
process described above.
The foreign key is a convenient name for the field that is related to the PK
field in another table. It is just another field. You don't define it as a
foreign key field. You can't. It becomes a foreign key when it becomes
part of the relationship.
Allow duplicates everywhere except for the PK field for now. I'm afraid I
can't advise about indexing, as I don't understand it all that well myself.