Append query with no results

M

Melynda E.

I know there are records that should append, but they aren't being added to
the table. I've checked the properties of all fields in both tables to make
certain if required data is present. Any advice?

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));
 
J

Jerry Whittle

Are you getting an error message or just no records being appended? If an
error message, please post it.

Try running the following SQL and see if it returns any records. It's
basically just the select section extracted from the append query.

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));
 
M

Melynda E.

There is no error message, it just states 0 records were appended. I tried
the query you suggested, no records were returned.

Jerry Whittle said:
Are you getting an error message or just no records being appended? If an
error message, please post it.

Try running the following SQL and see if it returns any records. It's
basically just the select section extracted from the append query.

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));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Melynda E. said:
I know there are records that should append, but they aren't being added to
the table. I've checked the properties of all fields in both tables to make
certain if required data is present. Any advice?

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));
Click to expand...
Click to expand...
 
L

LeAnne

Hi Melynda,

Some general suggestions for dealing with difficulties in Action queries:

First, change it to a regular Select query. Are all the records there?

If not, delete all the fields and re-add them back to the query one at a
time, checking the Dataset View frequently. Are all the records there?

If not, remove all db objects except the "base" table from the query.
Add the needed fields from the base table one at a time, checking the
Datasheet. Include criteria if you have any. Are all the expected
records from that table there?

If not, there's the problem. Make sure the records you're looking for
are actually *in* the table.

If so, begin re-adding tables and their needed fields, one at a time,
back into the query. Include criteria if you have any. Make sure you
check your joins, too (Look for unmatched records in joined tables by
putting the tables in question & their linking fields in the query and
checking left- and right-joins. Post back if you need info on how to do
this, or use the Find Unmatched Query Wizard.). Again, peek at the
Datasheet occasionally to be sure the query is returning expected results.

If you still cannot identify the problem, post back with a description
of how you "know" which records should be there but aren't. Some
background on your tables' structures would help, too. Be sure to
include the text of any error messages that pop up when you run the
Append (e.g. "Access didn't append x records due to key violations.")

hth,

LeAnne
 
J

Jerry Whittle

If the Select query did not return any records, nothing will be appended. I
extracted that select statement from your append query.

You first need to get the select statement to return the records that you
wish to append. Next modify the append query to work the same way.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Melynda E. said:
There is no error message, it just states 0 records were appended. I tried
the query you suggested, no records were returned.

Jerry Whittle said:
Are you getting an error message or just no records being appended? If an
error message, please post it.

Try running the following SQL and see if it returns any records. It's
basically just the select section extracted from the append query.

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));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Melynda E. said:
I know there are records that should append, but they aren't being added to
the table. I've checked the properties of all fields in both tables to make
certain if required data is present. Any advice?

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));
Click to expand...
Click to expand...
Click to expand...
 
M

Melynda E.

I've removed each of the fields one by one, without success in solving this
problem. I have also run the query as a select only query. This did return
all of the records that should be returned. The error message states
"Microsoft can't append all the records in the append query. Access set 0
fields to Null due to type convesion failure, didn't add 2 records to teh
table due to key violations, 0 records due to lock violatiosn, and 0 records
due to validation rule violations."

I did remove the key on both tables, but still no luck.

INSERT INTO [Chart Pull - Requests] ( [Rx Refill ID], Requestor, [Chart Pull
Request Date], [Chart Pull Request Time], Physician, [Patient Name], [Patient
DOB], [Patient MRN], [Physician Team], [MR Notes], [Chart Pull Complete] )
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]
FROM ([Refill Request] LEFT 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));



LeAnne said:
Hi Melynda,

Some general suggestions for dealing with difficulties in Action queries:

First, change it to a regular Select query. Are all the records there?

If not, delete all the fields and re-add them back to the query one at a
time, checking the Dataset View frequently. Are all the records there?

If not, remove all db objects except the "base" table from the query.
Add the needed fields from the base table one at a time, checking the
Datasheet. Include criteria if you have any. Are all the expected
records from that table there?

If not, there's the problem. Make sure the records you're looking for
are actually *in* the table.

If so, begin re-adding tables and their needed fields, one at a time,
back into the query. Include criteria if you have any. Make sure you
check your joins, too (Look for unmatched records in joined tables by
putting the tables in question & their linking fields in the query and
checking left- and right-joins. Post back if you need info on how to do
this, or use the Find Unmatched Query Wizard.). Again, peek at the
Datasheet occasionally to be sure the query is returning expected results.

