Question on Forms (I think!)

[bwgreen]

I am not an Access guru - but have been asked to build a database for us. I've created the tables and a first query, but now I am stuck on how to get all the data into the tables.

Basically, there is one table that uses references into other tables for static data. I'd like to create a form for users to enter data that displays the contents of the support tables and the references are stored (trying to minimize storage space!).

For example, assume I have Table1, Table2 & Table3. Table1 has two numeric fields storing the Primary Keys of Table2 & Table 3. When a new record is created in Table1, just the PK values of Table2 & Table3 should be stored, but the user can see the contents of what the PKs mean - they see the details field in Table2 & Table3.

Can this be done in Access? And if it can, can someone tell me how?

DB Structure:

Table1 Table2 Table3
====== ====== ======
ID - Numeric ID - Numeric ID - Numeric
Table2ID - Numeric Desc - Text Desc - Text
Table3ID - Numeric

Thanks,

Brian

 

[plog]

What you want to do is the exact reason to use a database. My suggestion is to read up on Queries (http://www.tizag.com/sqlTutorial/sqlqueries.php).

What you want is a simple query. You would join Table2 and Table3 to Table1 correctly, then bring in all the fields from Table2 and Table3 that you want to output. Again, read up on queries and work through a few tutorials.

 

[bwgreen]

The problem I'm running up against (first) isn't retrieving the data across tables, it's entering it. Everything I see refers to retrieving it. What I am trying to do is create a from for people to add records to a table - the table would just hold references into the supporting tables, while the form should show the contents of the supporting ones. So, for example, using a normal example of an Employee database, the supporting table has the name and employee #, while the table I want to update would just have the # in it.

 

[plog]

You've genericized your situtation almost too much, but I will work within the parameters of the database you have described (Table1, Table2, Table3). The technical term for Table1 is a junction table (http://en.wikipedia.org/wiki/Junction_table). It defines a many-many relationship between Table2 and Table3.

When it comes time to create forms for data management of this system you are going to need a subform. In general, you shouldn't base forms on queries, you should base them on tables. So each table will have its own form.

In practical terms, with the relationship you have (many to many) this means Table1 will be a subform on the form based on Table2 and/or the form based on Table3. It really depends on how data will be assigned.

If you can describe your actual data, I can be more specific, but in general that is how the forms for your system should work.

 

[pbaldy]

Unless I'm misunderstanding, you can use a combo box on a form that saves the numeric key value but displays the text value. The combo wizard should walk you through it.