Trying to route records based on field input

Question

Hi guys, 

I'm really new to databases and I'm creating a form that I want to use to add records to multiple tables. I have a table for every state, and I want each record to go into a table based on the state that is input using a combo box I created. How the heck do I do that? 

Thanks, 

Mickeybuck

Answer 1

Hi Mickeybuck,

Why do you have separate tables for each state? Couldn't you just have a state field in one table? Having all data in one table with a state field would make creating a form so much easier.

Just my 2 cents...

Answer 2

I have a table for every state.................

That's a really bad idea.  It is what is known as  ' encoding data as table names'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct design would be have a table States, with one row per state, and a foreign key column in a referencing table which references the primary key of States.

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes, amongst other things, simple examples of how data is stored in related tables, with explanations of the different relationship types; one-to-many, many-to-many, and one-to-one.

---

Ken Sheridan, Stafford, England

Answer 3

I realized as soon as I read this that I can accomplish the exact same goal with one table. I'll just make the excuse that it was a Friday at the tail end of a long week. Thanks .theDBguy

---

Mickeybuck "Not all who wander are lost. I mean, most are but they usually won't admit it."

Answer 4

Thanks. Now that I had the weekend to think about it I'm not even sure I need anything more than one table with a query to use when I need it. What exactly is the application of using the States table vs. using it as a field?

---

Mickeybuck "Not all who wander are lost. I mean, most are but they usually won't admit it."