Importing changing data from Excel

[Guest]

I am working on a project where I will get a daily export of data from a website into excel. It's a full export every day with new records plus changing fields to existing records as the website is updated. For example the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow me to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but override existing fields.

I'm pretty new to access so please can any replies be relayed in simple terms with specific actions so I can understand it

I'm using access & excel 2003

Help!!

 

[Ken Snell]

Assuming that the data in EXCEL's rows have a unique identifier for them,
which then matches the primary key that you're using in the ACCESS table for
that record, you could use a combination of an update query followed by an
append query to do what you want.

Import the data into a temporary table.

Create an update query that joins (INNER) your permanent table to the
temporary table using the primary key field. Select the appropriate fields
from the temporary table as the "Update To:" values for the matching fields.
You'll need to use a syntax similar to this for this:
[TemporaryTableName].[FieldName]

Then create an append query that is based on the temporary table and the
permanent table. Make the target table (the table to which the records will
be appended) be the permanent table. Join the two using the primary key
field. Make it a LEFT or RIGHT join (right click on the join line and select
the option that gives all records from temporary table and just the matching
records from the permanent table). Put all the fields from the temporary
table onto the grid, and be sure that they match up to the appropriate
fields in the pemanent table. Put the primary key field from the permanent
table on the grid, delete any field name in the "Append To:" cell, and type
this in the "Criteria:" cell under this field:
Is Null

Run the update query. Then run the append query.
--

Ken Snell
<MS ACCESS MVP>

I am working on a project where I will get a daily export of data from a

website into excel. It's a full export every day with new records plus
changing fields to existing records as the website is updated. For example
the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow me

to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but override existing fields.

I'm pretty new to access so please can any replies be relayed in simple

terms with specific actions so I can understand it

 

[Guest]

I thought I was there on this and the info given was really helpful, but when I try and run the update query, it asks me to enter parameter values for each field? Also when I try and run the append query (without having run the update query) I get an error that says 'cannot insert data with action query' - I've obviously gone a bit wrong somewhere - help?!

I assume I have to delete the data from the temp file before each new import? It will only let me import if I have all the data types set to text - is this a problem with the data in the excel spreadsheet?

Finally - I did proceed to run the update query by just clicking on ok for each request for a parameter value, I then had another error that told me I had 14 key violations in my table - oh dear

As you can see I am a complete novice, but determined to learn

Any help appreciated

Assuming that the data in EXCEL's rows have a unique identifier for them,
which then matches the primary key that you're using in the ACCESS table for
that record, you could use a combination of an update query followed by an
append query to do what you want.

Import the data into a temporary table.

Create an update query that joins (INNER) your permanent table to the
temporary table using the primary key field. Select the appropriate fields
from the temporary table as the "Update To:" values for the matching fields.
You'll need to use a syntax similar to this for this:
[TemporaryTableName].[FieldName]

Then create an append query that is based on the temporary table and the
permanent table. Make the target table (the table to which the records will
be appended) be the permanent table. Join the two using the primary key
field. Make it a LEFT or RIGHT join (right click on the join line and select
the option that gives all records from temporary table and just the matching
records from the permanent table). Put all the fields from the temporary
table onto the grid, and be sure that they match up to the appropriate
fields in the pemanent table. Put the primary key field from the permanent
table on the grid, delete any field name in the "Append To:" cell, and type
this in the "Criteria:" cell under this field:
Is Null

Run the update query. Then run the append query.
--

Ken Snell
<MS ACCESS MVP>

I am working on a project where I will get a daily export of data from a

website into excel. It's a full export every day with new records plus
changing fields to existing records as the website is updated. For example
the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow me

to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but override existing fields.

I'm pretty new to access so please can any replies be relayed in simple

terms with specific actions so I can understand it

I'm using access & excel 2003

Help!!

 

[Ken Snell]

As I cannot see the queries that you have created, nor know the names of
your tables, etc., you'll need to give me some more info so that we can work
through this.

The fact that you're being asked for parameter values indicates that the
names of the criteria or update values do not match any names in the query's
source tables.

Please post the names and structure of the temporary and permanent tables.
Then post the SQL statement of each query (open the query in design view,
then click on icon in top left on toolbar (small dropdown arrow) to select
the SQL view; copy the entire statement and paste into your post).

Let's see what you have. And yes, you would need to delete all records from
the temporary table after you've run the update and apped queries (you use a
delete query to do this) so that the temporary table is empty for the next
import.

--

Ken Snell
<MS ACCESS MVP>

