Database Layout Question

Hello. I’m a bit of an Access newbie and am having trouble figuring out how to implement something.

Here’s my problem: I have two tables with one common field, “Part number�. The first table I have lists everything that is currently open in another database (Database1). I do not want to be able to edit the information in this table and consider it read only data. Let’s call this first table “Untouchable� for reference. The second table I have lists all old or closed items from the Database1 plus some user defined information for the rows from Database1. This second table is mainly made up of information based on user input so let’s call this table “User Input�

Right now I have combined the two tables together by using linking the “Part Number� field, combining the two tables together in a new table called “Combined Data� via a Make Table Query, and displaying the combined results in a Form that points to “Combined Data�. In the “Combined Data� table is every common part number from both tables, plus everything else that is in the “User Input� table.

My problem is this. If the database user goes into the form and changes some of the user defined information, it only gets updated in the “Combined Data� table, not in the “User Input� table. I don’t know how I can reflect this updated information in “User Input� the next time I run my query. Is there an easy way to do this? Can a Form be tied to two tables? If so, how? Any thoughts on implementation are appreciated.

Thanks in advance.

Nate

2 possible solutions to your specific question:
1)
-Take the Make Table query you used to create your new table and change it
to a Select query. (and delete the "CombinedData" table?)

-Base your form on the Select query.

-Make sure that the only "Editable" fields on your form are from UserInput.
(PartNum shouldn't ever be editable)

2) Alternatively, and depending on your data, you might want to make your
form into a Form/subform setup (linked on PartNum) where the Parent form
data is from Untouchable and your subform data is from UserInput. This might
make it clearer to the user as to what is engraved in stone and what is not.
It also might make your life easier during development.

General Comment: *Usually*, Forms should be based on queries, not directly
on tables. This provides an extra layer of protection between the user and
the actual data because they are always working with a COPY of the data: the
query's recordset in memory. If something goes wrong during a session, this
extra layer of protection may keep your data from being corrupted. And if
it is a large table, you have the option of using the query to control how
much of that data is "delivered" to the user before any form filters are
applied. Just a real good habit to get into.

HTH,

--
George Nicholson

Remove 'Junk' from return address.


"PurdueNate" wrote in message
...
Hello. I'm a bit of an Access newbie and am having trouble figuring out
how to implement something.

Here's my problem: I have two tables with one common field, "Part number"
.. The first table I have lists everything that is currently open in another
database (Database1). I do not want to be able to edit the information in
this table and consider it read only data. Let's call this first table
"Untouchable" for reference. The second table I have lists all old or
closed items from the Database1 plus some user defined information for the
rows from Database1. This second table is mainly made up of information
based on user input so let's call this table "User Input"

Right now I have combined the two tables together by using linking the
"Part Number" field, combining the two tables together in a new table called
"Combined Data" via a Make Table Query, and displaying the combined results
in a Form that points to "Combined Data". In the "Combined Data" table is
every common part number from both tables, plus everything else that is in
the "User Input" table.

My problem is this. If the database user goes into the form and changes
some of the user defined information, it only gets updated in the "Combined
Data" table, not in the "User Input" table. I don't know how I can reflect
this updated information in "User Input" the next time I run my query. Is
there an easy way to do this? Can a Form be tied to two tables? If so,
how? Any thoughts on implementation are appreciated.

Thanks in advance.

Nate

"=?Utf-8?B?UHVyZHVlTmF0ZQ==?="
wrote in :

My problem is this. If the database user goes into the form and
changes some of the user defined information, it only gets updated in
the “Combined Data� table, not in the “User Input� table. I
don’t know how I can reflect this updated information in “User
Input� the next time I run my query. Is there an easy way to do
this? Can a Form be tied to two tables? If so, how? Any thoughts on
implementation are appreciated.

(Just a simple thing: please switch off whatever is making these strange
characters. Usenet is still an ASCII medium)

Your problem is really trying to make Access do something that it is just
not designed for. Access is a relational database program, and if you want
it to help you, you'll just have to learn some R theory and design
principles. For example, things belong the same or different tables
according to _what_ they describe, not _how_ you want them to be used.
There is half-a-century of maths theory backing up the R model and it's
worth taking care over.

Secondly, what you are asking for is not trivial in Access. It can be done
properly using Access security and a set of RWOP queries that control what
the user can and cannot see. It can also be done informally, using the form
to change the properties of controls on it (to Locked, for example). The
latter is pretty easy to get round for anyone who knows the program, and
the former involves a pretty steep learning curve for you (on top of the
general db theory).

Have you thought about a different approach? It seems that a quick and
dirty method would use an Excel workbook with two sheets: one of them can
be locked so that the user would not be able to change anything, and the
other open. I don't know all the ins and outs, but I think you can set up
workbooks so that they can be shared between multiple users. It looks as
though you have started with a spreadsheet solution, and sticking with it
may be better than shoe-horning it into a rdbms one.

HTH


Tim F