Field data jumps

[Jessica]

I have a table labeled Cancer treatments with an ID field, number format,
incremental, and a Name field(which houses the name of the cancer treatment),
text format. Then I have another table that is linked to this table that
pulls in Diagnosis(from another table), Cancer treatment(from this table I am
talking about) and Patient information(from another table). All of the
information is added to the linked table via a form. The problem is that I
added more types of Cancer treatments to the Cancer treatment table and the
linked table field for the cancer treatment jumps off by one after adding a
new records to the Cancer treatment table. I have compared this table to my
Diagnosis table to see where the programming might be an issue on the form,
but from what I can tell the Cancer treatment is showing up in the linked
table by the "postition" it was entered on the form. Any help is appreciated
and if you need more information please feel free to contact me, Thanks

 

[Mike Painter]

I have a table labeled Cancer treatments with an ID field, number
format, incremental, and a Name field(which houses the name of the
cancer treatment), text format. Then I have another table that is
linked to this table that pulls in Diagnosis(from another table),
Cancer treatment(from this table I am talking about) and Patient
information(from another table). All of the information is added to
the linked table via a form. The problem is that I added more types
of Cancer treatments to the Cancer treatment table and the linked
table field for the cancer treatment jumps off by one after adding a
new records to the Cancer treatment table. I have compared this
table to my Diagnosis table to see where the programming might be an
issue on the form, but from what I can tell the Cancer treatment is
showing up in the linked table by the "postition" it was entered on
the form. Any help is appreciated and if you need more information
please feel free to contact me, Thanks

Position entered is meaningless in Access about 99% of the time.

It seems like "anotherTable" ("Then I have another table that is") is the
main table.
AnotherTable should contain fields that hold the ID field from the Cancer
treatment table and the ID from Patients.
A query should be used to show the actual values.

If only one treatment (?) were used then it would also contain the ID from
Treatments.
Chances are that you want a Treatment's table that relates AnotherTable to
Treartments so you can have a subform showing all treatments.

..

 

[Jessica]

Here is my scenerio in a more general term. Table A(diagnosises) with two
fields: ID, Name Table B(Cancer treatments) with two fields ID, Type of
Treatment TableC (Patient Data) three fields ID, Name, DOB Table D connects
all these tables together via information added in a form via a query. So we
have entered over 700 records and then realized there is a new cancer
treatment that patients moving forward are going to start to receive. So I
went to the Cancer Treatment table and entered this new treatment on the
table. What happens next is that in all the existing entered records it bumps
what was orginally entered off by one record due to the New cancer treatment
that I entered. So then I went back and compared my Diagnosis table and the
entry points on the form to the Cancer treatments table and entry points on
the form and I can not find a discrepency anywhere. On top of that I have
also added Diagnosis's to the diagnosis's table and that data stays put in
the 700 existing entered records. Hope this helps explain it better.

 

[Mike Painter]

No, it does not. If table D "connects all these tables together" in a valid
relational way then the new cancer treatment you entered would not affect
anything,
No matter what you see on the screen, Access always adds new records to the
end of the file, indexes arrange things.
There is only a couple ways I can imagine what you describe could happen.
You changed an existing records treatment without changing the key and added
a new record so you could "insert" the record where you wanted it, or you
did not have a unique index on the primary key for some reason and entered a
duplicate.
There is also a remote possibility that something is corrupted but if the
results are consistent I doubt this is the problem.

Adding something new to an existing table is something that happens tens of
thousands of times every day in Access databases without this problem.