Re: Append query with no results
- From: LeAnne <nospam@xxxxxxxxxx>
- Date: Thu, 18 May 2006 14:09:30 -0400
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
Melynda E. wrote:
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" wrote:
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));
- References:
- Re: Append query with no results
- From: LeAnne
- Re: Append query with no results
- From: Melynda E.
- Re: Append query with no results
- Prev by Date: Re: multiple results - need to display on one line
- Next by Date: RE: Adding Columns in a Query don't work
- Previous by thread: Re: Append query with no results
- Next by thread: RE: Append query with no results
- Index(es):
Relevant Pages
|
