many table, one form?

[nescartel]

undefinedundefinedundefinedhi, i'm a newbie using MS Access 2003 on Windows XP. i am developing a database containing data obtained from different states, and in my case, i have 4 states; so the data will be in 4 different tables. to present the data to end-user, i made a form, displaying all the neccessary details. the problem is, rite now i have to make 4 similar forms for 4 tables even though the form are pretty much the same. is there any way i can present data on the form by only using 1 form? is there any way to let the user select data for which state he wants then display the common form that will refer to different data source (table) to user? pliz help me...

 

[MarkK]

You don't need a different table for each state. Have one table with a "State" field, which in your case will be one of four values.

Then it's way easy to make the one form.

 

[nescartel]

errm, okay. but how will the user able to make selection? is there any way that when the user select State1, only data corresponding to State1 will be shown? pliz give me ideas, i really appreciate it. thanx in advance!

 

[ColinEssex]

ComboBox for your selection of states.

ListBox for the items in the state selected

Col

 

[neileg]

Colin is quite correct, but I think you need a more basic hint.

Many people who are used to using a spreadsheet try to adopt familar methods when turning to a database. Often this will work but it's rarely the best way.

The big difference between a spreadsheet and a database is that in a spreadsheet, the data storage and the data display are the same thing (usually, anyway). In a relational database like Access, you store your data in tables, select and manipulate the data in queries, display the data in forms and print it in reports.

What you need to do is store all 4 states data in a table. Each record should have a field that identifies the relevant state. You create a query that extracts the data based on the state that the user supplies and the form for displaying the data is based on the query.

In order to prompt the user to identify which state they want, there are a number of techniques. The easiest for a beginner is to place a prompt in the criteria row of the query. Colin's suggestions are more elegant.

Has this confused you? Come back for more help.

 

[nescartel]

does this mean that i will have to present the data to user by using queries? i have never used queries before, i am really confused! i only use table to store my data and form to add entry/display. for the menu selection i use the switchboard. what do queries do actually?

 

[MarkK]

And whatever you do, stick with it, cause once you get your head around it's just really cool what you can do.

 

[neileg]

Have a look at this very simplistic example (in Access 2000 format).