Solved Append Query Key Violation

[JED25]

I'm fairly new to Access and I'm trying to build a client admission DB for my company by condensing an excessive amount of spreadsheets. I have a table where I'm compiling all the data, a linked table to an excel sheet where I manipulate past data, and created an append query. Every time I run the append query I get a key violation error for some of the data. My most recent attempt I tried to add 284 records, but only 84 appended correctly. Why won't it take all my data? I've already check that records are formatted correctly. None of my fields are indexed. The data shows up perfectly when I go to datasheet view. I'm completely at a loss.

Here is the SQL for the append query:

INSERT INTO [INTAKE LOG] ( ADMISSION, [CLT ID], INSURANCE, [LGL-ST], [REF BY], LOC, DISCHARGE, [CLT TYPE] )
SELECT [INTAKE LINK].ADMISSION, [INTAKE LINK].[CLT ID], [INTAKE LINK].INS, [INTAKE LINK].[LGL-ST], [INTAKE LINK].[REF BY], [INTAKE LINK].Loc, [INTAKE LINK].DISCHARGE, [INTAKE LINK].[CLT TYPE]
FROM [INTAKE LINK] LEFT JOIN [INTAKE LOG] ON [INTAKE LINK].ADMISSION = [INTAKE LOG].ADMISSION
WHERE ((([INTAKE LOG].ADMISSION) Is Null));

 

[theDBguy]

Hi. Welcome to AWF!

Key violations usually refer to index requirements. It would be hard to tell you what's wrong, if we can't see the actual database file with the problematic data included.

 

[Pat Hartman]

A key violation is pretty specific.

 

[Eugene-LS]

Here is the SQL for the append query:

Can you post a sample DB with test data and excel sheet?

 

[JED25]

I can't really send the DB due to HIPPA violations (I work in a hospital setting). However, I have looked up the indexes and there is nothing indexed.

 

[Eugene-LS]

HIPPA violations

You are not the only one who has this kind of thing
Just replace sensitive data with meaningless text by using update queries.

 

[Gasman]

You are not the only one who has this kind of thing
Just replace sensitive data with meaningless text by using update queries.

On a copy of your db as well.
I seem to recall @moke123 created some functions to obfuscate the data.
See the link in my signature. ( which is not visible on phone?)

 

[Pat Hartman]

Start by posting the tables without data

 

[moke123]

On a copy of your db as well.
I seem to recall @moke123 created some functions to obfuscate the data.
See the link in my signature. ( which is not visible on phone?)

here it is

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

 

[Gasman]

here it is

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

Yes, I have a link in my signature. Just it's not visible on my phone.

 

[JED25]

Ok I actually created a copy of my database and removed a bunch of thing and consolidated the information.

 

[Eugene-LS]

Ok I actually created a copy of my database and removed a bunch of thing and consolidated the information.

И где Excel файл - А! ???
Вы думаете - или нет? ...
...
EN:
And where is the Excel file - Ah! ???
Do you think - or not? ...

 

[theDBguy]

See the link in my signature. ( which is not visible on phone?)

Yes, I have a link in my signature. Just it's not visible on my phone.

@Gasman Try turning your phone sideways...

 

[JED25]

Ok I actually created a copy of my database and removed a bunch of thing and consolidated the information.

И где Excel файл - А! ???
Вы думаете - или нет? ...
...
EN:
And where is the Excel file - Ah! ???
Do you think - or not? ...

 

[Eugene-LS]

That's better!
Looking at the material ...

 

[Gasman]

@Gasman Try turning your phone sideways...

Aha, and there it is
Thanks @theDBguy

 

[Eugene-LS]

@JED25 - query works:

INSERT INTO [INTAKE LOG] ( ADMISSION, [CLT ID], INSURANCE, [REF BY], LOC, DISCHARGE )
SELECT [INTAKE LINK].ADMISSION, [INTAKE LINK].[CLT ID], [INTAKE LINK].INS,
    [INTAKE LINK].[REF BY], [INTAKE LINK].Loc, [INTAKE LINK].DISCHARGE
