Appending from Two Tables Info One!!

[rsmith64]

I'm trying to Append data from two different linked tables, 'Customer' and 'OrderH', into a table that I created. For some reason, it is only importing the data from the 'OrderH' table, and leaving the 'Customer' fields blank. What am I doing wrong?

 

[oharab]

have you tried just running it as a select query to check it is doing what you thinkit should?

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[thornmastr]

I am confused. if your customer table has nine records and the orders table has three records and your new tables has no records at all. After you do the append you expect a total of exactly twelve new records on your new table. Is that correct?
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[rsmith64]

Actually, the 'Customer' table and the 'OrderH' table are linked, both having related information. I'm trying to merge them onto a new table together using the Append query. They both have the same number of records, and both have a 'customerid' from which to reference to. The problem is that it's only appending information from the 'OrderH' table, and leaving the fields I have set from the 'Customer' table blank.

Does that make sense? Sorry, I'm just getting into this sort of thing, I'm doing my best.

 

[thornmastr]

It is starting to make much more sense. Tell me if this describes your situation. You append OrderH to your new table. Now, what you have is a record for record copy of OrderH on your new table.

Now, you want to update those records on your new table with information about specific customers as to how they pertain to specific orders on your new table.

Let me know if I’ve got your scenario. If so, it won’t take long to get you from where you are to where you want to be.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[rsmith64]

Yes! Except I'm actually appending the data at the same time, from the same Append Query, just from two different tables. Is that where I am running into a problem?

 

[thornmastr]

Well, your way is not working because an append query appends; it does not update.

You’ve gotten past the first step. Your copy of the product table exists and now you have to update matching records with customer information. I set up a dummy test. The following query does the update. You will have to substitute your table names and your values. This will get you started.

UPDATE testprod INNER JOIN testcustomer ON testprod.customerid = testcustomer.customerid SET testprod.customername = testcustomer.name


If you are running into other problems, post back.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com