Append to M2M & Stumped

[DarrenBoyer]

I have a simple project but the relationships and/or append query functionality has had the best of me for a couple days.

This is compilation of multiple systems run at vehicle dealership. The main issue I'm running into is there is an EmployeeID to every Vehicle transaction and there is a seperate and arbitrary EmpID assigned to each Customer. I am trying to append the data from an imported table into these tables.

There are 3 main tables involved:
TblEmployees - EmployeeID -PK
TblCustomers - CustID - PK
TblVehicles - VehicleID - PK

I tried a jcn table called jcnVehicleTransactions where TransactionID is the PK and EmpID & VehID were joined in a 12M relationship. Actually when I realized what I wanted wasn't working I tried numerous append query/relationship combo's thinking I would stumble across the solution but still no success.

Any suggestions?

 

[Golom]

From your description, I don't see where the Many to Many relationship occurs. You said that there is one EmpID attached to a vehicle (i.e. a 1-to-Many) and there is an EmpID attached to a customer (also 1-to-Many). I assume that there is also a CustID attached to a vehicle (0 or 1 to Many).

To expand your table structure, you have
[tt]
Table Primary Key Foreign Keys
TblEmployees EmployeeID <None>
TblCustomers CustID EmpID (Employees:EmpID)
TblVehicles VehicleID CustID (Customer:CustID)
EmpID (Employees:EmpID)
[/tt]
Can you confirm that the above is the relationship or have I missed something?

You need to stipulate the source and destination of the data that you want to append to these tables. You said that there is "... an imported table ..." containing the data to be appended. Exactly what data does it contain and where (i.e. to which of the existing tables) do you want to append it?

 

[DarrenBoyer]

The table structure you defined was exactly what I started with.

My imported data was in a table I called tblManual. I have successfully appended the customer related fields to the TblCustomers and had appended the vehicle related fields to the TblVehicles. This was before I concluded that I needed an EmployeeID relationship with both TblCustomers and TblVehicles. After that design change I could only append to the TblCustomer's and I would get a 'validation rule violation' trying to append to TblVehicles. From reading everything I could find this appeared to be due to a relationship design flaw so I started trying options from there.

I appreciate your input.

 

[DarrenBoyer]

FWIW,

I imported the data into a seperate Vehicles table. This wasn't pretty but it got me satisfactory results.

I still tried appending this to my main vehicles table with no success. I tried allowing zero length in the text fields and removing any default to 0 in the number fields, as well as having the fk fields set to long integer. Must be some other little thing that has escaped me.