Newbie append querie - Nightmare, please help!

[Ady]

Hi There

Using Office 2000
=============
I have an access database. I am trying to import data from an excel
spreadsheet into the DB via a macro. Within the spreadsheet I have 2
worksheets:

Start Date Jan 2000
Blanket Orders

Both worksheets within the spreadsheet contain the same column headings. In
access I have created a macro that will automatically import and create a
table for each worksheet:

Table: Start Date Jan 2000
Table: Blanket Orders

However what I am really trying to do is to merge all the data together in
one table (via macro). The data is in the same format on both
worksheets.Same column headings etc.

I tried the Append Query option and even though all the columns are the same
in both worksheets I get the following
error message:

Microsoft Access set 319 field(s) to Null due to a type conversion failure,
and it didn't add 0 records to the table due to key violations, 0 record(s)
due to lock violations, and 0 record(s) validation rule violations.

I probably couldn't get it more wrong if I tried to!!!!!!!

If it is any help here is the SQL view:

INSERT INTO [Blanket Order List] ( [Job No#], [Order No], [Part Number],
Description, QTY, Customer, [Date Order Rec'd], [Date Sent], [Due Date],
[Price Each], [Total Value], [Issue/cmnts], [Stock Amount] )
SELECT [Sales Order List].[Job No#], [Sales Order List].[Order No], [Sales
Order List].[Part Number], [Sales Order List].Description, [Sales Order
List].QTY, [Sales Order List].Customer, [Sales Order List].[Date Order
Rec'd], [Sales Order List].[Date Sent], [Sales Order List].[Due Date],
[Sales Order List].[Price Each], [Sales Order List].[Total Value], [Sales
Order List].[Issue/cmnts], [Sales Order List].[Stock Amount]
FROM [Sales Order List];

Thank you in advance for any help offered. All very much
appreciated!

Regards
Ady

 

[Steve Schapel]

Ady,

More questions than answers from me at the moment, I'm afraid. What is
the connection between your imported tables Start Date Jan 2000 and
Blanket Orders, and the tables used in your query Blanket Order List and
Sales Order List? What happens if you try an Append query directly from
one of your imported tables into the other? Does your Append Query
result in any records at all being appended, or are none of them coming
through? In your TransferSpreadsheet macro, is the 'Has Field Names'
argument set as applicable? Try a test... temporarily remove all the
date fields from the Append Query, and try it then.

 

[Ady]

Hi Steve

Thanks for your prompt reply.

I have 2 tables:
1. Start Date Jan 2000
2. Blanket Orders

The append query is attempting to place all the records from "Blanket Orders
table" into the "Start Date Jan 2000 table".
When I do this in design view, the "Blanket Orders" Column headers mirror
the headers in "Start Date Jan 2000 table".

I have uploaded a screen print to demonstrate:
http://www.symonsnet.fsnet.co.uk/help/access1.jpg

You wrote:
What happens if you try an Append query directly from

one of your imported tables into the other? Does your Append Query
result in any records at all being appended, or are none of them coming
through?

No records are coming through!

You wrote:
In your TransferSpreadsheet macro, is the 'Has Field Names'

argument set as applicable?

Both of these are set to "Yes"

You wrote:
Try a test... temporarily remove all the date fields from the Append Query,
and try it then.

Removed all the date columns from both tables then ran the query. Still got
the same result!!

Many thanks for you assistance in my nightmare

Ady

Ady,

More questions than answers from me at the moment, I'm afraid. What is
the connection between your imported tables Start Date Jan 2000 and
Blanket Orders, and the tables used in your query Blanket Order List and
Sales Order List? What happens if you try an Append query directly from
one of your imported tables into the other? Does your Append Query
result in any records at all being appended, or are none of them coming
through? In your TransferSpreadsheet macro, is the 'Has Field Names'
argument set as applicable? Try a test... temporarily remove all the
date fields from the Append Query, and try it then.

--
Steve Schapel, Microsoft Access MVP

Hi There

Using Office 2000
=============
I have an access database. I am trying to import data from an excel
spreadsheet into the DB via a macro. Within the spreadsheet I have 2
worksheets:

Start Date Jan 2000
Blanket Orders

Both worksheets within the spreadsheet contain the same column headings. In
access I have created a macro that will automatically import and create a
table for each worksheet:

Table: Start Date Jan 2000
Table: Blanket Orders

However what I am really trying to do is to merge all the data together in
one table (via macro). The data is in the same format on both
worksheets.Same column headings etc.

I tried the Append Query option and even though all the columns are the same
in both worksheets I get the following
error message:

Microsoft Access set 319 field(s) to Null due to a type conversion failure,
and it didn't add 0 records to the table due to key violations, 0 record(s)
due to lock violations, and 0 record(s) validation rule violations.

I probably couldn't get it more wrong if I tried to!!!!!!!

If it is any help here is the SQL view:

INSERT INTO [Blanket Order List] ( [Job No#], [Order No], [Part Number],
Description, QTY, Customer, [Date Order Rec'd], [Date Sent], [Due Date],
[Price Each], [Total Value], [Issue/cmnts], [Stock Amount] )
SELECT [Sales Order List].[Job No#], [Sales Order List].[Order No], [Sales
Order List].[Part Number], [Sales Order List].Description, [Sales Order
List].QTY, [Sales Order List].Customer, [Sales Order List].[Date Order
Rec'd], [Sales Order List].[Date Sent], [Sales Order List].[Due Date],
[Sales Order List].[Price Each], [Sales Order List].[Total Value], [Sales
Order List].[Issue/cmnts], [Sales Order List].[Stock Amount]
FROM [Sales Order List];

Thank you in advance for any help offered. All very much
appreciated!

Regards
Ady

 

[Steve Schapel]

Ady,

To be fair, I should point out that this type of problem, especially
when you are working with imported data, is not uncommon. There are a
lot worse nightmares that can happen. And the good news is, it will
eventually get sorted out.

I can't see at the moment what specifically is causing the problem. But
this is what I would do next. You have to go into a trouble-shooting
approach. Delete one field at a time from the query (you don't need to
delete fields from the tables probably), and try running the query, and
keep going until it appends data. Then you will know that the last
field deleted is related to at least one of the causes of the problem,
and you can have a look at the data in that field in both tables,
compare data types, check there is no odd-ball data in there, etc, in an
attempt to pinpoint the trouble.

But I am still confused by the SQL you posted, as compared with what you
said is happening. In the SQL, and the screenshot, it says...
INSERT INTO [Blanket Order List]
.... and...
FROM [Sales Order List]
Whereas shouldn't this be...
INSERT INTO [Start Date Jan 2000]
.... and...
FROM [Blanket Orders]

By the way, as an aside, it is not a good idea to use a ' or a # or a /
as part of the name of a field, and I would recommend that this be
changed (but this won't be the cause of the immediate problem).

 

[Ady]

Hi Steve

Will start the process and let you know!!

many thanks for your help!


Ady

Ady,

To be fair, I should point out that this type of problem, especially
when you are working with imported data, is not uncommon. There are a
lot worse nightmares that can happen. And the good news is, it will
eventually get sorted out.

I can't see at the moment what specifically is causing the problem. But
this is what I would do next. You have to go into a trouble-shooting
approach. Delete one field at a time from the query (you don't need to
delete fields from the tables probably), and try running the query, and
keep going until it appends data. Then you will know that the last
field deleted is related to at least one of the causes of the problem,
and you can have a look at the data in that field in both tables,
compare data types, check there is no odd-ball data in there, etc, in an
attempt to pinpoint the trouble.

But I am still confused by the SQL you posted, as compared with what you
said is happening. In the SQL, and the screenshot, it says...
INSERT INTO [Blanket Order List]
... and...
FROM [Sales Order List]
Whereas shouldn't this be...
INSERT INTO [Start Date Jan 2000]
... and...
FROM [Blanket Orders]

By the way, as an aside, it is not a good idea to use a ' or a # or a /
as part of the name of a field, and I would recommend that this be
changed (but this won't be the cause of the immediate problem).

--
Steve Schapel, Microsoft Access MVP

Hi Steve

Thanks for your prompt reply.

I have 2 tables:
1. Start Date Jan 2000
2. Blanket Orders

The append query is attempting to place all the records from "Blanket Orders
table" into the "Start Date Jan 2000 table".
When I do this in design view, the "Blanket Orders" Column headers mirror
the headers in "Start Date Jan 2000 table".

I have uploaded a screen print to demonstrate:
http://www.symonsnet.fsnet.co.uk/help/access1.jpg

 

[Ady]

Hi There

We have success (I Think!!)

One of the columns was formatted for currency and one for text. So therefore
made them both text. The query then ran, but still could not find the data!

I was looking for the imported data in the query. It was placing it all in
the table. This was fine I just didn't realise!

Many thanks again!

Ady

Hi Steve

Will start the process and let you know!!

many thanks for your help!


Ady

Ady,

To be fair, I should point out that this type of problem, especially
when you are working with imported data, is not uncommon. There are a
lot worse nightmares that can happen. And the good news is, it will
eventually get sorted out.

I can't see at the moment what specifically is causing the problem. But
this is what I would do next. You have to go into a trouble-shooting
approach. Delete one field at a time from the query (you don't need to
delete fields from the tables probably), and try running the query, and
keep going until it appends data. Then you will know that the last
field deleted is related to at least one of the causes of the problem,
and you can have a look at the data in that field in both tables,
compare data types, check there is no odd-ball data in there, etc, in an
attempt to pinpoint the trouble.

But I am still confused by the SQL you posted, as compared with what you
said is happening. In the SQL, and the screenshot, it says...
INSERT INTO [Blanket Order List]
... and...
FROM [Sales Order List]
Whereas shouldn't this be...
INSERT INTO [Start Date Jan 2000]
... and...
FROM [Blanket Orders]

By the way, as an aside, it is not a good idea to use a ' or a # or a /
as part of the name of a field, and I would recommend that this be
changed (but this won't be the cause of the immediate problem).

--
Steve Schapel, Microsoft Access MVP

Hi Steve

Thanks for your prompt reply.

I have 2 tables:
1. Start Date Jan 2000
2. Blanket Orders

The append query is attempting to place all the records from "Blanket Orders
table" into the "Start Date Jan 2000 table".
When I do this in design view, the "Blanket Orders" Column headers mirror
the headers in "Start Date Jan 2000 table".

I have uploaded a screen print to demonstrate:
http://www.symonsnet.fsnet.co.uk/help/access1.jpg

 

[Steve Schapel]

Ady,

Congratulations on successfully tracking down the problem. Please don't
hesitate to ask again if you need further help in the future.

 

[Steve Small]

Make a copy of Blanket Order List as a backup. Delete the
original that was just copied. Copy the Sales Order List
table, structure only, to be a new Blanket Order List.
Repopulate the Blanket Order list anyway you can. You
could export the backup to Excel and then re-import itif
you wanted to do it quick and dirty.

Run your SQL.

There is probably just a small difference in one field
structure that prevents the table from loading correctly.
You just want to ensure the tables are defined "exactly"
the same. Sometimes you just need a second set of eyes to
see a small difference between seemingly like items.

It could also be the data. If you download it to Excel you
should be able to get a better look at it and may see that
a date or numeric field actually contains text.

Good Luck.

Steve

 

[Ady]

Thanks Steve!

Got it sorted now! (See above threads!) Many thanks anyway!

By the way you are not by any chance the same Steve Small who supports
Birmingham City FC are You?

Regards

Ady