New table from 2 existing tables

P

Paul Wilson

I can't find anything that remotely works to create a new table from 2
existing table which have exactly the same layout. Can someone help?
 
J

John W. Vinson

I can't find anything that remotely works to create a new table from 2
existing table which have exactly the same layout. Can someone help?
Click to expand...

Not without knowing more about it. Why would you WANT to have the same data
stored redundantly in three tables - divided between two, and then stored
redundantly in a third??

You can do it in two steps using queries - run a MakeTable query from one of
the existing tables into a new tablename; and then an Append query. But I'd
like to know the background and why you're trying to do this.

John W. Vinson [MVP]
 
P

Paul Wilson

The person who was here before me has crap all over the place for a project,
I'm trying to merge all together in a access dbase to make getting
information required easier as opposed to having to search through countless
numbers of excel dbases.
Also to make it as idiot proof as possible for when a receptionist needs to
enter more data, I have set up a form which has a series of drop down values
and the rest is manual input.

I have been able to use the append successfully for merging 1 talbe with
another but now I have 4 more dbases that I wish to add and all have given me
the same error message.

I'm using

INSERT INTO details
SELECT *
FROM import1;

Microsoft Office Access Can't append all the records in the append query.
Microsoft Office Access set 0 field(s) to Null due to a type conversion
failure, and it didn't add 23 record(s) due to lock violations, and 0
record(s) due to validation rule violations.

Once again these to tables have been imported the same way have all the same
headings and are all in the same order.
 
J

Jeanette Cunningham

Hi Paul,
a couple of things can get in the way of doing this sort of thing. I don't
pretend to have all the answers to this but here are some pointers.
The relationships between the tables ( if set up at this point) will make a
difference to what can be appended in what order, due to issues with
needing primary keys in parent tables before putting records in child
tables.

I don't know all the details of your setup, sometimes it is easier to put
all the records together in Excel in one worksheet, then import them.

I will be interested to see what others have to say on this problem.

Jeanette Cunningham
 
P

Paul Wilson

Thanks Jeanette. I have done that. Thanks for your reply but there are now
relationship between any of these tables. I was just wanting to tag one table
on the end of the other to create a combined table. Now I just would like to
understand why it came up with the error to see whether it has to do with the
way I've set it up.

Cheers
 
J

John W. Vinson

I have been able to use the append successfully for merging 1 talbe with
another but now I have 4 more dbases that I wish to add and all have given me
the same error message.

I'm using

INSERT INTO details
SELECT *
FROM import1;

Microsoft Office Access Can't append all the records in the append query.
Microsoft Office Access set 0 field(s) to Null due to a type conversion
failure, and it didn't add 23 record(s) due to lock violations, and 0
record(s) due to validation rule violations.
Click to expand...

Lock violations suggests that you have a Primary Key or a relationship set,
and that adding the new records would either create a duplicate value in the
primary key field or violate referential integrity. Does either details or
import1 have a Primary Key defined - or a field with a unique Index? Might
there be (say) sequence numbers in the two tables, both starting 1,2,3 (and
therefore causing duplicates)?

John W. Vinson [MVP]
 
P

Paul Wilson

Yes, on import to automatically assigned an ID field which was used as a
primary key. After deleting that it worked perfect.

Cheers--
Paul Wilson
 
Top