Validation Rule Violations

[Guest]

I have a table in which I need to import data from another (smaller) table.
The larger table needs to be populated with this new data, then other data
entered to be sent to a vendor. I keep getting the above subject error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#], TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] = Agent.EESSN;

There are no validation rules, allow zero length is set to yes, there is no
indexed fields. What can I do to make this work? I thought if I hit Yes, it
would populate at least SOME of the data, but I can't get anything to write
over to the larger table.

 

[Guest]

Try coping the table structure and run your append query into the table copy.
Then analyze what did not get appended.

 

[Brendan Reynolds]

Do any of the fields in the target table disallow Null values? (Required =
Yes in design view). Might any of those fields in the source table contain
Null values?

 

[Guest]

I had the orig data in Excel format, so I imported the tables again with
absolutely no formatting & reran the query & it worked fine... all records
wrote... still don't know why the first run did not work.

Try coping the table structure and run your append query into the table copy.
Then analyze what did not get appended.

I have a table in which I need to import data from another (smaller) table.
The larger table needs to be populated with this new data, then other data
entered to be sent to a vendor. I keep getting the above subject error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#], TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] = Agent.EESSN;

There are no validation rules, allow zero length is set to yes, there is no
indexed fields. What can I do to make this work? I thought if I hit Yes, it
would populate at least SOME of the data, but I can't get anything to write
over to the larger table.

 

[Guest]

the source table has some zero values, but no nulls...

Do any of the fields in the target table disallow Null values? (Required =
Yes in design view). Might any of those fields in the source table contain
Null values?

--
Brendan Reynolds
Access MVP

I have a table in which I need to import data from another (smaller) table.
The larger table needs to be populated with this new data, then other data
entered to be sent to a vendor. I keep getting the above subject error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#],
TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] =
Agent.EESSN;

There are no validation rules, allow zero length is set to yes, there is
no
indexed fields. What can I do to make this work? I thought if I hit Yes,
it
would populate at least SOME of the data, but I can't get anything to
write
over to the larger table.

 

[Brendan Reynolds]

The query might still produce Null values, though, because you're using LEFT
JOIN. That will produce Null values if there is no matching record.

On reflection, I'm not sure I understand the intent of this join at all. Is
the intent to select only records from TotalEarnings that don't already
exist in Agent? Or only records that do already exist in Agent? Or all
records regardless of whether a matching record exists in Agent or not?

--
Brendan Reynolds
Access MVP

the source table has some zero values, but no nulls...

Do any of the fields in the target table disallow Null values? (Required
=
Yes in design view). Might any of those fields in the source table
contain
Null values?

--
Brendan Reynolds
Access MVP

I have a table in which I need to import data from another (smaller)
table.
The larger table needs to be populated with this new data, then other
data
entered to be sent to a vendor. I keep getting the above subject
error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#],
TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] =
Agent.EESSN;

There are no validation rules, allow zero length is set to yes, there
is
no
indexed fields. What can I do to make this work? I thought if I hit
Yes,
it
would populate at least SOME of the data, but I can't get anything to
write
over to the larger table.

 

[Guest]

I need ALL records to write over whether there is a match or not. I had to
join them because when I did not, I got NO matches.

The query might still produce Null values, though, because you're using LEFT
JOIN. That will produce Null values if there is no matching record.

On reflection, I'm not sure I understand the intent of this join at all. Is
the intent to select only records from TotalEarnings that don't already
exist in Agent? Or only records that do already exist in Agent? Or all
records regardless of whether a matching record exists in Agent or not?

--
Brendan Reynolds
Access MVP

the source table has some zero values, but no nulls...

Do any of the fields in the target table disallow Null values? (Required
=
Yes in design view). Might any of those fields in the source table
contain
Null values?

--
Brendan Reynolds
Access MVP


I have a table in which I need to import data from another (smaller)
table.
The larger table needs to be populated with this new data, then other
data
entered to be sent to a vendor. I keep getting the above subject
error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#],
TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] =
Agent.EESSN;