If you still cannot identify the problem, post back with a description
of how you "know" which records should be there but aren't. Some
background on your tables' structures would help, too. Be sure to
include the text of any error messages that pop up when you run the
Append (e.g. "Access didn't append x records due to key violations.")

hth,

LeAnne

I know there are records that should append, but they aren't being added to
the table. I've checked the properties of all fields in both tables to make
certain if required data is present. Any advice?

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));
Click to expand...
Click to expand...
 
L

LeAnne

Melynda,

Something's amiss with your table structure, I think. You have 3 db
objects ([Chart Pull - Requests], [Refill Request], and [Physicians])?
Both [Chart Pull - Requests]and [Refill Request] appear to have the same
PK? (What are their field types? Are they both, as I suspect,
Autonumbers?) Both tables also contain redundant fields (Patient Name,
DOB, etc.)? There are records in [Refill Request] that do not have
matches in [Chart Pull - Request]? And you are trying to insert those
records into [Chart Pull - Requests] while trying to pull in information
from other records in [Chart Pull - Request]? Am I summarizing correctly?

If so, then I *strongly* suggest you reconsider your database schema.
This does not appear to be a normalized design, and the append will
likely continue to fail. Think about your entities (real-life persons,
places, things, or events) and attributes (categories of information
related to each entity), and how they are related to each other. For
starters, you have patients. You have physicians. You have physician
teams. You have visits/examinations. You have treatments. You have
meds. Each patient can be examined one OR MORE times. Each patient can
be examined by one OR more physician teams. Each team is composed of
one OR MORE physicians. Each physician can be on one OR MORE teams.
Each physician can prescribe one OR MORE meds for one OR MORE patients.
Each patient can receive one OR MORE treatments and one OR MORE meds.

There may be a way to fudge a solution, but I can't think of one off the
top of my head. Sorry.

Hope this helps,

LeAnne
I've removed each of the fields one by one, without success in solving this
problem. I have also run the query as a select only query. This did return
all of the records that should be returned. The error message states
"Microsoft can't append all the records in the append query. Access set 0
fields to Null due to type convesion failure, didn't add 2 records to teh
table due to key violations, 0 records due to lock violatiosn, and 0 records
due to validation rule violations."

I did remove the key on both tables, but still no luck.

INSERT INTO [Chart Pull - Requests] ( [Rx Refill ID], Requestor, [Chart Pull
Request Date], [Chart Pull Request Time], Physician, [Patient Name], [Patient
DOB], [Patient MRN], [Physician Team], [MR Notes], [Chart Pull Complete] )
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]
FROM ([Refill Request] LEFT 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));



:

Hi Melynda,

Some general suggestions for dealing with difficulties in Action queries:

First, change it to a regular Select query. Are all the records there?

If not, delete all the fields and re-add them back to the query one at a
time, checking the Dataset View frequently. Are all the records there?

If not, remove all db objects except the "base" table from the query.
Add the needed fields from the base table one at a time, checking the
Datasheet. Include criteria if you have any. Are all the expected
records from that table there?

If not, there's the problem. Make sure the records you're looking for
are actually *in* the table.

If so, begin re-adding tables and their needed fields, one at a time,
back into the query. Include criteria if you have any. Make sure you
check your joins, too (Look for unmatched records in joined tables by
putting the tables in question & their linking fields in the query and
checking left- and right-joins. Post back if you need info on how to do
this, or use the Find Unmatched Query Wizard.). Again, peek at the
Datasheet occasionally to be sure the query is returning expected results.

If you still cannot identify the problem, post back with a description
of how you "know" which records should be there but aren't. Some
background on your tables' structures would help, too. Be sure to
include the text of any error messages that pop up when you run the
Append (e.g. "Access didn't append x records due to key violations.")

hth,

LeAnne


Melynda E. wrote:

I know there are records that should append, but they aren't being added to
the table. I've checked the properties of all fields in both tables to make
certain if required data is present. Any advice?

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));
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

Duplicate Records from Append Query 8
Show null in Select Query. 2
Help with Duplicate Output Alias Error 'Exp1' 0
Query to Pull Most Recent Call with a Callback Date Scheduled 10
Avoid Duplicates with an Append Query 1
MS Access query 1
AutoNumber Field Not Populating with Append Query 2
Invalid Argument 2

Top