Help Needed To Merge 2 Access Tables Into One!!!

moss2076
01-11-2004, 05:47 PM
Hi everyone, ive got another VB function i need help with..

I have an access database called 'db1'. It has 2 tables, DETAILS (name, age) and ORDERS (order number, amount). It is all linked up to VB with data controls, add, delete,save buttons etc..

On the click of a command button I want to merge details of whats being shown in both tables at that time, effectively creating a MERGERD TABLE called say PURCHASES.

Any ideas?

Cheers

Tom

Optikal
01-11-2004, 06:02 PM
Use a SQL SELECT statement with a JOIN in it. If you want to actually create a new table in the database rather than just in memory, you can use a SELECT INTO statement. If you don't know SQL, there should be a tutorial at www.w3schools.org/sql

Shurik12
01-11-2004, 06:07 PM
Hi,

You mean youd like to have a "physically" existing table in you database
or a recordset which would consist of the two tables' fields?
To be honest I don't see why one would like to do the first, anyway if you need it, use "CREATE TABLE" sql, or better yet create that [MERGED TABLE] at design time.
Then run "INSERT INTO". Or use .AddNew method of the Recordset object. (By the way are the tables Details and Orders related to each other?)

Regards,
Shurik.

moss2076
01-11-2004, 07:02 PM
Hi,

You mean youd like to have a "physically" existing table in you database
or a recordset which would consist of the two tables' fields?
To be honest I don't see why one would like to do the first, anyway if you need it, use "CREATE TABLE" sql, or better yet create that [MERGED TABLE] at design time.
Then run "INSERT INTO". Or use .AddNew method of the Recordset object. (By the way are the tables Details and Orders related to each other?)

Regards,
Shurik.

I have an empty table called 'mergerdfields' waiting for the data from the other 2 tables to fill it so it can be saved. I want to create customer orders - from customer details and order details. eg (table1)customer name, age + (table2) order number, order amount = table 3 (customer name, age, order number,order amount).

Cheers

Tom

moss2076
01-12-2004, 05:25 AM
Hi,

You mean youd like to have a "physically" existing table in you database
or a recordset which would consist of the two tables' fields?
To be honest I don't see why one would like to do the first, anyway if you need it, use "CREATE TABLE" sql, or better yet create that [MERGED TABLE] at design time.
Then run "INSERT INTO". Or use .AddNew method of the Recordset object. (By the way are the tables Details and Orders related to each other?)

Regards,
Shurik.

Yes the 2 tables are related via the field 'ordernumber' eg table1 - cutstomer name, customer age, ordernumber. table2 - orderprice, orderamount, ordernumber.

Tom

Shurik12
01-12-2004, 04:35 PM
OK,
but I just would like to repeat my question:
>... way are the tables Details and Orders related to each other?

moss2076
01-12-2004, 05:07 PM
OK,
but I just would like to repeat my question:
>... way are the tables Details and Orders related to each other?

They are related, because 1 customer can make many purchases. Ive linked them as a one-to-many via ordernumber. That way you can see what customer has ordered what product.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum