Creating sub-tables from a master table

[Guest]

I need to create several different tables from a master table, but I also
need to add fields to the sub-tables that are not in the master. So...

The master table has fields A, B, and C. I need to create a table with
fields B, C and new field D.

I tried doing a Make Table query, but I can't add field D since it is not in
the master table.

 

[Guest]

Hi Jelly''s,

You shouldn't have repeated fields across tables if they are holding the
same data.

Apart from that, you could use the make table query to create as much of the
table as you can, then open the table in design view to add the extra fields.

Personally I just use Design View and build the tables from scratch...

Hope this helps.

Damian.

 

[Guest]

Thanks for the help Damian. I ended up using an Append query to copy the
common fields from one table to the next and then an Update query to fill out
the additional fields. It seems to be working well enough.

Thanks again.

 

[Jeff Boyce]

Please re-read Damian's reply. The recommendation was that you NOT have
multiple copies of common fields across multiple tables. The one exception
could be if the "common fields" are, in fact, a multi-column composite
primary key. You didn't say if that was the case.

One very good reason not to have the same value in more than one table
(except primary-foreign keys) is the problem of keeping the values
synchronized -- if one table's value is updated, how will you keep the value
in table2 updated?

If you describe a bit more about WHY you wish to do this, the newsgroup
readers may be able to offer appropriate alternatives.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/