Add data to table

[BB]

When I enter data in a form, I would like to add some of the info in a second table. What is the best way to do this? The tables have a one-to-one relationship.

Thanks, BB

 

[Atomic Shrimp]

The inevitable question will be:
Why bother having two separate tables with a one-to-one relationship; can't you combine them into a single table? (the only time I've ever seen a real need for a one-to-one relationship was where one of the tables was linked in from an external source)

 

[BB]

I have several tables (CreditCardTransactions, Rental, WorkOrder, FuelTransaction, Adjustments) that involve transactions. It was suggested that I include an AccountTransaction table to bring all the transactions together, making it easier to do my reports(invoices).

 

[Rich@ITTC]

Hi Mike

I take your point about usually only needing one table, but what about the situation where you want to keep some areas of information confidential? For example, each member of staff is only on one pay scale and has given me only one set of account/bank details ... but the other staff details can also be seen by many other users of the database. The tblStaffPay is a table that is then only available to high level users (set via Security), but it has a one-to-one relationship with the main table, tblStaff.

Rich Gorvin

 

[Alexandre]

My 2 cents

I usually rely on hidding controls or dynamically opening/closing subforms for security matters.
the most common occasions when I had to use One to One relationships so far were:
-In a table, a subset of records have specific properties (fields) that would be left blank for many records if I used only one table.
-A subset of records or all records have temporarily associated properties, that I would like to easily delete on will, and without wasting space.

Alex

 

[Pat Hartman]

Since you seem to be willing to restructure your tables. How about the following:

Create a transaction table that holds all the common fields. This would be the primary table since ALL transactions would be entered here. Then remove the common fields from your existing tables (except for the key which will allow them to link back to the common table). Replace all the queries for the separate forms with new ones that join the common table to the specific table for that form. Use a LEFT join if none of the specific fields is defined as required in the table definition. When you insert a record using this query, Access will automatically create a matching row in each table as long as the specific table is going to contain some data.

It will probably take you a day or so to create the proper queries to separate the data into the new structure and fix up the queries. But, in the long run, you'll find the whole database much easier to work with. Your forms should not need to be changed as long as you don't need to change any field names. Same for the reports. Just replace their recordsource queries and you should be all set.

 

[BB]

I am doing something similar to what Pat suggested, but am getting an error: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I am able to enter one record in my form, but when I try to enter a second record, I get the above statement. Any ideas what I might be doing wrong?

Thanks, BB

 

[Pat Hartman]

Try running the query by itself to see if the query works. You do not need any code in the form to populate the key field of the child table. In fact, since the key to the primary table is an autonumber, you don't need either key field in the query at all. Access will populate the keys automatically as long as the relationships are properly defined. You should select enforce referential integrity, cascade update, and cascade delete.

 

[larryajax]

Yes I know what is wrong. You have your query backwards I think. You must use the join field (cannot be a primary key) from the many side(the data being used for your auto fill) and all the fields from the one side (Do not include the join field which must be a primary key) which have the data you want auto filled. Confusing? I know. Once you get all that right (use the help menu) you will say "ah ha!".