merging tables

[bplus]

Hello first post here, I need some help!
Sorry not sure if this should go under queries or tables.

Anyway I'm working with quite a large access db (64mb).
I want to merge data from several tables into one table.

Its a supermarket db (not a real one).
Its got a customer and product table

Its also got a table called
agg_c_special_sales_fact_1997
with the fields customer_id and product_id
this shows what customers bought what product.

I want to merge the data from customer, product and agg_c_special_sales_fact_1997 into one big table. I want the merge to be permanent so I can see the which customer (and all their details) bought which product (all details) in one big table.

I ve been trying to do this all day (im quite new to SQL) I copied the data in
agg_c_special_sales_fact_1997 into a new table "new_sales_fact". I added a row called brand_name to that I want to copy from the product table

So far I ve come up with this:
SELECT product.brand_name INTO new_sales_fact
FROM product, agg_c_special_sales_fact_1997
WHERE product.product_id = agg_c_special_sales_fact_1997.product.id;


unfortuantely access asks me enter a parameter value when I run the query.
Could this becuase agg_c_special_sales_fact_1997 is huge, its got 86,000+records?

Can anyone help? I'd be most greatful this has been driving me insane all day!!

thanks in advance!

P.S the database is the test database that comes with Mondrian......

P.P.S I know its not good practice to have all data in one big table but for the purposes of what im doing it will simplfy things a lot!

 

[Pat Hartman]

A query is just as convenient as a table. Open the query builder, add the two tables, draw a join line to connect the tables on the related field, and just select whatever columns you want from each table. You can use this query as the RecordSource for a form or report or you can export it to Excel or a text file. It is rarely necessary to make temp tables.

 

[bplus]

Hi,
Thanks for that. Is there any way I can save the results of query as a permanent table?

The reason that I want to have it as a permanent table is that it Im doing some natural language generation stuff with it and it and if I just have one big table I can forget about sql completely and concentrate on the nlg stuff. Although I like playing around with sql my time for this project is short!!

 

[Pat Hartman]

In Access, you can use a query pretty much any place you would use a table so that's what I would recommend. Once you put the query's data into a table, you have to worry about is it still current?

To turn a select query into a make table query, open the query in design view and change the type.

 

[bplus]

thanks for all the help, got it working now.