insert records into multiple tables from ONE form? ? ?

[kyserkess]

hi Jz, I was trying to figure out how to make foreign keys, but I wasn't able to. So, if I make them into multiple tables, will there still be parent tables that effect child tables? So, if I select something like Kung Fu, then my Substyles narrow down to Shaolin, Wing Chun, Choy Fat, Hung Ga, etc. without choices from other parent styles, such as Okinawa, Shotokan, Goju-Ryu, etc. - all being Karate styles. Orgin refers to the Martial Art style, Kung Fu is of course from China, Origin 2 can be more specific, such as a particular province, city, or region of China.

Logically, I would want to be able to browse my database multiple ways, such as by parent Martial Art style, or by Origin, and of course by alphabetical names of instructors - as when I want to call someone and need to look up their information.

I imagine that browsing by their price rate would not be as difficult either? For instance, a quarry form where I am able to select that I want to search for a LOCAL - TaeKwonDo Instructor - with HOURLY RATES between $20 and $40.

Like any Access process, I believe I design the tables first, the relationships second, a form to input the information (in the easiest format), and then a quarry form for retrieving the information. I think you are right that I probably have step wrong and therefore cannot accomplish step 2 correctly.

Does any of the information I added on this reply post change the table format that you propose?

Thank you kindly.

 

[jzwp22]

I was trying to figure out how to make foreign keys, but I wasn't able to. So, if I make them into multiple tables, will there still be parent tables that effect child tables? So, if I select something like Kung Fu, then my Substyles narrow down to Shaolin, Wing Chun, Choy Fat, Hung Ga, etc. without choices from other parent styles, such as Okinawa, Shotokan, Goju-Ryu, etc. - all being Karate styles. Orgin refers to the Martial Art style, Kung Fu is of course from China, Origin 2 can be more specific, such as a particular province, city, or region of China.

With the setup I proposed, you would be able the filter the choices of the substyle. That uses a technique called cascading combo boxes, but that is a form issue. We need to concentrate on the tables and relationships first as you suggested. You would accomplish your browsing requirements with queries and forms, but all of that depends on setting up the correct table structure.

Back to the origin, you can use a series of tables similar in to the styles/substyles to define the origin. How you set them up depend on how much detail you want. As an example:

tblCountry
-pkCountryID primary key, autonumber
-txtCountryName

tblStates (I'll use states but you can use this table to store states or provinces or any other subdivision of the country)
-pkStateID primary key, autonumber
-fkCountryID foreign key to tblCountry
-txtStateName

Then you can go further to the city level

tblCities
-pkCityID primary key, autonumber
-fkStateID foreign key to tblStates
-txtCity

Now when you define the substyle, you can associate the pkCityID as a foreign key in the following table to fully define the origin of the the substyle (putting in the fkCityID relates back to the state and to the country for the complete origin)

tblSubStyles
-pkSubStyleID primary key, autonumber
-fkStyleID foreign key to MAStyles table
-txtSubStyleName
-fkCityID foreign key to tblCities

As to setting up foreign keys, the foreign key is just a long number field in the table. It does not actually become a foreign key until it is joined to its corresponding primary key in the parent table via the relationship window or you can do it on the fly in a query.

 

[kyserkess]

fantastic!

So, I will set up these tables and add a screenshot here.

Is it difficult to setup the combo box? I can try to use the wizard to do that and see how it goes.

You are very helpful!

 

[jzwp22]

Setting up the cascading combo boxes in a form can be a little tricky; this site has a sample that should help out.