Update or Append Query?

[ResTech]

I am trying to automatically update one table to another each time a new record is added to my form, I have tried using the Update and the Append Query is there another way to do this without using code? Thanks.

 

[jdraw]

What are you trying to do in plain English -- no database terms?
It sounds like you're storing the same thing in 2 different locations --- why?

 

[ResTech]

I have a customer service person filling out a form for a service request all info stored in Customer Table, 3 of the fields are going to be dumped into the Service Table...when the device comes back for repair the service tech opens his form and searches for e.g. repair# 10 his form comes up and he can verify that it is #10, patient Id is in there and the complaint, then he can populate the rest of the information such as what repairs were made and such. Is there a better way without having 3 duplicate fields?
Thanks.

 

[jdraw]

I'd suggest creating a data model that represents your business -- that the database should support. In your post I see these "things" --about which some info should be stored to help your business.
CustomerServiceRep
ServiceRequest
Customer
Service
Device
ServiceTech
Repair
Patient
Complaint

I think you need to sort out what these things are exactly (write a 2 line definition for each-- it will help you understand your business, and help readers understand your issues/business).

Here's a link for tutorial on designing a database.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[Pat Hartman]

With verrrrry few exceptions, each attribute should be stored in one and only one table. To relate tables, the primary key of the "parent" table is stored in the "child" table where it is called a Foreign key. Then to see them all together, use a query that joins the tables.

So in your example, CustomerID should be stored in tblService. Then when you want to show customer information, you join tblService to tblCustomer and choose columns from both tables.