Append query not working, Null values? (1 Viewer)

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
I have an append query created to add files to a table from an imported excel file. The table to which I am appending (SubTBL) has a field names Observation. This field is not required. There is a relationship to another table (ObservationTBL) which has 3 records. The information I am trying to append includes 7000 records, about 4000 have observations assigned and they match the information in the ObservationTBL. The remaining 3000 have blank records for observation. When I run the append query, only those with observations are appended. The rest are not added because of "key violations". I removed the relationship between the ObservationTBL and the SubTBL and the append query runs. Then when I try to re-do the relationship it fails "Violates referential integrity rules"

To be clear:
1. The records without an observation are blank. (There are other fields in this append that have blank fields also and they are not causing any problems)
2. The field observation is NOT set to required.

What is going on here? Is it not allowing Null records? If so why and how can I correct this? Any help greatly appreciated.

Sue
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:59
Joined
Aug 11, 2003
Messages
11,696
Is their key blank too? That would explain the error.

In a database there is no real need to have blank records for the sake of having blank records....
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
Is their key blank too? That would explain the error.

If you mean primary key - it is not blank as it is an auto number

In a database there is no real need to have blank records for the sake of having blank records....

They are not blank "for the sake of having blank records" (I am not sure exactly what that means) They are blank because the user did not have that information available to enter. The system is set up this way because of the user's requirements. The records are not required and therefore should be able to leave it blank.
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
If anyone has any thoughts on this i would appreciate your comments - I need to wrap this project up in the morning.

Thanks
Sue
 

ronc

New member
Local time
Today, 22:59
Joined
Mar 25, 2014
Messages
7
Hi Sue

When you create a relationship between two tables access requires something in the related field that matches one of the values in the primary table.
Couple of options depending on the purpose of the link:
Drop the relationship between these two tables then use the ObservationTBL as a lookup list in forms etc You don't need a relationship to use a table for lookups. You can restrict input into the related field to values in the list based on the lookup table without a relationship.

or

Add a default value option in the primary table and add this value to the data you are importing. ie line 4 in ObservationTBL has value of default.

I would probably go with the first option but again it depends on the reason for the relationship between tables.

Ron
 

MarkK

bit cruncher
Local time
Today, 04:59
Joined
Mar 17, 2004
Messages
8,178
When you create the relationship between the tables, do not select the "Enforce Referential Integrity" option. You know how you drag a field in one table and drop it on the field to link to in the other table, and a dialog pops up? Just click create in that dialog. See if that changes anything. The relationship line in the diagram will not be as dark.
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
Thank you both for your responses. (I continue to learn something everyday in Access.) I did not realize that by using referential integrity, is was similar to making a field required. I will have to consider the options. My concern is that without enforcing referential integrity if the user edits the observation the system will not update the existing records. I guess there would be somehow to add code to the form that managed the observation table to make this happen but it seems like a lot of extra work to do this.
 

ronc

New member
Local time
Today, 22:59
Joined
Mar 25, 2014
Messages
7
Hi Sue

When using a table for looking up values, you store the line ID from the lookup table in your main data table then on forms and reports you link the two tables and show the description from the lookup table. that way if the description changes all of the records with that description will be updated. The down side is if the description is changed to something unrelated it then makes all of the existing records incorrect. You don't have to create a relationship to be able to use a table as a lookup.

OK change would be correction of spelling or expanding on the entry
Not OK change would be changing cold to hot etc..

Generally you would then not allow the users to modify the obs table but they could add to it.

In the past I have used a field as its own lookup table rather than having a separate table, I don't think this is a great idea unless you're not expecting to know what values are going to be required. It's probably never the best idea.... It's better to have an add new obs button on your form that inserts a new obs entry then sets it for the current record. Again it depends on how you want things to be updated/changed

forgive my rambling.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:59
Joined
Aug 11, 2003
Messages
11,696
They are not blank "for the sake of having blank records" (I am not sure exactly what that means) They are blank because the user did not have that information available to enter. The system is set up this way because of the user's requirements. The records are not required and therefore should be able to leave it blank.

Your description fits exactly my "blank for the sake of being blank"
Blank records mean nothing in a database, it is a simple outer join where some records may or may not exist.
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
When you create a relationship between two tables access requires something in the related field that matches one of the values in the primary table.

I am totally confused by this relationship blocking blank records. When one manually enters records with the relationship set up they can leave the observation blank and there is no problem. Therefore the relationship is not blocking me from leaving the field blank. (and this is with referential integrity set up). I really thought that the "required" setting in the fields property determined if the field could be left blank or a value was required.

Thanks
Sue
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
SOLVED! It was not that the relationship was causing the problem - you can in fact have records with nothing in them even if the relationship includes enforce referential integrity (or at least I was able to accomplish it) It turns out that the fields with nothing in them need to be set to Null. A query that changed the fields from "" (empty string) to Null allowed the append query to work perfectly. I am hoping that by posting my solution I can help others that might have the same problem.

Thanks to those that replied.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 12:59
Joined
Aug 11, 2003
Messages
11,696
storing null values for the sake of having the record there is still a bad (design) idea.
 

Design by Sue

Registered User.
Local time
Today, 04:59
Joined
Jul 16, 2010
Messages
640
storing null values for the sake of having the record there is still a bad (design) idea.

I am really not understanding your comments on this. There are other fields within this record that have information. It is just that they have not assigned an observation yet (and may never assign one). It is not that the entire record is the observation only, observation is only one of nine fields.
 

muse

New member
Local time
Today, 07:59
Joined
Jun 15, 2018
Messages
2
Sue, if you're still out there... thanks for solving this yourself. This is exactly my issue!
 

Users who are viewing this thread

Top Bottom