Relationship question

G

Guest

I just CANNOT figure this out!

I have two tables. Table one is called HeaderInfo. Table 2 is called
DetailsInfo.

Within table HeaderInfo I have a field called RecordNum set as my primary key.
In table DetailsInfo I have a field called LogNum.

In my access relationships I set it so that there is a One-To-Many
relationship between the RecordNum field and the Lognum field. RecordNum is a
primary key.

I am trying to set it up so that for each record in table HeaderInfo, the
first field (RecordNum, which is set to AutoNumber) will, for example, put a
1 for the first record.

Consequently, I want each "detail" record (which are located in the
DetailsInfo table) to have a 1 in the LogNum field.

For record 2 in the HeaderInfo table (which would have a 2 under the field
RecordNum), there would be a 2 in the LogNum field for each record it
corresponds with in the OrderDetails table.

Is it the fact that I am trying to use an AutoNumber that is screwing this
up? I had it working before I switched it to AutoNumber. Please help.
Thanks.
 
C

Chriske911

I just CANNOT figure this out!
I have two tables. Table one is called HeaderInfo. Table 2 is called
DetailsInfo.

Within table HeaderInfo I have a field called RecordNum set as my primary
key. In table DetailsInfo I have a field called LogNum.

In my access relationships I set it so that there is a One-To-Many
relationship between the RecordNum field and the Lognum field. RecordNum is a
primary key.

I am trying to set it up so that for each record in table HeaderInfo, the
first field (RecordNum, which is set to AutoNumber) will, for example, put a
1 for the first record.

Consequently, I want each "detail" record (which are located in the
DetailsInfo table) to have a 1 in the LogNum field.

For record 2 in the HeaderInfo table (which would have a 2 under the field
RecordNum), there would be a 2 in the LogNum field for each record it
corresponds with in the OrderDetails table.

Is it the fact that I am trying to use an AutoNumber that is screwing this
up? I had it working before I switched it to AutoNumber. Please help.
Thanks.

depends how you are filling the database with data, ?

however,
you need a relation with referential integrity
in the header table the recordnum field is a primary key, autonumber
in the detail table the lognum is a (non primary key) field with long
integer as type

then you create a main and a subform as GUI for users to create records
the link will be updated automagically by access

grtz
 
L

Larry Daugherty

Each table can have an Autonumber primary key. FWIW don't use an
autonumber type for anything that a human being will see and try to
make sense of. You have been warned! Autonumbers are not guaranteed
to be sequential and autonumbers of deleted records are not
automatically regenerated. People (customers in particular) tend to
believe that they have a say in how anything they see should be
controlled. Trying to make autonumbers behave predictably is a
fruitless endeavor.

The details table should have its Autonumber primary key field
followed by a Long Integer Foreign Key field which, for convenience
sake, should always be named the same name as the key to which it
refers in the parent table. In your case that is "RecordNum". Now
you should be able to draw your relationship from the Primary key of
the first table to the Foreign Key (of the same name) in the second
table.

If in fact you have to generate a number sequence for your users you
have to take on the management of number creation and management.

For some insights into the agonies of the mis-use of Autonumber, do a
google search on the Access newsgroups for the term.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top