There are no validation rules, allow zero length is set to yes, there
is
no
indexed fields. What can I do to make this work? I thought if I hit
Yes,
it
would populate at least SOME of the data, but I can't get anything to
write
over to the larger table.

 

[Brendan Reynolds]

The following query will append all records in TotalEarnings into Agent, no
join is required ...

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#],
TotalEarnings.TotEarn
FROM TotalEarnings

--
Brendan Reynolds
Access MVP

I need ALL records to write over whether there is a match or not. I had to
join them because when I did not, I got NO matches.

The query might still produce Null values, though, because you're using
LEFT
JOIN. That will produce Null values if there is no matching record.

On reflection, I'm not sure I understand the intent of this join at all.
Is
the intent to select only records from TotalEarnings that don't already
exist in Agent? Or only records that do already exist in Agent? Or all
records regardless of whether a matching record exists in Agent or not?

--
Brendan Reynolds
Access MVP

the source table has some zero values, but no nulls...

:

Do any of the fields in the target table disallow Null values?
(Required
=
Yes in design view). Might any of those fields in the source table
contain
Null values?

--
Brendan Reynolds
Access MVP


I have a table in which I need to import data from another (smaller)
table.
The larger table needs to be populated with this new data, then
other
data
entered to be sent to a vendor. I keep getting the above subject
error.
here is the sql for my query:

INSERT INTO Agent ( FULLNAME, EESSN, Earnings )
SELECT TotalEarnings.fullName, TotalEarnings.[SocSec#],
TotalEarnings.TotEarn
FROM TotalEarnings LEFT JOIN Agent ON TotalEarnings.[SocSec#] =
Agent.EESSN;

There are no validation rules, allow zero length is set to yes,
there
is
no
indexed fields. What can I do to make this work? I thought if I
hit
Yes,
it
would populate at least SOME of the data, but I can't get anything
to
write
over to the larger table.

 

[iliace]

Remove the LEFT JOIN statement. You don't need a join for that kind of
query, that's only for selecting from multiple tables. All your data
in the insert values comes from a single table.

 

[Guest]

Thanks for the replies... now I need to add more data to the table that I
need to compile to send to this vendor, I would need to match on the SSN but
keep getting the same error message. I removed all formatting, made certain
there were no "required" fields & still no go. Here is my sql:

INSERT INTO Agents ( Address1, Address2, City, State, Zip, DOH, EEGender,
EEDOB, EELastName, EEFirstName, EEMI )
SELECT tblinfo.Address1, tblinfo.Address2, tblinfo.city, tblinfo.st,
tblinfo.zip, tblinfo.ContractDate, tblinfo.gender, tblinfo.DOB,
tblinfo.Lname, tblinfo.Fname, tblinfo.MI
FROM tblinfo LEFT JOIN Agents ON tblinfo.ssn = Agents.EESSN;

Any assistance would be greatly appreciated.

 

[iliace]

You are *not* inserting from (tblInfo LEFT JOIN Agents). You are
inserting from tblinfo - that's it. Note that not a single one of your
parameters begins with Agents. as table name.

You might be using the wrong SQL command. Perhaps an UPDATE query is
what you are looking for? Even then, you would still not have a LEFT
JOIN. Remove that part.

Or, you do in fact want to create a new record, in which case I must
ask: what are your primary keys? If Agents.EESSN is one, then the LEFT
JOIN part makes even less sense. You would not have any EESSN that
match the Agents tables (where you're inserting into), otherwise you
will get a key violation each time you run that query. Besides, and
you should be inserting the primary key field as well, not joining upon
match.

Finally, LEFT JOIN seems like the wrong type of join even if its usage
were justified. INNER JOIN would be much more logical.

I don't think it's clear what it is that you're trying to accomplish.
Sometimes it helps to do a quick algebra sum on paper to make sure your
SQL operations make sense.