Append query wont run because:

[Bob H]

I have 8 tables in access 2007 database, and I want to append all the
separate tables into one table. To do this I created a new table with
all the feilds which are used in the 8 separate tables. 6 of these
fields are common, and 8 are unique for each type of tool.
Some of the feilds in the new table may well be Null value because there
is no data for that feild, as in for one table there is a feild called
'Lenght' but that doesn't appear in ay other table.

I then created a new query and added my first table's feilds to it, then
clicked on 'append' in the menu bar (to the new table) and made sure all
the fields matched up before running the query, which gave me multiple
errors:
'No feilds set to Null type due to a type conversion failure, and didn't
add the records to the table due to key violations, 0 records due to
lock violations, and 0 records due to validation rules violations.'

Thanks very much access 2007, but I know there are no rule validations
on either the new table or the new one I want to append the records to.
I've no idea about the other errors, becuase I am not sure what they all
mean.

Help please.

Thanks

 

[Ken Sheridan]

I'd recommend that you rethink your approach to this. By creating a single
'one-size-fits-all' table you are losing control over the integrity of the
data as there is nothing to stop values being entered into a row where they
are inappropriate to the tool in question; your example of 'length' is a case
in point.

What you have here by the sound of things is a type/sub-type scenario. The
type 'tools' shares all the attributes represented by the 6 common columns,
so a table with those columns (and a primary key column such as an autonumber
if one of the columns doesn't serve as a 'natural' key) would model the type.
The columns which represent attributes unique to each type of tool would go
in one or more other tables as sub-type of the tools type. This would be
related to the (super)type table in a one-to-one relationship by virtue of
its primary key also being a foreign key referencing the key of the
(super)type table. If the key of the (super)type table is an autonumber, the
key of the subtypes table(s) can't be an autonumber of course, but a
straightforward long integer number data type.

You won't necessarily have 8 sub-types of course. Of the 8 you could have 4
which share common attributes not shared by the other 4, so these 4 would be
a single sub-type. And this in turn could have its own sub-types if for
instance two sets of 2 of the 4 had attributes common to each pair but not
shared by the other pair.

With a model such as this only data can be entered which are legitimate
values of an attribute belonging to the sub-type represented by the column in
which that attribute is represented by a column, so the database is far more
robust than would be the case with a single table with Nulls at many column
positions in many rows. Bringing all the data together when required is of
course merely a question of joining the tables in a query. Usually LEFT
OUTER JOINs are needed of course as the type table does not have matches in
all of its sub-type(s). The result set of a query which joined all the
tables would be the single table you are aiming at.

Recasting your current tables into this structured set is really just an
extension of what you are currently attempting, but hopefully without the
problems you've encountered. First you start by appending the common columns
from all tables to the topmost 'tools' type table, then the common columns
from restricted sets to sub-types of the tools type. And so on down the line
until you have everything where it should be.

Ken Sheridan
Stafford, England

 

[Bob H]

I went with this approach as a result of a reply to a post I made in the
tablesdesign group about where I asked if my existing setup of tables
could bve improved upon.
The basic idea there was to create a master tooltype table then append
all the other tables to it after of course creating a table with all the
fields from all the tables; there are 16 fields in total for all the
tables, of which 6 are common, and the remainder unique.

Anyway, from what you say, is the idea to append each column of data
from each table on a 1 column by 1 column basis?
If that be rthe case then I may well do that, but other than that as I
am still learning, I may well leave my existing setup as is.

Thanks

 

[Bob H]

I actually just tried a 1 by column approach to append the records, but
I recieved the same erors as before, even with just 1 column!
Thanks

 

[Pete D.]

Bob,
Before trying any of this make a couple of backups and put them where you
won't get confused and overwrite all your hard work loading tools.

You have all these tables and before you can append them you need to do some
prep. First all like fields with data have to have same type and length.
Right now you have some that are different and these will not import, as a
matter of fact I think that is your current problem.

You have many keys and relationships. None of your current relationships
should
exist as you have manufactures in one type tool table related to manufacture
in another type tool table. These serve no purpose and should be deleted.

You have many key fields, some of which you should have but in the situation
you have right now you will probally need to remove those keys until you
parse these into one table, do not delete the data in these keyfields, just
remove them as keyfields.

Also since this is a new thread, Ken may not know
that this was the first step to eliminating your multi tables and then
afterwards creating sub types. I think once you merge these into one table
you will be able to see the logical sub tables you should create for each.

You could also really use some lookup tables such as fire extingusher types.
You have many like items with various spelling errors and the lookup tables
or lists will help cleanup your data.

I also recommend before you append tables together you make a field in your
new table for fldtooltype and in the update query to the new field you don't
currently have in your new empty table you intend to append to.

For each tool type table update this new table tooltype field with something
like fldToolType: "FireExtingushers" That way you don't have to flag them
later. It will make it easier for folks here to help you create your sub
tables. Pete

 

[Ken Sheridan]

You don't append column by column; the principle is that you append subsets
of columns to the relevant tables. There is some merit in first appending
everything to a temporary single table, but rather than trying to define this
table first you can use a UNION ALL query to tack your 8 tables together into
1 and then use this query as the basis for a 'make table' query to create a
new table. This should not give rise to any errors. Where there are tables
which have columns not represented in other tables return Nulls in addition
to the columns from the other tables.

I can probably explain this more easily with a simple hypothetical example.
Lets assume we start with three tables, table1, table2 and table3. All three
have columns field1,field2 and field3 in common. Table2 has columns
field1,field2, field3, field4 and field5; and table 3 has columns
field1,field2, field3, field6 and field 7. To tack these three tables
together in a UNION ALL query you'd do this:

SELECT field1,field2,field3,
NULL AS field4,NULL AS field5,
NULL AS field6,NULL AS field7
FROM table1
UNION ALL
SELECT field1,field2, field3,
field4,field5,NULL,NULL
FROM table2
UNION ALL
SELECT field1,field2, field3,
NULL,NULL,field6,field7
FROM table3;

This query will give you a result set which equates to what you'd get if you
appended all rows from the three tables independently into a new table of 7
columns. Note that you only have to give the Nulls a column heading in the
first part as its this which determines the column headings for the result
set.

The next step is save the above query and the create a 'make table' query
based on the query to create a new table, called TempTable say. That should
not give rise to any errors as a make table query produces a very simple
table with no indexes. The next step is to add an autonumber column, ToolID
say, to TempTable as its primary key.

You can now design the individual tables to represent the types and
sub-types. So lets call the main type table Tools; this will have the
columns common to all so it will have ToolID (autonumber), field1,field2, and
field3 columns. Rows will be amended to this with:

INSERT INTO Tools (ToolID, field1,field2,field3)
SELECT ToolID, field1,field2,field3
FROM TempTable;

Next you'll append rows into a table representing the subtype originally
represented by table2. This table, which we'll call ToolsType2 (you'd give
it a real name of course reflecting what it represents) will have columns
ToolID (not an autonumber in this case), field4 and field5, so the append
query would be:

INSERT INTO ToolsType2 (ToolID, field4,field5)
SELECT ToolID, field4,field5
FROM TempTable
WHERE field4 IS NOT NULL
OR field5 IS NOT NULL;

Finally you'd do the same for the other sub-type with:

INSERT INTO ToolsType2 (ToolID, field6,field7)
SELECT ToolID, field6,field7
FROM TempTable
WHERE field6 IS NOT NULL
OR field7 IS NOT NULL;

With your 8 tables the process would be exactly the same, but over more
stages of course, appending the relevant columns into firstly the main
(super)type table and then into each sub-type table. In every append query
you'd append the ToolID as this is what ties everything together, being the
primary key of the main type (Tools) and both a primary key and foreign key
in all the sub-types.

Foreign keys columns such as manufacturerID would be in the main Tools table
as this is an attribute common to all tools. The sub-type tables would have
those columns which represent attributes specific to the sub-type, so your
length column would be in whatever table represents the sub-type of tools for
which the length attribute is appropriate.

Once you have everything correctly represented in the various tables the
TempTable is redundant and can be deleted.

Ken Sheridan
Stafford, England

 

[Bob H]

Hi Pete,
ok, I realized later what the problem was, and it was as you
have said below.
So I then went about correcting the errors in the data type for the
different tables as well as all the spelling errors.
I then created a FieldType table with all the fields I listed from all
of my tables; now there are 17 fields.
Since I have done that I have managed to append/import all of my tables
into the one FieldType table.
I have deleted the keyfields and only have one in the main table now,
that being ToolTypeID. I have also deleted the other reletionships that
are now not needed.

Bob,
Before trying any of this make a couple of backups and put them where you
won't get confused and overwrite all your hard work loading tools.

You have all these tables and before you can append them you need to do some
prep. First all like fields with data have to have same type and length.
Right now you have some that are different and these will not import, as a
matter of fact I think that is your current problem.

You have many keys and relationships. None of your current relationships
should
exist as you have manufactures in one type tool table related to manufacture
in another type tool table. These serve no purpose and should be deleted.

You have many key fields, some of which you should have but in the situation
you have right now you will probally need to remove those keys until you
parse these into one table, do not delete the data in these keyfields, just
remove them as keyfields.

Also since this is a new thread, Ken may not know
that this was the first step to eliminating your multi tables and then
afterwards creating sub types. I think once you merge these into one table
you will be able to see the logical sub tables you should create for each.

You could also really use some lookup tables such as fire extingusher types.
You have many like items with various spelling errors and the lookup tables
or lists will help cleanup your data.

I also recommend before you append tables together you make a field in your
new table for fldtooltype and in the update query to the new field you don't
currently have in your new empty table you intend to append to.

I am not altogether following you with what you say here.
I understand about making a new field, but how do you mean about the rest?
My apologies for not quite getting it.

For each tool type table update this new table tooltype field with something
like fldToolType: "FireExtingushers" That way you don't have to flag them
later. It will make it easier for folks here to help you create your sub
tables. Pete

Yes good idea that, I wiil do that tomorrow.

I have the updated tables a t work and so am not able to do anything
here just now, but I'll make some more alterations when I'm back tomorrow.

Thanks

 

[Bob H]

No I realised you don't append by columns , I did that to test if what I
was doing was right, and it wasn't.
I realised later what and where the problems lay, which I then corrected
today.

Thanks for your detailed explanation which I will print out and digest
before I attempt to do anything like.

 

[Pete D.]

Cool your doing well, I think it will be quite a useful datafile when your
done...well being as it is home grown you'll probally never be done as
you'll keep thinking of improvements. But it will be something you can
continue to improve and make into what you want.