I thought I was there on this and the info given was really helpful, but

when I try and run the update query, it asks me to enter parameter values
for each field? Also when I try and run the append query (without having
run the update query) I get an error that says 'cannot insert data with
action query' - I've obviously gone a bit wrong somewhere - help?!

I assume I have to delete the data from the temp file before each new

import? It will only let me import if I have all the data types set to
text - is this a problem with the data in the excel spreadsheet?

Finally - I did proceed to run the update query by just clicking on ok

for each request for a parameter value, I then had another error that told
me I had 14 key violations in my table - oh dear

As you can see I am a complete novice, but determined to learn

Any help appreciated

Assuming that the data in EXCEL's rows have a unique identifier for them,
which then matches the primary key that you're using in the ACCESS table for
that record, you could use a combination of an update query followed by an
append query to do what you want.

Import the data into a temporary table.

Create an update query that joins (INNER) your permanent table to the
temporary table using the primary key field. Select the appropriate fields
from the temporary table as the "Update To:" values for the matching fields.
You'll need to use a syntax similar to this for this:
[TemporaryTableName].[FieldName]

Then create an append query that is based on the temporary table and the
permanent table. Make the target table (the table to which the records will
be appended) be the permanent table. Join the two using the primary key
field. Make it a LEFT or RIGHT join (right click on the join line and select
the option that gives all records from temporary table and just the matching
records from the permanent table). Put all the fields from the temporary
table onto the grid, and be sure that they match up to the appropriate
fields in the pemanent table. Put the primary key field from the permanent
table on the grid, delete any field name in the "Append To:" cell, and type
this in the "Criteria:" cell under this field:
Is Null

Run the update query. Then run the append query.
--

Ken Snell
<MS ACCESS MVP>

I am working on a project where I will get a daily export of data from

a
website into excel. It's a full export every day with new records plus
changing fields to existing records as the website is updated. For example
the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow

me
to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but

override
existing fields.

I'm pretty new to access so please can any replies be relayed in

simple
terms with specific actions so I can understand it

I'm using access & excel 2003

Help!!

 

[Guest]

The name of the temporary file is fullexporttemp and has the following fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the same fields (I need to add more as I will use this table to extract cases for QC purposes and record the results, but I wanted to get the import/ update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case No] = [full exporttemp].[Case No] SET [Lead details].[Case No] = fullexporttemp.[Case No], [Lead details].Client_Name = fullexporttemp.Client_Name, [Lead details].[Date Lead Received] = fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] = fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email, [Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan Amount Required] = fullexporttemp.[Loan Amount Required], [Lead details].Valuation = fullexporttemp.Valuation, [Lead details].Term = fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone, [Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead details].[Time of Day] = fullexporttemp.[Time of Day], [Lead details].[Supporting Information] = fullexporttemp.[Supporting Information], [Lead details].Status = fullexporttemp.Status, [Lead details].[Client Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] = fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] = fullexporttemp.[Anticipated Completion], [Lead details].Comments = fullexport.fullexporttemp, [Lead details].[Appointment Booked] = fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] = fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] = fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] = fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee] = fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] = fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other fee] = fullexporttemp.[other fee], [Lead details].[other value] = fullexporttemp.[other value], [Lead details].[other date] = fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they are all in my perm 'lead details' table. I changed some of the details and added two new records before importing into the temp 'fullexporttemp' and that file looks correct

I've just looked at the update query in datasheet view and noticed that it has the orginal 14 records before they were changed, but not the two new ones.

The append query file has only the two new records in it, but none of the original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

As I cannot see the queries that you have created, nor know the names of
your tables, etc., you'll need to give me some more info so that we can work
through this.

The fact that you're being asked for parameter values indicates that the
names of the criteria or update values do not match any names in the query's
source tables.

Please post the names and structure of the temporary and permanent tables.
Then post the SQL statement of each query (open the query in design view,
then click on icon in top left on toolbar (small dropdown arrow) to select
the SQL view; copy the entire statement and paste into your post).

Let's see what you have. And yes, you would need to delete all records from
the temporary table after you've run the update and apped queries (you use a
delete query to do this) so that the temporary table is empty for the next
import.

--

Ken Snell
<MS ACCESS MVP>

I thought I was there on this and the info given was really helpful, but

when I try and run the update query, it asks me to enter parameter values
for each field? Also when I try and run the append query (without having
run the update query) I get an error that says 'cannot insert data with
action query' - I've obviously gone a bit wrong somewhere - help?!

I assume I have to delete the data from the temp file before each new

