Problem with MS Access Database

[BrianB]

I use MS Access to keep info on Civil War soldiers I have researched over the past 30+ years - currently over 12,000 soldiers are in my database.

When I set up the database, I set it up as two separate "tables" - one would have info on that particular soldier and the second would have sources for the aforesaid info.

Access assigns an ID number to each "record" in the database, which links the two tables together.

Problem: Each entry from table #1 is being linked with the wrong entry from table #2 -- the link is to the table #2 entry for the NEXT record. When I display a record on soldier Bill Jones, it displays the source info for whoever was the NEXT person I entered in the database.

Any one had this problem before? Any advice how to fix it?

 

[Bob Velke]

(I'm not sure why this is here. If the admin objects, you can send me a PM.)

Table #1 is unique soldiers and #2 is non-unique sources? I'm assuming that you intend for it to be a one-to-many relationship between tables (i.e., there are - or can be - multiple source records for each soldier record)?

So there is a key field in #1 and each record has a unique value there? What is the name of that field? Does the value in that field match the physical record number in each case?

Then there's a similar field in #2 except the numbers there are not unique? What is the name of that field? Each value points to a soldier record in #1?

So if there is a family Bible and it contains information about two soldiers, John and George, then there is exactly one record for each soldier in table #1 and multiple records for the Bible in table #2?

From your short description, this is how I envision you have it set up. If not, I'd need a similarly-specific description of the tables and common fields in order to understand the file structure.

 

[BrianB]

Bob Velke - Thanks for replying! It is a one-to-one relationship -- Table 1 has data about an individual soldier in a single entry - the corresponding record in table 2 has the sources for info about that one soldier, also in a single entry. (I am not sophisticated enough to do some fancy multi-entry relationship)

When I enter a new name/info in table 1, MS Access automatically assigns an ID #. So, a particular entry might be "Smith, John" and the program would assign the next ID number in numerical order - say "1287".

I type in all the entry on the soldier and also type in my source info (MS Access allows you to create a "form" on your computer screen, which lets you view/enter data into a form) my form has entries for:

(this is not a complete list)
Last name:
First Name:
Unit:
1850 census entry source:
1860 census source:
1890 veterans census source:
Burial place: source:

MS Access takes the data about "John Smith" and saves all the information on him in table one, on line number 1287, and saves all the source info about him in table two, also line number 1287.

Problem: for whatever reason, MS Access is saving info about John Smith in table 1, Line 1287, and saving his source info in table 2, line 1288. So, when I go back to look at the data, I look at the form layout on my computer screen and I see a mixture of information - info about John Smith mixed with source info corresponding to someone else.

Any thoughts how to fix this?

 

[tony_gunter]

Is it possible you accidentally got a duplicate somewhere in the linked form? If it's a one-to-one table, you should have the no duplicates set in the design view.

 

[Library Lady]

If you need more help that you can get here, see if there is a library in your area that offers Microsoft Office classes to the public. If there is, contact them to see if you can make an appointment to get one-on-one tech help from the trainer. It is unlikely that you would need a card from that library to access the help. It is also unlikely that you would be charged for the help.

 

[BrianB]

Library and Tony - Thanks!

Tony - I don't think there is a duplicate - I think what has happened/is happening is that (for some reason) when I deleted an entry, MS Access failed to delete the line from both tables - resulting in one line too many one one table.

 

[milhistbuff1]

Library and Tony - Thanks!

Tony - I don't think there is a duplicate - I think what has happened/is happening is that (for some reason) when I deleted an entry, MS Access failed to delete the line from both tables - resulting in one line too many one one table.

Download into excel and use the duplicate checker in the conditional formatting section, home tab. Should find the MIA entry for deletion in access.

 

[BrianB]

Milhistbuff1 -- Thanks! I will try that.

 

[milhistbuff1]

Milhistbuff1 -- Thanks! I will try that.

Your welcome, happy to help, hope it works!