Import DBF files and create relationships that are updateable

[AccessNovice123]

Hello,
I am trying to convert a DOS system to an Access database. I have all of the .dbf files from the DOS system and have imported them into Access but am having trouble establishing the relationships. I have a Customers table with all personal identification of users. I also have three claims tables, each table is a different claim category: Credit, Life, Health. In each of my claims table, one person can have multiple claims (exception is Life) so my relationship from Customers to Credit and Health is one-to-many while Customers to Life is one-to-one.

I have forms created for user entry and viewing. I need the following two things to happen: 1) If I am editing a person's details in the Credit form, it should update my Customers table automatically, 2) If I am adding a brand new customer from any of the claims forms (credit, health, life) I need it to add the brand new person to the Customers table automatically.

I have tried doing this with primary keys and foreign keys but the error I run into is that when I try to add a new person in one of the claims forms, I get an error that the person does not exist in the Customers table and it will not let me add them.

Everything I have learned about Access is through various forums and YouTube, but I am still learning. Any help and guidance would be greatly appreciated. Thank you.

 

[plog]

It doesn't sound like your tables are set up correctly. Data shouldn't be moved or copied around in a database. There should be no need to update one table based on data in another--the relationship between them should tie their data together without storing it all over the place.

Can you bring all your tables into your relationship tool, make all fields of them visible and then take a screenshot and post here?

 

[AccessNovice123]

Plog,
I agree the tables are not set-up correctly which is what I am trying to fix. A screenshot with all fields visible is impossible, some of my tables have the max 255 fields. I can elaborate on what each of my forms is capturing if that is helpful.

For existing customers, if personal information is changed for any reason, I understand how to get the tables to update from any of the claims-my relationships work from that perspective-so #1 in my question has been resolved.

#2 is a bit more complicated so I will elaborate on my intended approach. On any of the claims forms, there is a personal tab which is the data connected to the Customers table. I need to be able to search for an SSN and if it exists, the personal information will populate within the form if it does not, I need to create a new record and have the personal information saved to the Customers table.

 

[plog]

...some of my tables have the max 255 fields

Yeah, that's not right. Sounds like you've got a system of spreadsheets inside your database. You need to read up on normalization and fix that:

https://en.wikipedia.org/wiki/Database_normalization

Throw away all the form talk for now. No point trying to figure out what shade of blue to paint the upstairs bathroom when the foundation is bales of hay, rubber bands and bubble gum.

 

[jdraw]

Totally agree with plog --- the 255 fields in a table is a real red flag.

Here's a link to info on database planning and design. Watch some of the videos for concepts, but the tutorials from RogersAccessLibrary are excellent(you do have to work through one or two--45-60 minutes each).

Getting your tables and relationships designed to support your requirements is the critical, key step to database.

Good luck.

 

[Pat Hartman]

I question allowing multiple forms to add customer records. Adding an important entity to a table requires validation and you really don't want to have to duplicate that validation in multiple forms. That becomes a maintenance nightmare.

You might want to organize the forms such that you have a main form with some customer demographic information including all the required fields. and then use a tab control with subforms for the various claim data and a final tab (but no subform) for any extra Customer data that you can't fit into the header section.

 

[AccessNovice123]

Thank you for the responses. I will look into the documentation and videos.

Appreciate the direction.