import? It will only let me import if I have all the data types set to
text - is this a problem with the data in the excel spreadsheet?

Finally - I did proceed to run the update query by just clicking on ok

for each request for a parameter value, I then had another error that told
me I had 14 key violations in my table - oh dear

As you can see I am a complete novice, but determined to learn

Any help appreciated

Assuming that the data in EXCEL's rows have a unique identifier for them,
which then matches the primary key that you're using in the ACCESS table for
that record, you could use a combination of an update query followed by an
append query to do what you want.

Import the data into a temporary table.

Create an update query that joins (INNER) your permanent table to the
temporary table using the primary key field. Select the appropriate fields
from the temporary table as the "Update To:" values for the matching fields.
You'll need to use a syntax similar to this for this:
[TemporaryTableName].[FieldName]

Then create an append query that is based on the temporary table and the
permanent table. Make the target table (the table to which the records will
be appended) be the permanent table. Join the two using the primary key
field. Make it a LEFT or RIGHT join (right click on the join line and select
the option that gives all records from temporary table and just the matching
records from the permanent table). Put all the fields from the temporary
table onto the grid, and be sure that they match up to the appropriate
fields in the pemanent table. Put the primary key field from the permanent
table on the grid, delete any field name in the "Append To:" cell, and type
this in the "Criteria:" cell under this field:
Is Null

Run the update query. Then run the append query.
--

Ken Snell
<MS ACCESS MVP>




I am working on a project where I will get a daily export of data from a
website into excel. It's a full export every day with new records plus
changing fields to existing records as the website is updated. For example
the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow me
to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but override
existing fields.

I'm pretty new to access so please can any replies be relayed in simple
terms with specific actions so I can understand it

I'm using access & excel 2003

Help!!

 

[Ken Snell]

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>

The name of the temporary file is fullexporttemp and has the following fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in

access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the

same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case

No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they

are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it

has the orginal 14 records before they were changed, but not the two new
ones.

 

[Guest]

Yes the temp fields are all text fomratted and I'm asked to provide parameters on all 39 fields (now I've corrected a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14 records due to key violations and 0 records due to record violations. To ignore click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>

The name of the temporary file is fullexporttemp and has the following fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in

access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the

same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case

No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they

are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it

has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of the original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Jim/Chris]

sisnce it is a complete Excel file why not delete the
records in your table and inport all of the new records
from excel.

Jim

-----Original Message-----
Yes the temp fields are all text fomratted and I'm asked

to provide parameters on all 39 fields (now I've corrected
a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion

failure, 14 records due to key violations and 0 records due
to record violations. To ignore click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>

The name of the temporary file is fullexporttemp and

has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but

the data types in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and

currently has exactly the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON

[Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON

[full exporttemp].[Case
No] = [Lead details].[Case No]

WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at

the moment and they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view

and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in

it, but none of the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

.

 

[Guest]

I need to add fields to the imported data to monitor salesmen activity, so presumably I can't delete the imported records, but maintain the additional fields - can i? The data should come in the same order every day with new records added at the bottom and exisitng fields updated

This is confusing me no end!!

Pauline

sisnce it is a complete Excel file why not delete the
records in your table and inport all of the new records
from excel.

Jim

-----Original Message-----
Yes the temp fields are all text fomratted and I'm asked

to provide parameters on all 39 fields (now I've corrected
a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion

failure, 14 records due to key violations and 0 records due
to record violations. To ignore click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

.

 

[Guest]

I need to add fields to the imported data to monitor salesman activity. Can I delete the imported data, but maintain the additonal fields nad insert the new daily download?

The daily export should be in the same order each day, with new records added at the bottom and updated fields within the exiting data

I'm really confused!!

sisnce it is a complete Excel file why not delete the
records in your table and inport all of the new records
from excel.

Jim

-----Original Message-----
Yes the temp fields are all text fomratted and I'm asked

to provide parameters on all 39 fields (now I've corrected
a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion

failure, 14 records due to key violations and 0 records due
to record violations. To ignore click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

.

 

[Guest]

I need to add fields to the imported data to monitor salesman activity. Can I delete the imported data, but maintain the additonal fields nad insert the new daily download?

The daily export should be in the same order each day, with new records added at the bottom and updated fields within the exiting data

I'm really confused!!

Pauline

sisnce it is a complete Excel file why not delete the
records in your table and inport all of the new records
from excel.

Jim

-----Original Message-----
Yes the temp fields are all text fomratted and I'm asked

to provide parameters on all 39 fields (now I've corrected
a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion

failure, 14 records due to key violations and 0 records due
to record violations. To ignore click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

.

 

[Ken Snell]

Now that I'm more awake, do you see the same typo in your query that I see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query is
asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to provide

parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14 records

due to key violations and 0 records due to record violations. To ignore
click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>

The name of the temporary file is fullexporttemp and has the following fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types

in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly

the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead

details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full

exporttemp].[Case
No] = [Lead details].[Case No]

WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and

they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed

that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of

the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Guest]

I saw it, corrected it and a couple of other minor orrors and it's working just fine thanks.

My append query is not though - it says 'Cannot insert data with action query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

Now that I'm more awake, do you see the same typo in your query that I see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query is
asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to provide

parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14 records

due to key violations and 0 records due to record violations. To ignore
click yes etc.

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the
same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment and they
are all in my perm 'lead details' table. I changed some of the details and
added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

I've just looked at the update query in datasheet view and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none of the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Ken Snell]

Typo again? You have
Lead details append query
as the target table (the table to get the appended records), but I believe
it should be
Lead details



INSERT INTO [Lead details]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));



--

Ken Snell
<MS ACCESS MVP>

I saw it, corrected it and a couple of other minor orrors and it's working just fine thanks.

My append query is not though - it says 'Cannot insert data with action query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

Now that I'm more awake, do you see the same typo in your query that I see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query is
asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to provide

parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14

records
due to key violations and 0 records due to record violations. To ignore
click yes etc.

:

I do not see anything obviously wrong with your queries. You note

that
the

temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data

types
in

access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has

exactly
the

same fields (I need to add more as I will use this table to extract cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated

Completion]
=

fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead

details].[ASU
fee]

= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment

and
they

are all in my perm 'lead details' table. I changed some of the

details
and

added two new records before importing into the temp

'fullexporttemp'
and

that file looks correct

I've just looked at the update query in datasheet view and noticed that it
has the orginal 14 records before they were changed, but not the two new
ones.

The append query file has only the two new records in it, but none

of
the

original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Ken Snell]

First error that you get: "now I'm getting 'you are about to append two
records (which is correct), but then is says 0 records have been appended
due to key violations - have searched in all sorts of places to understand
what this means, but cannot figure it out."

This error means that you're trying to add a record that has the same
primary key value as a record that already exists in the target table. Based
on your joining field, I assume that Case No is the primary key field in the
target table, and if so, then I am puzzled that you would get this error --
unless Case No is Null in the records that you're trying to append? Provide
more info about the fields and the primary key setup.....

Oh wait.... see the last field in your list of fields just before the FROM
clause:
[Lead details].[Case No]

You're trying to use the Case No of the joined record (which will be Null)
as the value of Case No that is to be put into the target table -- and this
accounts for the key violations. I believe this should be
[full exporttemp].[Case No]


Second error: "because it kept saying 'Microsoft access can't match the
fields you added using the asterix in the append query' and changing the
name of the append to field back to lead details append query*next time I
went in"

I wasn't able to test your actual SQL statement, but I wasn't sure that you
would be successful using the asterisk. As you've correctly noted in your
SQL that lists all the fields in the target table, you must list the fields,
not an *.


--

Ken Snell
<MS ACCESS MVP>

So near but so far - corrected the typo and also appended field by field so I have this
INSERT INTO [Lead details] ( Client_Name, [Date Lead Received], [Date of

Birth], Email, Postcode, [Loan Amount Required], Valuation, Term, Telephone,
[Best Time], [Time of Day], [Supporting Information], Status, [Client
Contacted], [Appointment Date], [Follow Up Date], [Anticipated Completion],
[Advise On], Comments, [Appointment Booked], [Mortgage fee], [Mortgage
value], [Mortgage date], [LC fee], [LC value], [LC date], [ASU fee], [ASU
value], [ASU date], [BC fee], [BC value], [BC date], [Broker fee], [Broker
value], [Broker date], [other fee], [other value], [other date], [Case No] )

