Hi Mat,
A primary key (PK) is a field that uniquely identifies a record in a table.
A PK cannot be null, and it's value cannot be repeated. A foreign key (FK) is
a PK field from a parent table, without the restrictions of a PK. It can be
null (unless the Required property is set to Yes), and it most certainly
repeats.
I recommend that you study the following four documents to gain a better
understanding of database design in general:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
My preference is to use Autonumber as the PK datatype most of the time. The
corresponding FK datatype must be a long integer, which is the default
fieldsize for a number. Some developers prefer text-based PK/FK. I am not in
that camp. This can be, and has been, argued religiously for many years. I
refer you to two articles that support the surrogate key (autonumber)
argument:
http://www.access.qbuilt.com/html/articles.html (See article # 1)
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
(Read the entire paper, but see the section titled "Use Meaningless Field
for the Key Field").
In general, every table should have a PK. In order to relate tables, you
need to insert a field into the child table that can store the PK value. Open
the Northwind sample that you mentioned earlier. Click on Tools >
Relationships... You should see all of the tables layed out, with
relationships specified between the tables. The PK fields will appear bolded
in relationships view. For example, the CustomerID field in the Customers
table is the primary key. This particular PK is text based. The CustomerID
field in the Orders table is a foreign key. It does not appear bold. The FK
has the "many" side of a one-to-many (1:M) relationship. Thus, if you open
the Orders table, you will see many repeats of the FK value. Actually, what
you'll see in the unmodified Orders table is many repeats of the Customer
name, but the only reason for this is due to a Lookup field defined for the
CustomerID field in the Orders table. If you remove the Lookup field, you'll
see the actual CustomerID values are stored in this field. Many developers
consider tables lookups the creation of the evil one. See the 2nd commandment
here:
http://www.mvps.org/access/tencommandments.htm
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
hey, well, your presumption was correct. I really don't know the difference
between the two. I do know, that you, most of the time, need a primary key in
the table. Are there different types of keys?? and if so, how do you choose
between
thanks
mat
:
Hi Mat,
Do you know if the fields that you saw in the Link Master Fields / Link
Child Fields represent your primary and foreign key fields for the form &
subform? I don't want to be presumptuous, but do you know what a primary key
(PK) and a foreign key (FK) are? If so, is the PK included in the recordset
for the main form, and the FK included in the recordset for the subform?
I suppose another possibility might be if your subform is based on a query
that involves more than one table, and you do not have a join included. In
that case, the query would produce what is known as a Cartesian Product. A
Cartesian Product is the result of multiplying the number of records selected
from one table times the number of records selected from another table.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
hey, thanks for the reply, but i do have a Link Master field / Link child
Setting, and i had a bit of a fiddle with that, but i couldn't seem to get it
to work
i do think that your right though, that it is counting all the sales
Reccords, instead of the actual entering Your Name, Address etc...
Mat
__________________________________________
:
Hi Mat,
Is your subform displaying ALL sales records irregardless of which Receipt
Number is active in the main form? If so, you may be missing the Link Master
Field / Link Child Field setting, which allows related records in a subform
to be displayed for a given parent record.
Open your form in design view. Click on View > Properties to display the
properties dialog, if it is not already displayed. Select the subform. You
should see "Subform/Subreport: NameOfSubform" in the blue title bar of the
properties dialog, where NameOfSubform is the name of your subform. Select
the Data tab. Do you see any entries for Link Child Fields and Link Master
Fields? You will want the primary key fieldname from the recordset for the
main form in the Link Master Field property, and the related foreign key
fieldname in the Link Child Field property.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
hello
I have two tables, and i have two queries off those tables which bring up
information such as Receipt No, Full Name, The Product which you can choose
in a combo box, how much of a particluar quantity you would like, and a
Subtotal Box which is worked out in the query.
I then have a Form and Subform, which are form both queries, which stores
the information into tables.
When ever i enter in a new sale in the form, the Record Navigation Bar for
the Form shows that there are more records than there actually are for a
particular Receipt Number.
I want it to show that there are only the number of records for a particular
Receipt. For example, if there are 5 receipts, then i only want it to tell me
that there are 5 records stored.
Can some one please help me???
Thankyou
p.s:- I have also looked at the NorthWind Example that comes with access,
and i still don't understand how to do it
