INSERT Query woes...

[ArielZusya]

I thought I had solved this problem earlier but apperently not. I'm getting
the following error:

The changes you requested to the table were not successful because they
would create duplicate value in the index, primary key, or relationship.
Change the data in the fields that contain duplicate data, remove the index,
or redefine the index to permit duplicate entries and try again.

Here's the INSERT Query I'm using:

INSERT INTO tblRefNum ( RefNum, BatchNum )
SELECT DISTINCT RefNum, {lngBatch}
FROM tblMain;

{lngBatch} is a number I generate using VBA which looks at tblRefNum for the
highest BatchNum in the table and adds 1 to it so if I've never added a
record to tblRefNum lngBatch would be 1, and if this is the second time I've
added a record to tblRefNum lngBatch would be 2. I use the insert query
every time I import an excel spreadsheet into tblMain. The excel spreadsheet
has FirstName, LastName, and RefNum. Every FirstName and LastName in a given
spreadsheet being imported has the same RefNum as the others in that
spreadsheet. That's why the insert query looks for Distinct values in
tblMain. In other words the spreadsheet looks like this:

FirstName LastName RefNum
Joe Smith 54CBF6267
Jane Doe 54CBF6267
etc.

It then inserts that case name into tblRefNum with the BatchNum as described
above. I'm worried that this query does nothing to determine if the unique
values found in tblMain are already in tblRefNum. If this is the first time
I've run the query, the query would only find one RefNum so it wouldn't have
any problems. But, if this is the second or third or later time, it would
find more RefNums. What do I do? In case it's helpful, here's my table
structures:

tblMain.ID_Main, AutoNumber, PrimaryKey
tblMain.FirstName, Text
tblMain.LastName, Text
tblMain.RefNum, Text

tblRefNum.RefNum, Text, PrimaryKey
tblRefNum.BatchNum, Number, PrimaryKey
tblRefNum.Description, Text

There is a one-to-many relationship between tblRefNum.RefNum and
tblMain.RefNum. Thanks for your help!

 

[Baz]

What's the deal with this {lngBatch}? Aren't braces supposed to signify a
GUID? And, AFAIK, there is no way to refer to a VBA variable from within
the SQL.

You maybe want something like this (warning: untried):

INSERT INTO tblRefNum ( RefNum, BatchNum )
SELECT DISTINCT
tblMain.RefNum,
(SELECT Max(BatchNum) + 1 FROM tblRefNum) AS BatchNum
FROM
tblMain LEFT JOIN
tblRefNum ON tblMain.RefNum = tblRefNum.RefNum
WHERE
tblRefNum.RefNum IS NULL

 

[ArielZusya]

Thanks for your note. As for the lngBatch... I was just using the { } to
denote that it wasn't actually part of the sql statment but was a placeholder
used to attempt to make my question a bit less complicated. Guess that
didn't work out. The way I've inserted a string into the sql statement is in
VBA just concatonating the string together and then calling the string. As I
explained below, it's a number generated by the code and is used as a test
statement elsewhere in the code so it isn't something I can generate in the
sql. In case you're interested, the code, called by an onclick from a
button, looked like this:

Dim lngBatch As Long
Dim strSql As String
Dim stLinkCriteria As String

Const stFormName = "frmDescriber"
Const stTableName = "tblMain"

DoCmd.Close

DoCmd.TransferSpreadsheet acImport, , stTableName, _
OpenTextFile, True

lngBatch = Nz(DMax("BatchNum", "tblRefNum"), 0&) + 1&

strSql = "INSERT INTO tblRefNum ( RefNum, BatchNum ) " & vbCrLf _
& "SELECT DISTINCT RefNum, " & lngBatch & vbCrLf & "FROM tblMain;"

DBEngine(0)(0).Execute strSql, dbFailOnError

stLinkCriteria = "BatchNum = " & lngBatch
DoCmd.OpenForm stFormName, , , stLinkCriteria

With your changes my code now looks like this:

strSql = "INSERT INTO tblRefNum ( RefNum, BatchNum ) " & vbCrLf _
& "SELECT DISTINCT tblMain.RefNum, " & lngBatch & vbCrLf & "FROM _
tblMain LEFT JOIN" & vbCrLf & "tblRefNum ON tblMain.RefNum _
= tblRefNum.RefNum WHERE tblRefNum.RefNum IS NULL;"

For whatever reason, this didn't solve the problem. Any other thoughts?
Thanks!

 

[Baz]

OK, I understand now about the lngbatch thing.

The code as you posted it will not even compile. Try this:

strSql = "INSERT INTO tblRefNum ( RefNum, BatchNum ) " & _
"SELECT DISTINCT tblMain.RefNum, " & lngBatch & " FROM " & _
"tblMain LEFT JOIN tblRefNum ON tblMain.RefNum " & _
"= tblRefNum.RefNum WHERE tblRefNum.RefNum IS NULL;"

Note that all those vbCRLF constants are completely unnecessary.

 

[ArielZusya]

I'm not sure what the code problem you're having is but it compiled just fine
for me and works just fine but only for inserting the first record into
tblRefNum. Inserting any records after that is where I get that error. as
for the vbCrLfs, I just put those in there so I could output the SQL
statement to a MsgBox and have it fit nicely so I could see it just before it
ran so I could visually verify it was what I expected it to be... they didn't
really serve any other purpose.

So... about your SQL statement... I don't think I understand the reason
behind the IS NULL. To test it I ran the following SQL:

SELECT DISTINCT tblMain.RefNum, 2
FROM tblRefNum
LEFT JOIN tblMain ON tblRefNum.RefNum = tblMain.RefNum
WHERE tblRefNum.RefNum IS NULL;

and it returned no records even though there are records in tblMain that
have a RefNum not yet in tblRefNum. I then tried the following SQL:

SELECT DISTINCT tblMain.RefNum, 2
FROM tblRefNum
LEFT JOIN tblMain ON tblRefNum.RefNum <> tblMain.RefNum;

and it returned the one record in tblMain that has a RefNum that isn't yet
in tblRefNum which is great. Half way there! That output looked like this:

RefNum Expr1001
12CBF2243 2

Trouble is when I add the INSERT statement it gave me errors and wouldn't
insert the one record I was able to grab above. That insert statement looked
like this:

INSERT INTO tblRefNum (RefNum, BatchNum)
SELECT DISTINCT tblMain.RefNum, 2
FROM tblRefNum
LEFT JOIN tblMain ON tblRefNum.RefNum <> tblMain.RefNum;

and the error was:

Database set 0 field(s) to Null due to a type conversion failure, and it
didn't add 1 record(s) to the table due to key vilations, 0 record(s) due to
lock vilations, and 0 record(s) due to validation rule violations. Do you
want to run the action query anyway? To ignore the error(s) and run the
query click Yes. For an explanation of the causes of the violations, click
Help.

so... looks like this is a key problem. What am I doing wrong? What do I
need to change? Thanks for your help.

 

[Baz]

1. Your code could not compile because you cannot put a line continuation in
the middle of a string literal. If it compiled for you, then what you
compiled is not the same as what you posted.

2. You've got the tables the wrong way round. What I posted was this:

FROM tblMain LEFT JOIN tblRefNum

But what you are doing is this:

FROM tblRefNum LEFT JOIN tblMain

You may be accustomed to the sequence of the tables being irrelevant in an
inner join, but in an outer join such as this it is *critical* to get the
tables the right way round.

 

[ArielZusya]

So... I tried your sql statement and the select part works but the insert is
still a problem with the error I described below. Thoughts?

 

[Baz]

Presumably you are happy, from examining the results of the SELECT query,
that it is only returning values for RefNum which are *not* already in
tblRefNum?

If so, is there a relationship to some other table which is causing the key
violation? For example, is tblRefNum.BatchNum related to some other table?

 

[ArielZusya]

Looks like the problem was ultimately a corrupt table. I rebuilt the table
tblRefNum and suddenly everything worked. Thanks for the help.

 

[Baz]

Fantastic, glad you got there...

Looks like the problem was ultimately a corrupt table. I rebuilt the
table
tblRefNum and suddenly everything worked. Thanks for the help.