SELECT [full exporttemp].Client_Name, [full exporttemp].[Date Lead

Received], [full exporttemp].[Date of Birth], [full exporttemp].Email, [full
exporttemp].Postcode, [full exporttemp].[Loan Amount Required], [full
exporttemp].Valuation, [full exporttemp].Term, [full exporttemp].Telephone,
[full exporttemp].[Best Time], [full exporttemp].[Time of Day], [full
exporttemp].[Supporting Information], [full exporttemp].Status, [full
exporttemp].[Client Contacted], [full exporttemp].[Appointment Date], [full
exporttemp].[Follow Up Date], [full exporttemp].[Anticipated Completion],
[full exporttemp].[Advise On], [full exporttemp].Comments, [full
exporttemp].[Appointment Booked], [full exporttemp].[Mortgage fee], [full
exporttemp].[Mortgage value], [full exporttemp].[Mortgage date], [full
exporttemp].[LC fee], [full exporttemp].[LC value], [full exporttemp].[LC
date], [full exporttemp].[ASU fee], [full exporttemp].[ASU value], [full
exporttemp].[ASU date], [full exporttemp].[BC fee], [full exporttemp].[BC
value], [full exporttemp].[BC date], [full exporttemp].[Broker fee], [full
exporttemp].[Broker value], [full exporttemp].[Broker date], [full
exporttemp].[other fee], [full exporttemp].[other value], [full
exporttemp].[other date], [Lead details].[Case No]

FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));

now I'm getting 'you are about to append two records (which is correct),

but then is says 0 records have been appended due to key violations - have
searched in all sorts of places to understand what this means, but cannot
figure it out.

I couldn't leave as it was, because it kept saying 'Microsoft access can't

match the fields you added using the asterix in the append query' and
changing the name of the append to field back to lead details append
query*next time I went in

I then tried a different approach and copy your code across and the error

I get when I switch to design view is 'Syntax error in join operation'

Sorry I'm such a nightmare - I'm sure once I fix it all will become clear as to what the problem is

Typo again? You have
Lead details append query
as the target table (the table to get the appended records), but I believe
it should be
Lead details



INSERT INTO [Lead details]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));



--

Ken Snell
<MS ACCESS MVP>

I saw it, corrected it and a couple of other minor orrors and it's

working
just fine thanks.

My append query is not though - it says 'Cannot insert data with

action
query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

:

Now that I'm more awake, do you see the same typo in your query that

I
see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query is
asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to provide
parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I get the
following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14 records
due to key violations and 0 records due to record violations. To ignore
click yes etc.


:

I do not see anything obviously wrong with your queries. You

note
that

the
temporary table's fields all are text formatted. Are the permanent
table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the
update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the

data
types

in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly
the
same fields (I need to add more as I will use this table to extract
cases
for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead
details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of

Birth]
=

fullexporttemp.[Date of Birth], [Lead details].Email =
fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone =
fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting
Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead
details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up
Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion]
=
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] =
fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU
fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] =
fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker

fee],
[Lead

details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead
details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the

moment
and

they
are all in my perm 'lead details' table. I changed some of the details
and
added two new records before importing into the temp 'fullexporttemp'
and
that file looks correct

I've just looked at the update query in datasheet view and noticed
that it
has the orginal 14 records before they were changed, but not the

two
new

ones.

The append query file has only the two new records in it, but

none
of

the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Ken Snell]

What you've learned is that append queries will discard any records that
would create duplicate primary key values in the target table. That is why
what you now have is working. However, I am puzzled by why the query
wouldn't work with the Is Null criterion, but it's working as you now have
it set up so I won't worry about it.

Good luck.

--

Ken Snell
<MS ACCESS MVP>

Ken

It's working although I don't really understand why!! Case number is the

