filling in primekeys through forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that connects several tables together. The main table - say
table 1, has a prime key and that prime key is in all the other tables. When
I bring up the form, the 1st record appears. I want to put in a second
record and have the generated prime key of table 1 in all the associated
tables so that all the information will display when the information in
table1 is diesplayed. How is this accomplished? Is there a auto fill or
something to have this done. (I did not see this, thus the question)

Thank you.
 
I really can't figure out what you are trying to do. Are the tables related?
Why do you want the same primary key (assuming that's what you mean by
"prime key" in all of the tables? If the tables are related the primary key
in Table1 could be the foreign key in the other tables, but I won't go too
far down that road without knowing what you are trying to do. To get a
helpful answer you should provide some specifics.
 
Bruce,

Thank you for your reply. Yes, the tables are related. I want the same
primary key in all the tables so there is a relationship between the tables.
You are correct, I used the wrong terminology - the correct terminology is
foreign key (which is the primary key of table1). How do I get the foreign
key filled with the value of the primary key of the main table, table1? I'm
not sure what other specifics you would need that would influence the answer.
If you could ask them, I will try to answer them.

Craig
 
The short answer is that establishing relationships between the tables is how
a record in one table with a record or records in another. The best choice
(the only choice, really) for entering and working with data is a form, but
you need to start with tables and proper database design. It would help if
you could describe what you need to do, providing some specifics. For
instance, are you keeping track of payroll, inventory, sports teams, school
grades, or what? Without knowing that I would have to say something like
"suppose that the main table is for employees", when in fact you are trying
to keep track of spare parts for the Mars rover.
 
Bruce,

Ok. I am trying to build something in Access that will allow someone in a
hospital to keep track of patients, what type of things they made the visit
for and the medicine that was given out. This hospital is located in Ghana.
It is run by the missionaries. It is not a very big hospital, but it handles
about 1000 people a week. It is the only hospital for hundreds of miles
around.

I have the ‘main’ table called Name. The fields are as follows: Last Name,
First Name, Middle name, Miscellaneous, and Name_prime_key. The prime key is
called name_prime_key. This prime key will be in almost every table.
The next table is named Address. The fields are as follows:
Address_primary_key, address1, address2, Country and Name_primary_key.

The next table is named Contact. The fields are as follows:
contact_primary_key, Contact_number1, Contact_number2, contac3, and
Name_priamr_key.

The next table is named History. The fields are as follows:
History_primary_key, date_of_vist, Reason_fir_visit, Treatment, and
Name_primary_key.

The next table is medicine. The fields are as follows: Medicine_prime_key,
History_prime_key, and medicine_issued.

The first 4 tables are connected by the primary key in the Name table,
Name_prime_key. Since there is a 1 to many relationship for the history and
the medicine, there is no need for the name_primary_key to be there.

What I want to do is have the primary key fill in automatically when a new
record is added. It simply can NOT be that hard to do. Other products fill
it in automatically, or at worse there is a filed that you click on and then
it is filled in automatically. I am trying to find out where or what I have
to do in order to have the name_prime_key filled in automatically and then of
course the history_prime_key in the medicine table.

I am not sure that there is anything other information that I could provide
to you. However, if you ask for more detail, I will try to provide.

Your help is much appreciated.

Thank you.
 
I assume that you have a separate address table because several people could
live at the same address. If that is the case I need to tell you that I am
unsure how best to proceed. The only thing I can think of is to store
AddressID in the name table, but with no relationship between the fields.
You would select the address from a combo box with its row source a query
based on the address table. However, that means you would need either to
create the address first if there is not one in tblAddress, or the AddressID
number would appear in the combo box field because it is the bound column.
The bound column is the field that is stored, so if you are storing AddressID
that is what you would see after making the selection from the combo box.
There are ways around either of these problems, but I just don't know what is
the most effective design. I also can't quite figure out what would happen
if a family moves. You could create a new address, but then you would have
to update each record that contained the old address, and that doesn't seem
quite right somehow.
I suggest you start a new thread with something like "One address, several
people" as the subject line. Describe the situation just as you did in the
most recent post. That is exactly the kind of information that is needed to
elicit an effective response. I will be watching for the question and any
replies, as it is something I would like to learn about as well. Good luck.
 
Bruce,

The separate address table allows the people to move from 1 villiage to
another. I did not want a many to many relationship. we could probably have
a whole discussion about that, and yes that 'should' probably be under
another subject. Getting back to the original subject, which is hard to keep
on at times, how does one fill in the foreign keys on tables in a form, or as
I put it the first time - filling in the prime keys through a form? Can you
help with that question? I have provided all the answers to what you asked,
although I could not see what those questions had to do with the getting to
the answer, I provided them to you. Will you help me with this issue?

I know that the answer MUST be simple or this would not be such a hard thing
to answer. Most products do this automatically, and as such, I thought that
MS would do the same thing. Perhaps they do and I just have something very
simple set up wrong, which is what I expect. I know that answer is there
somewhere and that I just missed it somehow.

Craig
 
Getting back to the original subject, which is hard to keep
on at times, how does one fill in the foreign keys on tables in a form, or as
I put it the first time - filling in the prime keys through a form? Can you
help with that question?
Click to expand...

By using a Form for the main table, a Subform for the related table,
with the Primary Key as the Master Link Field and the foreign key as
the Child Link Field, typically.

John W. Vinson[MVP]
 
Ignoring address for a time and looking at names and history, your table
structure would be something like this:

tblName
NameID (primary key, or PK) - use autonumber as the data type
FirstName
LastName
etc.

tblHistory
HistoryID (PK) - autonumber
NameID (foreign key, or FK) - use Number as the data type
VisitDate
Reason
etc.

Click Tools > Relationships. In the Relationships window select each table
and click add. Close the box. Drag NameID from one table to the other, and
drop it on NameID in the second table. Click Enforce Referential Integrity,
then Create. Close the Relationships window.
Make a form (frmNames) based on tblNames. Better yet, make a query
(qryNames)based on tblNames, sort the names alphabetically, and base the form
on the query. You can also use a query to combine FirstName and LastName.
Help has more on that if you are interested, but it's not to the immediate
point. To base the form on either the table or the query, select Create Form
in Design View in the database window. A blank form will show up. Double
click the small box with a solid rectangle in the middle at the top left of
the form. It is where the rulers would intersect. That will open the form's
property sheet. Click the Data tab, click Record Source, click the down
arrow, and select the table or query. A list of fields will appear in their
own box. Drag the fields you need onto the form. You needn't drag NameID
and such. They are part of the form's record set, whether or not they appear
on the form. Leave room on the form for history, and save the form. Repeat
the process to build a form based on tblHistory. Save it as fsubHistory. Go
to the database window (the window with tabs for forms, queries, etc. that
appears when you start Access). Pressing F11 should bring it to the front.
With frmNames open in Design view, drag the icon for fsubHistory onto
frmNames. Switch to form view and take a look. Try entering some names and
histories.
You do not need to enter primary keys or foreign keys. The autonumber PKs
are assigned automatically. If you create a record, then start to create
another record but stop before you have entered any data, it will still "use
up" a number. Later, when you go back to enter a new record you will see
that the first record is 1 and the new one is 3. 2 is gone. It doesn't
matter. If you need a sequential number there are ways of doing that, but
let's not get into that just now.
The autonumber PK is assigned automatically as soon as you start a new
record. When you add a record to History (fsubHistory is the subform to
frmNames), it will automatically, because of the relationship between the
tables, have NameID filled in for the FK field. Your don't need to do
anything else to make this happen. If NameID is 1, every record created in
fsubHistory will also have 1 in the NameID field. Those history records will
always be associated with that Name record. No other History records will
have the same number for NameID.
You could do the same general process to establish a relationship between
History and Medicines. The Medicine form would be a subform of the History
form, which is in turn a subform of the Name form. These are nested
subforms, like one of those Russian wooden dolls.
There are lots of additional features, like using a combo box to select a
name and view all of the related information (rather than having to scroll
through all of the records to find a name). If the basic design works we can
get to that.
I'm still not quite sure what's going on with the Address table. Address is
often part of something like a Patient table, along with name and other
personal information unique to an individual.
 
BruceM said:
NameID (primary key, or PK) - use autonumber
as the data type
Click to expand...

Autonumber (autoincrement, IDENTITY, etc) is a property, not a data
type. A data type is also required, usually INTEGER but could be other
numeric types including, bizarrely, CURRENCY.
make a query
(qryNames)based on tblNames, sort the names alphabetically,
Click to expand...

So if your suggested major use of the table is to sort on names
alphabetically, a better choice of primary key would be (LastName,
FirstName, NameID) in that order. Remember that the meaning of 'primary
key' in MS Access/Jet is different from other SQL products i.e. it
determines the physical order on disk. Building your table's physical
order on an incrementing INTEGER (or a random CURRENCY value, perhaps
<g>) then sorting on other columns each time you use it may not be the
best advice. If you want to use NameID as a foreign key in another
table then create a separate, single-column UNIQUE constraint/index.

Ensure you are attaching the correct meaning to 'primary key'. Also,
'FK references PK' is not a requirement: for a FK, any UNIQUE key will
do.

Jamie.

--
 
Responses inline

onedaywhen said:
Autonumber (autoincrement, IDENTITY, etc) is a property, not a data
type. A data type is also required, usually INTEGER but could be other
numeric types including, bizarrely, CURRENCY.
Click to expand...

In table design view there is a data type column. One of the choices there
is autonumber. Number is also a choice in the data type column, as is
currency (and date/time, Yes/no, text, memo, and a few others). Integer is a
field size property for Number data type. Whether or not the terminology is
technically correct, these are the terms used within Access.
So if your suggested major use of the table is to sort on names
alphabetically, a better choice of primary key would be (LastName,
FirstName, NameID) in that order. Remember that the meaning of 'primary
key' in MS Access/Jet is different from other SQL products i.e. it
determines the physical order on disk. Building your table's physical
order on an incrementing INTEGER (or a random CURRENCY value, perhaps
<g>) then sorting on other columns each time you use it may not be the
best advice.
Click to expand...

My suggested use of the Name table is to store data. My suggested use of a
query is to manipulate the data, including sorting, concatenating, limiting
to certain criteria such as date range, and so forth. One of the main
functions of a relational database is to look at data in different ways.
Even if your suggestion offers some performance advantage over use of a query
when the data is to be sorted by name, what happens when you want to sort by
some other criteria such as birth date? NameID IS a unique value. If there
are studies demonstrating that use of an autonumber (or number) PK is
ill-advised for performance reasons I would be interested in seeing them.
Until then, my own observation is that a query based on a table with several
thousand entries opens in about the same amount of time as does the table
itself.
If you want to use NameID as a foreign key in another
table then create a separate, single-column UNIQUE constraint/index.
Click to expand...

NameID IS a unique value. What purpose is served by creating another?
Ensure you are attaching the correct meaning to 'primary key'. Also,
'FK references PK' is not a requirement: for a FK, any UNIQUE key will
do.
Click to expand...

I am using FK to mean the field on the many side of a one-to-many
relationship. If the PK is autonumber, the FK must be number; if the PK is
other than autonumber then the FK must be of the same data type.
Also, 'FK references PK' is not a requirement: for a FK, any UNIQUE key will
do.
Click to expand...

I cannot find where I made the statement you have placed in quotes, so I
don't know the context. A FK is defined by its relationship with a PK in the
case I have outlined. The FK is not a unique value in a one-to-many
relationship. Many records could have the same FK, but only one record on
the one side of the realationship will have that same value as a PK.
 
BruceM said:
In table design view there is a data type column. One of the choices there
is autonumber.
Integer is a
field size property for Number data type.
Click to expand...

If we are talking about Jet databases, there is a COUNTER data type
which is an incrementing INTEGER by implication. If you are referring
to MS Access applications, there is an autonumber feature that is
subtly different e.g. supports other numeric data types, random
progression, etc. Autonumber is exposed to Jet as IDENTITY but choosing
a data other than INTEGER will result in the loss of incrementing
functionality outside of the MS Access UI.

So the MS Access UI describes autonumber as a 'data type' and its
numeric data type as a dependent property, rather than the other way
round? Counterintuitive to me but no doubt makes sense to countless
others <g>. Of course, just because the MS Access UI says something it
doesn't mean it is true. To use a pertinent example, I recall seeing
this message: "A table must have a primary key for you to define a
relationship between this table and other tables in the database."
However, I can define a foreign key, which has stricter rules than an
MS Access 'relationship', using a column with a unique constraint. It
lies, it lies ...
NameID IS a unique value.
Click to expand...

But your autonumber still doesn't provide you with an identifier. I
guess you are using an autonumber because you could have more than one
'Jean Dupont' so the it breaks duplicates. However, you will still not
be able to tell one 'Jean Dupont' row from another unless you expose
your autonumber values to users, which is a bad idea (e.g. there are
better identifiers than random/incrementing numeric values).
What purpose is served by creating another?
Click to expand...

Let's go back to basics. If all you want is a NOT NULL UNIQUE column,
why not just declare it as NOT NULL UNIQUE? Why are you using the term
PRIMARY KEY?
If there
are studies demonstrating that use of an autonumber (or number) PK
is ill-advised for performance reasons I would be interested in seeing
them.
Click to expand...

Here's one you can do at home with a paper copy telephone directory.
Study exercise1: get a list of phone numbers for names beginning with
the letter A. Study exercise 2: get a list of phone numbers beginning
with the number 1 (after area codes etc). Why is exercise 1 so much
easier than exercise 2? Because the physical ordering is on name. And
why is a paper copy directory ordered on name? Because this is how most
people use the directory.

The same applies to the pages in a database table i.e. there is a
performance advantage if the physical order matches your usage. Using
an autonumber as a primary key would be like a the paper copy telephone
directory ordered by telephone number with the associated degradation
in performance.

If you are looking for something more 'official', try these:

Jet 3.0 Performance Overview (search for each appearance of the text
'cluster'):
http://www.microsoft.com/AccessDev/Articles/v3perf.htm

New Features in Microsoft Jet Version 3.0 (start reading from 'New
compacting method'):
http://support.microsoft.com/default.aspx?scid=kb;en-us;137039

ACC2000: Defragment and Compact Database to Improve Performance (key
phrase = 'Clustered Indexes'):
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Jamie.

--
 
Back
Top