FROM [INTAKE LINK]
    LEFT JOIN [INTAKE LOG]
        ON [INTAKE LINK].ADMISSION = [INTAKE LOG].ADMISSION
WHERE ([INTAKE LOG].ADMISSION Is Null);

 

[The_Doc_Man]

There are only a couple of things that would stop the updates. One of them is indexing, but you say no fields are indexed. The other is that for some reason, there is a constraint on the fields. If you have a linked table based on the Excel file, there should still be a table definition for the external file and field definitions should still be associated with the linked table. Check for field-level constraints such as "required" (="YES") or "ZLS allowed" (="NO").

I am somewhat confused, though. Which version of Office are you using? I ask because some versions of Office do not support updating linked Excel tables. For those versions, Excel tables are read-only. If it does updates at all, obviously it is not one of those versions - but then, which version is it? Knowing that MIGHT make a difference.

 

[JED25]

There are only a couple of things that would stop the updates. One of them is indexing, but you say no fields are indexed. The other is that for some reason, there is a constraint on the fields. If you have a linked table based on the Excel file, there should still be a table definition for the external file and field definitions should still be associated with the linked table. Check for field-level constraints such as "required" (="YES") or "ZLS allowed" (="NO").

I am somewhat confused, though. Which version of Office are you using? I ask because some versions of Office do not support updating linked Excel tables. For those versions, Excel tables are read-only. If it does updates at all, obviously it is not one of those versions - but then, which version is it? Knowing that MIGHT make a difference.

I have 2016

@JED25 - query works:

INSERT INTO [INTAKE LOG] ( ADMISSION, [CLT ID], INSURANCE, [REF BY], LOC, DISCHARGE )
SELECT [INTAKE LINK].ADMISSION, [INTAKE LINK].[CLT ID], [INTAKE LINK].INS,
    [INTAKE LINK].[REF BY], [INTAKE LINK].Loc, [INTAKE LINK].DISCHARGE
FROM [INTAKE LINK]
    LEFT JOIN [INTAKE LOG]
        ON [INTAKE LINK].ADMISSION = [INTAKE LOG].ADMISSION
WHERE ([INTAKE LOG].ADMISSION Is Null);

Ok this really doesn't make sense because it does work on the copy, but not the original. So something I changed must be causing the problem.

 

[theDBguy]

Here's your original query from your first post.

Here is the SQL for the append query:

INSERT INTO [INTAKE LOG] ( ADMISSION, [CLT ID], INSURANCE, [LGL-ST], [REF BY], LOC, DISCHARGE, [CLT TYPE] )
SELECT [INTAKE LINK].ADMISSION, [INTAKE LINK].[CLT ID], [INTAKE LINK].INS, [INTAKE LINK].[LGL-ST], [INTAKE LINK].[REF BY], [INTAKE LINK].Loc, [INTAKE LINK].DISCHARGE, [INTAKE LINK].[CLT TYPE]
FROM [INTAKE LINK] LEFT JOIN [INTAKE LOG] ON [INTAKE LINK].ADMISSION = [INTAKE LOG].ADMISSION
WHERE ((([INTAKE LOG].ADMISSION) Is Null));

Here's the working query from @Eugene-LS from post #17.

@JED25 - query works:

INSERT INTO [INTAKE LOG] ( ADMISSION, [CLT ID], INSURANCE, [REF BY], LOC, DISCHARGE )
SELECT [INTAKE LINK].ADMISSION, [INTAKE LINK].[CLT ID], [INTAKE LINK].INS,
    [INTAKE LINK].[REF BY], [INTAKE LINK].Loc, [INTAKE LINK].DISCHARGE
FROM [INTAKE LINK]
    LEFT JOIN [INTAKE LOG]
        ON [INTAKE LINK].ADMISSION = [INTAKE LOG].ADMISSION
WHERE ([INTAKE LOG].ADMISSION Is Null);

They are not exactly the same. As far as I can see, the second/working query is missing two fields from your original query.