primary key in the lead details (perm) table. I don't have one set in the
full exporttemp table (although I've tried with and without)

I updated the last part of the code as suggested, but I was then getting

'Cannot insert data with action query' so I got bold and deleted the 'is
null'. and it worked?!

It says you are about to update 16 records and after I ok it says 14

records were not updated due to key violations (which would be right as 14
of the case numbers do exist) , but my two new records appear correctly.
Assuming this error is a standard warning you get when running this type of
query - I'm there. I don't quite understand what the 'is null' criteria was
doing despite reading loads about it.

I really am a dummy at this, but will learn if it's the last thing I do -

this is first stage of this project. I've got to analyze the changing data
and report on it, that should be fun!!

First error that you get: "now I'm getting 'you are about to append two
records (which is correct), but then is says 0 records have been appended
due to key violations - have searched in all sorts of places to understand
what this means, but cannot figure it out."

This error means that you're trying to add a record that has the same
primary key value as a record that already exists in the target table. Based
on your joining field, I assume that Case No is the primary key field in the
target table, and if so, then I am puzzled that you would get this error --
unless Case No is Null in the records that you're trying to append? Provide
more info about the fields and the primary key setup.....

Oh wait.... see the last field in your list of fields just before the FROM
clause:
[Lead details].[Case No]

You're trying to use the Case No of the joined record (which will be Null)
as the value of Case No that is to be put into the target table -- and this
accounts for the key violations. I believe this should be
[full exporttemp].[Case No]


Second error: "because it kept saying 'Microsoft access can't match the
fields you added using the asterix in the append query' and changing the
name of the append to field back to lead details append query*next time I
went in"

I wasn't able to test your actual SQL statement, but I wasn't sure that you
would be successful using the asterisk. As you've correctly noted in your
SQL that lists all the fields in the target table, you must list the fields,
not an *.


--

Ken Snell
<MS ACCESS MVP>

So near but so far - corrected the typo and also appended field by

field
so I have this

INSERT INTO [Lead details] ( Client_Name, [Date Lead Received], [Date

of
Birth], Email, Postcode, [Loan Amount Required], Valuation, Term, Telephone,
[Best Time], [Time of Day], [Supporting Information], Status, [Client
Contacted], [Appointment Date], [Follow Up Date], [Anticipated Completion],
[Advise On], Comments, [Appointment Booked], [Mortgage fee], [Mortgage
value], [Mortgage date], [LC fee], [LC value], [LC date], [ASU fee], [ASU
value], [ASU date], [BC fee], [BC value], [BC date], [Broker fee], [Broker
value], [Broker date], [other fee], [other value], [other date], [Case No] )

SELECT [full exporttemp].Client_Name, [full exporttemp].[Date Lead

Received], [full exporttemp].[Date of Birth], [full exporttemp].Email, [full
exporttemp].Postcode, [full exporttemp].[Loan Amount Required], [full
exporttemp].Valuation, [full exporttemp].Term, [full exporttemp].Telephone,
[full exporttemp].[Best Time], [full exporttemp].[Time of Day], [full
exporttemp].[Supporting Information], [full exporttemp].Status, [full
exporttemp].[Client Contacted], [full exporttemp].[Appointment Date], [full
exporttemp].[Follow Up Date], [full exporttemp].[Anticipated Completion],
[full exporttemp].[Advise On], [full exporttemp].Comments, [full
exporttemp].[Appointment Booked], [full exporttemp].[Mortgage fee], [full
exporttemp].[Mortgage value], [full exporttemp].[Mortgage date], [full
exporttemp].[LC fee], [full exporttemp].[LC value], [full exporttemp].[LC
date], [full exporttemp].[ASU fee], [full exporttemp].[ASU value], [full
exporttemp].[ASU date], [full exporttemp].[BC fee], [full exporttemp].[BC
value], [full exporttemp].[BC date], [full exporttemp].[Broker fee], [full
exporttemp].[Broker value], [full exporttemp].[Broker date], [full
exporttemp].[other fee], [full exporttemp].[other value], [full
exporttemp].[other date], [Lead details].[Case No]

FROM [full exporttemp] LEFT JOIN [Lead details] ON [full

exporttemp].[Case
No] = [Lead details].[Case No]

WHERE ((([Lead details].[Case No]) Is Null));

now I'm getting 'you are about to append two records (which is

correct),
but then is says 0 records have been appended due to key violations - have
searched in all sorts of places to understand what this means, but cannot
figure it out.

I couldn't leave as it was, because it kept saying 'Microsoft access

can't
match the fields you added using the asterix in the append query' and
changing the name of the append to field back to lead details append
query*next time I went in

I then tried a different approach and copy your code across and the

error
I get when I switch to design view is 'Syntax error in join operation'

Sorry I'm such a nightmare - I'm sure once I fix it all will become

clear
as to what the problem is

:

Typo again? You have
Lead details append query
as the target table (the table to get the appended records), but I believe
it should be
Lead details



INSERT INTO [Lead details]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));



--

Ken Snell
<MS ACCESS MVP>

I saw it, corrected it and a couple of other minor orrors and it's working
just fine thanks.

My append query is not though - it says 'Cannot insert data with action
query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

:

Now that I'm more awake, do you see the same typo in your query

that
I

see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the

query
is

asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to provide
parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I

get
the

following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14
records
due to key violations and 0 records due to record violations.

To
ignore

click yes etc.


:

I do not see anything obviously wrong with your queries. You note
that
the
temporary table's fields all are text formatted. Are the permanent
table's
fields also text formatted?

Which specific parameters are you asked to provide when you

run
the

update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the
following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data
types
in
access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has
exactly
the
same fields (I need to add more as I will use this table to extract
cases
for QC purposes and record the results, but I wanted to get the
import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead
details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead

Received]
=

fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth]
=
fullexporttemp.[Date of Birth], [Lead details].Email =
fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead
details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone =
fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting
Information],
[Lead details].Status = fullexporttemp.Status, [Lead
details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead
details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up
Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated
Completion]
=
fullexporttemp.[Anticipated Completion], [Lead

details].Comments
=

fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead

details].[Mortgage
fee] =

fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage

date]
=

fullexporttemp.[Mortgage date], [Lead details].[LC fee] =
fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details],
[Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead
details].[ASU
fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] =
fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU

date],
[Lead

details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC
value] =
fullexporttemp.[BC value], [Lead details].[BC date] =
fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee],
[Lead
details].[Broker value] = "f[fullexporttemp].[Broker

value]",
[Lead

details].[Broker date] = fullexporttemp.[Broker date], [Lead
details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other

value]
=

fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the moment
and
they
are all in my perm 'lead details' table. I changed some of the
details
and
added two new records before importing into the temp
'fullexporttemp'
and
that file looks correct

I've just looked at the update query in datasheet view and noticed
that it
has the orginal 14 records before they were changed, but not

the
two

new
ones.

The append query file has only the two new records in it,

but
none

of
the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

 

[Guest]

Thank you very much for your help - I'm now working with the real download and it's working like a dream so I've moved onto the next stage. Whach out for more posts from 'confused from England!'

Pauline

What you've learned is that append queries will discard any records that
would create duplicate primary key values in the target table. That is why
what you now have is working. However, I am puzzled by why the query
wouldn't work with the Is Null criterion, but it's working as you now have
it set up so I won't worry about it.

Good luck.

--

Ken Snell
<MS ACCESS MVP>

Ken

It's working although I don't really understand why!! Case number is the

primary key in the lead details (perm) table. I don't have one set in the
full exporttemp table (although I've tried with and without)

I updated the last part of the code as suggested, but I was then getting

'Cannot insert data with action query' so I got bold and deleted the 'is
null'. and it worked?!

It says you are about to update 16 records and after I ok it says 14

records were not updated due to key violations (which would be right as 14
of the case numbers do exist) , but my two new records appear correctly.
Assuming this error is a standard warning you get when running this type of
query - I'm there. I don't quite understand what the 'is null' criteria was
doing despite reading loads about it.

I really am a dummy at this, but will learn if it's the last thing I do -

this is first stage of this project. I've got to analyze the changing data
and report on it, that should be fun!!

First error that you get: "now I'm getting 'you are about to append two
records (which is correct), but then is says 0 records have been appended
due to key violations - have searched in all sorts of places to understand
what this means, but cannot figure it out."

This error means that you're trying to add a record that has the same
primary key value as a record that already exists in the target table. Based
on your joining field, I assume that Case No is the primary key field in the
target table, and if so, then I am puzzled that you would get this error --
unless Case No is Null in the records that you're trying to append? Provide
more info about the fields and the primary key setup.....

Oh wait.... see the last field in your list of fields just before the FROM
clause:
[Lead details].[Case No]

You're trying to use the Case No of the joined record (which will be Null)
as the value of Case No that is to be put into the target table -- and this
accounts for the key violations. I believe this should be
[full exporttemp].[Case No]


Second error: "because it kept saying 'Microsoft access can't match the
fields you added using the asterix in the append query' and changing the
name of the append to field back to lead details append query*next time I
went in"

I wasn't able to test your actual SQL statement, but I wasn't sure that you
would be successful using the asterisk. As you've correctly noted in your
SQL that lists all the fields in the target table, you must list the fields,
not an *.


--

Ken Snell
<MS ACCESS MVP>


So near but so far - corrected the typo and also appended field by field
so I have this
INSERT INTO [Lead details] ( Client_Name, [Date Lead Received], [Date of
Birth], Email, Postcode, [Loan Amount Required], Valuation, Term, Telephone,
[Best Time], [Time of Day], [Supporting Information], Status, [Client
Contacted], [Appointment Date], [Follow Up Date], [Anticipated Completion],
[Advise On], Comments, [Appointment Booked], [Mortgage fee], [Mortgage
value], [Mortgage date], [LC fee], [LC value], [LC date], [ASU fee], [ASU
value], [ASU date], [BC fee], [BC value], [BC date], [Broker fee], [Broker
value], [Broker date], [other fee], [other value], [other date], [Case No] )
SELECT [full exporttemp].Client_Name, [full exporttemp].[Date Lead
Received], [full exporttemp].[Date of Birth], [full exporttemp].Email, [full
exporttemp].Postcode, [full exporttemp].[Loan Amount Required], [full
exporttemp].Valuation, [full exporttemp].Term, [full exporttemp].Telephone,
[full exporttemp].[Best Time], [full exporttemp].[Time of Day], [full
exporttemp].[Supporting Information], [full exporttemp].Status, [full
exporttemp].[Client Contacted], [full exporttemp].[Appointment Date], [full
exporttemp].[Follow Up Date], [full exporttemp].[Anticipated Completion],
[full exporttemp].[Advise On], [full exporttemp].Comments, [full
exporttemp].[Appointment Booked], [full exporttemp].[Mortgage fee], [full
exporttemp].[Mortgage value], [full exporttemp].[Mortgage date], [full
exporttemp].[LC fee], [full exporttemp].[LC value], [full exporttemp].[LC
date], [full exporttemp].[ASU fee], [full exporttemp].[ASU value], [full
exporttemp].[ASU date], [full exporttemp].[BC fee], [full exporttemp].[BC
value], [full exporttemp].[BC date], [full exporttemp].[Broker fee], [full
exporttemp].[Broker value], [full exporttemp].[Broker date], [full
exporttemp].[other fee], [full exporttemp].[other value], [full
exporttemp].[other date], [Lead details].[Case No]
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));

