Append Query Error Help (validation rule violations)

Question

I am trying to run an append query (first time) to add notes to a table. I have a table full of electrical notes and each one has a note number. There is then a separate table with notes relating to each note number. On my report I am trying to get it to place all the relevant notes at the bottom of each switchboard however every time I add the notes table to the query it fails to fetch anything else. Therefore I am trying to run a query that will fill in the comments into the loads table based on the numbers, however it comes up with validation rule violations. I am not sure what this could be as both fields are identical. All help to do with both issues would be greatly appreciated. 

Thanks

Jordan

Answer 1

You said: "On my report I am trying to get it to place all the relevant notes at the bottom of each switchboard".

I would use a Subreport for that. Create a subreport on your Notes table. Then add it to the footer of your Switchboard group on your report linked on Note Numbers. That should give you what you want.

On a separate note. It is not a good idea to use spaces in object names. This can come back to haunt you.

Also, I would seriously consider a redesign. Your Document table should be split it into two tables.

ProjectInfo:

ProjectID (PK Autonumber)

ProjectName (Unique index)

SwitchboardID (FK) {Replace Link Field or use the MCCTag}

ClientID (FK to Client Table)

PlantID (FK to Plant table)

DocumentInfo

DocInfoID (PK Autonumber)

ProjectID (FK)

NumberTypeID (FK to Types table)

DocNumber

And you would add a lookup table for the types.

NumberTypes

NumberTypeID (PK Autonumber)

NumberType (JE Project, Client Project, JE Load Schedule, etc.)

This creates a more normalized vertical structure.

Answer 2

Ok, what do you want to know about the structure?

Here is a list of the tables and their relationships

Answer 3

Not without understanding your table structure and relationships. 

A Validation rule error, means that you have a validation rule set on the table level. And appending data would violate that rule. But, as Ken said, you shouldn't be duplicating data.

If adding the Notes table to your query produces no results, then its a problem with the relationships and you probably need an Outer join. But without knowing your structure that's just a guess.

Answer 4

Ok, if thats a bad idea then could you please tell me how to get the report query to fetch the notes data from the notes table? It currently fetches from 3 other tables (switchboards, loads and document information) but whenever I add notes it wont fetch anything

Answer 5

Can you post details of the structures of the loads and notes tables, along with details of the how the tables are related.

Appending data from one table to another is almost certainly a bad idea.  The database relational model is premised on each datum being stored once and once only, and duplication opens the door to update anomalies.