How to Assign Data to a Specific Table Based on Data Entered in Form

[ajwats81]

Hello! Thanks in advance for any help! I have a database detailing potential customer's contact information. My database is divided into 4 tables, based on the type of business it is..."Garden", "Feed", "Farm", and "Hardware". Each table contains the same fields for Business Name and Contact info, with the only difference being that they are separated into tables based on the Business Type. I am trying to create a form that will assign the record to the required table based on a combo box entry for the business type. Is there a way to do this or do I have to make an data entry form for each table?? Side note: I do not have any relationships set up. I had originally set up one table with all the records in it but decided to split them into separate tables. Now I think my ID field is screwed up. Thanks again for any help or suggestions.

[Bulzie]

Can you post examples of your tables and fields you have so far. From what I can understand, you should have a main switchboard form with the Business Type combo box. Create 4 forms for Garden, Feed, Farm, Hardware and when they select the Business Type from Combo, open that specific Form?

[ajwats81]

I have *5 tables. Each table is a different business type (Garden Center, Farm, Hardware Store, etc.). Each table has the same fields. (Zone, BusinessName, StreetAddress, City, State, ZipCode, Contact, Phone, CellPhone, Fax, EmailAddress, BusinessType). I would like one entry form for entering business info and when I use the BusinessType ComboBox, it automatically enters the record into the specified table. Example: I am entering a new record via form and after I enter all the contact info, I check "Hardware" and it saves the record to the HardwareTable. Do I need a seperate table for each BusinessType or should I keep them all in one table and just use queries to sort them or report from them?

[orange]

Forget tables and queries at the moment. Describe to readers, using simple English, what is your business? What do you do? To whom do you do it? How often do you do X or Y or Z ? When do you do it?
What if you add or remove 1 or more Business types?

Here is a tutorial that will help translating a business description into table and relationships.
You have to work through it, but you will learn how to design and test your tables and relationships.
It includes a solution.

[ajwats81]

I am tasked with finishing a coworker's database, which was originally designed in Excel. After explaining the benefits of having a database, I imported my coworker's Excel spreadsheet into Access. She had the address as one field so i created multiple fields for the address and fixed all the broken data. I have separate tables for the business type: farm, feed, garden, hardware, and lumber. We are trying to sell them Christmas trees and bales of straw. My job is to Google "garden centers of *STATE*" and then record contact info for each business. I am working with 3 states and 5 business types at the moment. I would like to create an entry form that would enter the contact data for each business and then at the end of the form when I select the business type via ComboBox, enter the record to the GardenTable or FeedTable, depending on which BusinessType is selected in the form.


w

[orange]

I think this draft model may be of some use.

Good luck

[Bulzie]

Why do you need the separate tables, especially if all the data fields are the same except for the BusinessType? Just save the combobox value (farm, feed, garden, hardware, lumber) into the BusinessType field. Then all your data is in 1 table and you can query just on a specific BusinessType if needed.

OR two tables, 1 for Business Information(everything but BusinessType) and another table called BusinessType that has BusinessName and BusinessType(combo box). Link the tables on BusinessName

[ajwats81]

Thanks for all the replies. I realized as I was working it out that I was trying to be too complicated and had already rearranged my database to have a BusinessType table as the last poster suggested. Now I just have the entry form with a ComboBox for the BusinessType. Thanks again guys.

I would still like to know if my original question has a solution. Is it possible to assign data to different tables based on form entry?

[NightWalker]

I would still like to know if my original question has a solution. Is it possible to assign data to different tables based on form entry?

The short answer for your original question is yes

[orange]

What if your potential Business is in 3 different BusinessTypes and, what if he is interested in ChristmasTrees and StrawBales?
What if there is a different contact for different BusinessTypes?

I'm not suggesting these are real cases. But the OP didn't rule them out.

You have to identify the business rules, then design your tables and relationships.

You don't start with I am building a database and it will only have 1 table (no matter what).