now I'm getting 'you are about to append two records (which is correct),
but then is says 0 records have been appended due to key violations - have
searched in all sorts of places to understand what this means, but cannot
figure it out.

I couldn't leave as it was, because it kept saying 'Microsoft access can't
match the fields you added using the asterix in the append query' and
changing the name of the append to field back to lead details append
query*next time I went in

I then tried a different approach and copy your code across and the error
I get when I switch to design view is 'Syntax error in join operation'

Sorry I'm such a nightmare - I'm sure once I fix it all will become clear
as to what the problem is


:

Typo again? You have
Lead details append query
as the target table (the table to get the appended records), but I
believe
it should be
Lead details



INSERT INTO [Lead details]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));



--

Ken Snell
<MS ACCESS MVP>

I saw it, corrected it and a couple of other minor orrors and it's
working
just fine thanks.

My append query is not though - it says 'Cannot insert data with
action
query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

:

Now that I'm more awake, do you see the same typo in your query that
I
see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query
is
asking for the parameter values for each field.

--

Ken Snell
<MS ACCESS MVP>

Yes the temp fields are all text fomratted and I'm asked to
provide
parameters on all 39 fields (now I've corrected a couple of errors I
spotted)

If I ok the parameter request without entering any data. I get
the
following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14
records
due to key violations and 0 records due to record violations. To
ignore
click yes etc.


:

I do not see anything obviously wrong with your queries. You
note
that
the
temporary table's fields all are text formatted. Are the
permanent
table's
fields also text formatted?

Which specific parameters are you asked to provide when you run
the
update
query?

--

Ken Snell
<MS ACCESS MVP>


The name of the temporary file is fullexporttemp and has the
following
fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the
data
types
in
access are all set to text otherwise I couldn't import the
table.

The permanent table id called 'lead details' and currently has
exactly
the
same fields (I need to add more as I will use this table to
extract
cases
for QC purposes and record the results, but I wanted to get the
import/
update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead
details].[Case
No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =
fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received]
=
fullexporttemp.[Date Lead Received], [Lead details].[Date of
Birth]
=
fullexporttemp.[Date of Birth], [Lead details].Email =
fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead
details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead
details].Term =
fullexporttemp.Term, [Lead details].Telephone =
fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting
Information],
[Lead details].Status = fullexporttemp.Status, [Lead
details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead
details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead
details].[Follow Up
Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated
Completion]
=
fullexporttemp.[Anticipated Completion], [Lead details].Comments
=
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage
fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date]
=
fullexporttemp.[Mortgage date], [Lead details].[LC fee] =
fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details],
[Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead
details].[ASU
fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] =
fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date],
[Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC
value] =
fullexporttemp.[BC value], [Lead details].[BC date] =
fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker
fee],
[Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]",
[Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead
details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value]
=
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case
No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));


There are only 14 records in my excel spreadsheet at the
moment
and
they
are all in my perm 'lead details' table. I changed some of the
details
and
added two new records before importing into the temp
'fullexporttemp'
and
that file looks correct

I've just looked at the update query in datasheet view and
noticed
that it
has the orginal 14 records before they were changed, but not the
two
new
ones.

The append query file has only the two new records in it, but
none
of
the
original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline