Duplicate Records from Append Query

G

Guest

I understand that a strong where clause in an append query can eliminate
duplicate records. I can't seem to find a clause that will work. I've tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
 
G

Guest

If you flip the icon to make it a select query does it pull duplicate records
or is it pulling a record that already exists in [Chart Pull - Requests]?
You do not have this or any other constraints on the query that would stop
you from pulling the same record twice.

Repost your SQL and show how you used the tblMessage.ID not in tblChart
Pull-Request.Message ID

Melynda E. said:
I understand that a strong where clause in an append query can eliminate
duplicate records. I can't seem to find a clause that will work. I've tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
 
G

Guest

This query is adding records to a table that already exist. Message.ID is
the key for it's table, there should be no duplicates of this ID.

KARL DEWEY said:
If you flip the icon to make it a select query does it pull duplicate records
or is it pulling a record that already exists in [Chart Pull - Requests]?
You do not have this or any other constraints on the query that would stop
you from pulling the same record twice.

Repost your SQL and show how you used the tblMessage.ID not in tblChart
Pull-Request.Message ID

Melynda E. said:
I understand that a strong where clause in an append query can eliminate
duplicate records. I can't seem to find a clause that will work. I've tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
 
G

Guest

Simple solution, remove any duplicates, open the table in design view, make
Message.ID the primary key by clicking in the field name and then clicking
the icon that looks like a key.

Melynda E. said:
This query is adding records to a table that already exist. Message.ID is
the key for it's table, there should be no duplicates of this ID.

KARL DEWEY said:
If you flip the icon to make it a select query does it pull duplicate records
or is it pulling a record that already exists in [Chart Pull - Requests]?
I've tried tblMessage.ID not in tblChart Pull-Request.Message ID.
Click to expand...
You do not have this or any other constraints on the query that would stop
you from pulling the same record twice.

Repost your SQL and show how you used the tblMessage.ID not in tblChart
Pull-Request.Message ID

Melynda E. said:
I understand that a strong where clause in an append query can eliminate
duplicate records. I can't seem to find a clause that will work. I've tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Is Messages.ID the primary key in the Messages table?

If so they try the following query which should eliminate any message.ID records
that have a match in records Chart Pull - Requests Based on Message Id


INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
 
G

Guest

Messages.ID is the primary key in the Messages table. However, when I try
this new query, I receive an error (Type mismatch in expression). Any advice
Is Messages.ID the primary key in the Messages table?

If so they try the following query which should eliminate any message.ID records
that have a match in records Chart Pull - Requests Based on Message Id


INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
I understand that a strong where clause in an append query can eliminate
duplicate records. I can't seem to find a clause that will work. I've tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
 
J

John Spencer

My guess is that your field types don't match in the On expression. Are
Messages.ID and CPR.[Message ID] the same type of field? If one is a text
field and the other is a number field that could be the cause of the
problem.

In addition it could be any of the other pairs of fields that is causing the
problem for the same reason.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
On Messages.ID = CPR.[Message ID]
WHERE CPR.[Message ID] is Null
Melynda E. said:
Messages.ID is the primary key in the Messages table. However, when I try
this new query, I receive an error (Type mismatch in expression). Any
advice
Is Messages.ID the primary key in the Messages table?

If so they try the following query which should eliminate any message.ID
records
that have a match in records Chart Pull - Requests Based on Message Id


INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
I understand that a strong where clause in an append query can
eliminate
duplicate records. I can't seem to find a clause that will work. I've
tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the
current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff
Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call
Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That was it! A simple field type. Thank you so much! I can stop pulling at
my hair again.

John Spencer said:
My guess is that your field types don't match in the On expression. Are
Messages.ID and CPR.[Message ID] the same type of field? If one is a text
field and the other is a number field that could be the cause of the
problem.

In addition it could be any of the other pairs of fields that is causing the
problem for the same reason.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
On Messages.ID = CPR.[Message ID]
WHERE CPR.[Message ID] is Null
Melynda E. said:
Messages.ID is the primary key in the Messages table. However, when I try
this new query, I receive an error (Type mismatch in expression). Any
advice
Is Messages.ID the primary key in the Messages table?

If so they try the following query which should eliminate any message.ID
records
that have a match in records Chart Pull - Requests Based on Message Id


INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
On Messages.ID = CPR.[Message ID]
WHERE CPR.[Message ID] is Null

Melynda E. wrote:

I understand that a strong where clause in an append query can
eliminate
duplicate records. I can't seem to find a clause that will work. I've
tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the
current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff
Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call
Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok. I have a similar query. I know there are 2 records that should append,
but they aren't being added to the table.

INSERT INTO [Chart Pull - Requests] ( [Rx Refill ID], Requestor, [Date],
[Time], Physician, [Patient Name], [Patient DOB], [Patient MRN], [Physician
Team], [MR Notes], [Chart Pull Complete], [Chart Pull Request Date/Time] )
SELECT [Refill Request].[Refill Request ID], [Refill Request].[Staff Name],
[Refill Request].Date, [Refill Request].Time, [Refill Request].Physician,
[Refill Request].[Patient Name], [Refill Request].[Patient DOB], [Refill
Request].[Patient MRN], Physicians.Refills, [Refill Request].[MR Notes],
[Refill Request].[Chart Pull Complete], [Refill Request].[Chart Pull Request
Date/Time]
FROM ([Refill Request] INNER JOIN [Chart Pull - Requests] AS CPR ON [Refill
Request].[Refill Request ID]=CPR.[Rx Refill ID]) INNER JOIN Physicians ON
[Refill Request].Physician=Physicians.Physician
WHERE (((CPR.[Rx Refill ID]) Is Null));


John Spencer said:
My guess is that your field types don't match in the On expression. Are
Messages.ID and CPR.[Message ID] the same type of field? If one is a text
field and the other is a number field that could be the cause of the
problem.

In addition it could be any of the other pairs of fields that is causing the
problem for the same reason.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
On Messages.ID = CPR.[Message ID]
WHERE CPR.[Message ID] is Null
Melynda E. said:
Messages.ID is the primary key in the Messages table. However, when I try
this new query, I receive an error (Type mismatch in expression). Any
advice
Is Messages.ID the primary key in the Messages table?

If so they try the following query which should eliminate any message.ID
records
that have a match in records Chart Pull - Requests Based on Message Id


INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages LEFT JOIN [Chart Pull - Requests] as CPR
On Messages.ID = CPR.[Message ID]
WHERE CPR.[Message ID] is Null

Melynda E. wrote:

I understand that a strong where clause in an append query can
eliminate
duplicate records. I can't seem to find a clause that will work. I've
tried
tblMessage.ID not in tblChart Pull-Request.Message ID. Below is the
current
SQL without my failed clause.

INSERT INTO [Chart Pull - Requests] ( [Message ID], [Date], [Time],
Requestor, Physician, [Physician Team], [Patient Name], [Patient DOB],
[Patient MRN], Reason, [Chart Pull Complete], [Chart Pull Completion
Date/Time], [Chart Not Found], [MR Notes] )
SELECT Messages.ID, Messages.Date, Messages.Time, Messages.[Staff
Name],
Messages.Physician, Messages.[Physician Team], Messages.[Patient Name],
Messages.[Patient DOB], Messages.[Patient MRN], Messages.[Call
Subject],
Messages.[Chart Pull Complete], Messages.[Chart Pull Completion
Date/Time],
Messages.[Chart Not Found], Messages.[MR Notes]
FROM Messages;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append query with no results 6
run append query from form 3
FWT Newsletter - Weekly - October 25, 2004 8
FWT Newsletter - Weekly - November 17, 2003 0
FWT Newsletter - Weekly - October 4, 2004 2
FWT Newsletter - Weekly - November 3, 2003 2
FWT Newsletter - Weekly - September 13, 2004 1
FWT Newsletter - Weekly - October 11, 2004 0

Top