Hi all,
I'm trying fix a broken append query in an access database, which has been tossed to me at my new job. I'm relatively new to using Access, so I read through the book "Microsoft Access 97" by microsoft press from front to back over the holidays (I'm using Access 2002, but the book was freely available to me). I still can't figure out why this particular append query isn't working. The original (now broken) query used a filter to get data from two tables, and then appended to one table from that filter. Instead of fixing that querry, to simplify things I just created my own append query to directly append from the two tables to the third (without using a filter in between).
The basic problem is that we need to append patient data from two tables to a third table. I made the criteria for the append be patients who have visited in the last 21 days (i.e. >=Now()-20 for 'Visit Date'). I figure even if my users run the append more then once in 21 days, there won't be duplicate data appended because of primary key violations. When I add myself to the "Clinic Log" with a visit date of last week, and take care to add myself to the "Patient History" table, running the append should insert me into the "Clinic Data" table. But when I run the append I get a validation rule violation error. I've made sure there are no validation rules for any of the fields that are being broken, and that the data types match for all the appended fields. A select query of the same data from the two tables works fine. Trying to append that data to the third results in the violation.
Is there anything I haven't mentioned that you think may be causing validation rule violations? I haven't found any helpful information on this in my Access book, nor in Access' help files. I wish Access had a 'debugger' of sorts that would explain this violation in a little more detail.
Thanks,
jay9333
P.S.
I'll list the details of how the tables are designed, and how the querry is set up.
The Query is appending to a table called "Flow Data". Here is a list of Flow Data's fields and data types:
By the way, there are no validation rules for any of the fields except on % Lymph and WBC, which each have a rule saying, "Is Null Or Between 1 And 90". That shouldn't be a problem though, because I don't append anything to that field, so it should be Null and fine.
Patient History (which is an Access table linked to the database containing the Flow Data table):
Clinic Log (which is also an Access table linked to the database containing the Flow Data table):
Here is what is being appended:
As you can see for yourself, all the data types match for appended fields. None of the fields in "Flow Data" that aren't being appended to are marked as 'required', so that shouldn't be the problem. Some of the fields have different lengths (for instance Flow Data's comments field has a length of 100, while Clinic Log's has a length of 85)... but this shoudln't matter because in all such cases, the table being appended to has a larger field length, so no data shoudl be lost. I just have no idea what is causing the validation rule violation.
I'll mention one more thing Just for in case it matters... when I go into the "Clinic_Log" database (which is where the Clinic Log table" is linked from), it turns out that 'Clinic Log' isn't even a table... it is a 'form' and it is called "Log Clinic Samples". I can't really find a way to edit its field properties (not that I need to, but I was just curious if I could). When I view the forms 'properties' the record source is identified as "Clinic Log Query". But in the database window, there is no such query listed. When I bring up the design view of the query from the forms 'record source' field, it shows the querry getting its data from the "Clinic Log table". But there is no such table. I don't know if this is the problem or what. I was able to add myself to the "Clinic Log table" (a linked table in the same database as 'flow data') just fine, so it must exist somewhere. The database containing the 'flow data' table says it exists in the "Clinic Log" database... but I don't see it in there. I suspect I'm just not familiar enough with access to know how tables are always stored, and that this doesn't have anything to do with the problem. But I thought I'd mention it to give as much detail as possible.
I'm trying fix a broken append query in an access database, which has been tossed to me at my new job. I'm relatively new to using Access, so I read through the book "Microsoft Access 97" by microsoft press from front to back over the holidays (I'm using Access 2002, but the book was freely available to me). I still can't figure out why this particular append query isn't working. The original (now broken) query used a filter to get data from two tables, and then appended to one table from that filter. Instead of fixing that querry, to simplify things I just created my own append query to directly append from the two tables to the third (without using a filter in between).
The basic problem is that we need to append patient data from two tables to a third table. I made the criteria for the append be patients who have visited in the last 21 days (i.e. >=Now()-20 for 'Visit Date'). I figure even if my users run the append more then once in 21 days, there won't be duplicate data appended because of primary key violations. When I add myself to the "Clinic Log" with a visit date of last week, and take care to add myself to the "Patient History" table, running the append should insert me into the "Clinic Data" table. But when I run the append I get a validation rule violation error. I've made sure there are no validation rules for any of the fields that are being broken, and that the data types match for all the appended fields. A select query of the same data from the two tables works fine. Trying to append that data to the third results in the violation.
Is there anything I haven't mentioned that you think may be causing validation rule violations? I haven't found any helpful information on this in my Access book, nor in Access' help files. I wish Access had a 'debugger' of sorts that would explain this violation in a little more detail.
Thanks,
jay9333
P.S.
I'll list the details of how the tables are designed, and how the querry is set up.
The Query is appending to a table called "Flow Data". Here is a list of Flow Data's fields and data types:
Code:
Field Name Data Type
pri key Type Text
pri key Last Name Text
pri key Patient ID Text
pri key Visit Date Date/Time
Physician Text
Run Date Date/Time
Run Number Text
CD3 Avg Number
45 Number
4 Number
8 Number
NK Number
19 Number
WBC Number
% Lymph Number
Study Text
Comments TextBy the way, there are no validation rules for any of the fields except on % Lymph and WBC, which each have a rule saying, "Is Null Or Between 1 And 90". That shouldn't be a problem though, because I don't append anything to that field, so it should be Null and fine.
Patient History (which is an Access table linked to the database containing the Flow Data table):
Code:
Field Name Data Type
pri key Last Name Text
pri key First Name Text
PID Text
Birthdate Date/TimeClinic Log (which is also an Access table linked to the database containing the Flow Data table):
Code:
Field Name Data Type
pri key Last Name Text
pri key First Name Text
pri key Bleed Date Date/Time
pri key Type Text
Date Received Date/Timet
Specimen Text
Source Text
Invoice Text
Study Text
Panel Text
Invoice2 Text
Study2 Text
Panel2 Text
Researcher Text
Comments TextHere is what is being appended:
Code:
Type - from Clinic Log ; appending to same field name in Flow Data
Last Name - from Clinic Log ; appending to same field name in Flow Data
PID - from Patient History ; appending to "Patient ID" in Flow Data
Bleed Date- from Clinic Log ; appending to "Visit Date" in Flow Data
Study - from Clinic Log ; appending to same field name in Flow Data
Comments - from Clinic Log ; appending to same field name in Flow Data
Invoice - from Clinic Log ; appending to "Physician" in Flow DataAs you can see for yourself, all the data types match for appended fields. None of the fields in "Flow Data" that aren't being appended to are marked as 'required', so that shouldn't be the problem. Some of the fields have different lengths (for instance Flow Data's comments field has a length of 100, while Clinic Log's has a length of 85)... but this shoudln't matter because in all such cases, the table being appended to has a larger field length, so no data shoudl be lost. I just have no idea what is causing the validation rule violation.
I'll mention one more thing Just for in case it matters... when I go into the "Clinic_Log" database (which is where the Clinic Log table" is linked from), it turns out that 'Clinic Log' isn't even a table... it is a 'form' and it is called "Log Clinic Samples". I can't really find a way to edit its field properties (not that I need to, but I was just curious if I could). When I view the forms 'properties' the record source is identified as "Clinic Log Query". But in the database window, there is no such query listed. When I bring up the design view of the query from the forms 'record source' field, it shows the querry getting its data from the "Clinic Log table". But there is no such table. I don't know if this is the problem or what. I was able to add myself to the "Clinic Log table" (a linked table in the same database as 'flow data') just fine, so it must exist somewhere. The database containing the 'flow data' table says it exists in the "Clinic Log" database... but I don't see it in there. I suspect I'm just not familiar enough with access to know how tables are always stored, and that this doesn't have anything to do with the problem. But I thought I'd mention it to give as much detail as possible.
