Combining tables, and problem with subdatasheets

[davewalter]

I am fairly new to using Access. I have 3 tables that I would like to combine as one. The field that makes each record unique, Part Number, exists in all 3 tables. However, there are some records which do not exist in all the tables. I tried using a query, but this only extracts those records which exist in all 3 tables. Any ideas how I can fit all my data onto one table?
Also, while playing around trying to do this, I looked into subdatasheets. I gave up on this idea, but now one of my tables has a column of + signs. When I click on these, the 'Insert Datasheet' box pops up. Is there a way to remove this column? Or doesn't it matter?

Thanks in advance for any help,

Dave

 

[Fizzio]

From your description, it seems like you are trying to merge the 3 tables into 1. There are two ways of doing this. The easiest is to use an append query (you will need 2 or 3 depending on which method) and appending the data from each table either into a new table or adding the data from 2 tables into the other one. The other alternative is to use a maketable query and using a Union query to amalgamate all 3 tables (but this is more tricky)

Regarding the subdatasheet. This allows the viewing of records associated with that particular record. For example a table of customers may be linked to a table of orders via the Customer ID and to show the order for each customer in the table view, you would use the subdatasheet.
It is much more aesthetically pleasing and more functional to use a form / subform combination instead as this aloows more flexibility on display, control of data input etc.

 

[davewalter]

I used an append query, but I still end up with a table with only the entries that are commen to all 3 tables.

 

[Fizzio]

Do all three tables have the same fields in them or does each table have different fields in.

If they have the same fields in, add only one table to the append query and append only this table to the existing one

You will have to repeat this with the other table also. To add all tables at once, you will need to use a combination of a maketable and union query (which is more difficult)

eg
Table 1 - keep this as the base table
qry1 Append Table2 to Table1
qry2 Append Table3 to Table1
(this assumes all tables have the same/compatible fields)

 

[mac.vba]

Now, I have a table with Subdatasheet in it which can be seen with a click on + sign.
I have a query as below:
SELECT [SETUP - NameAll].* INTO [SETUP - Name]
FROM [SETUP - NameAll];

where, SETUP - NameAll is my main table & SETUP - Name is the table which I want the records updated in.

The above query lets me to copy entire rows without subdatasheets. Can someone please tell me the query which copys data with subdatasheets in a new table?

Thanks you,