Inserting data from 2 tables into 1

[sphynx]

What I am basically working on is a projects database to control R&D development through to full scale manufacture thus ensure all tasks are completed when and where necessary.

Currently the system is run via 100's of linked xls sheets all of which are updated individually and take an eon to go through and update. There are approx 50 - 100 projects running at once with over 300 steps & 20 different people responsible for tasks.

The project steps are set and stored in a table as are all the other variables so the data entry criteria is set. I have attached a file containing a picture of my relationships to help with the explanation below

I have 2 master tables, 1 which holds the tasks & responsibilities "LaunchProcessList_Tbl" & one which hold project specific info ie Customer, Launch date, Developer "ProductID_Tbl"

The info for the "ProductID_Tbl" is entered via a form with Combo Box data input.

Once a record is saved I would like to pull info from the "ProductID_Tbl" & the "LaunchProcessList_Tbl" into a cross over table, I am not quite sure how to do this.

I would like the crossover table to contain:

PK - ID - Autonumber
FK - Product ID - From "ProductID_Tbl"
FK - Gate ID - From "LaunchProcessList_Tbl" - approx 300 tasks import each Product ID

I will also add to this Due Date, Status and Comment

Unfortunatley I can't get my mind around how to get all the 300 tasks into my crossover table with reference to a single Product Id when updating my "ProductID_Tbl", ie

Product ID 1 - Task 1
Product ID 1 - Task 2
Product ID 1 - Task 3

I hope I have posted in a way that someone else will be able to understand!!!

I would be grateful for any help

 

[sphynx]

Having had a nights sleep and an early start this morning, I have had a look at append queries. The following SQL gives me the result I want within the query viewer

INSERT INTO ProductLaunchCrossover_Tbl ( [GATE ID], [PRODUCT ID] )
SELECT LaunchProcessList_Tbl.[GATE ID], Max(ProductID_Tbl.[PRODUCT ID]) AS [MaxOfPRODUCT ID]
FROM ProductID_Tbl, LaunchProcessList_Tbl
GROUP BY LaunchProcessList_Tbl.[GATE ID]
ORDER BY LaunchProcessList_Tbl.[GATE ID];


However when I run the append query I am now getting the following error

"281 records not added due to a key violation"

GATE ID & PRODUCT ID are both PK in there separate table and are generated via auto number.

Can someone help me understand why I cant get this to work

 

[EraserveAP]

What is the Table ProductLaunchCrossover Data types of of the fields [GATE ID] and [PRODUCT ID]?
I am not 100% positive but it sounds like to me you are trying to Append in to the wrong data type.


Another possibility is your ProductLaunchCrossover may contain data and a field is either primary Key or Indexed as No Duplicates, thereby making the append could add duplicate values to the field.

Then again I suppose it could be none of the above.
Can you provide a sample database?

 

[sphynx]

I checked your suggestion previously as a matter of course and found no errors which was what prompted me to post as I couldn't work it out!!

After some more searching I removed a 1 to many relationship with the "status" field attached to the append table and all is good.

Thanks for the response though

 

[EraserveAP]

I honestly do not know why your Query fails, however I managed to duplicate it in some part. I copied your sql into a query and remade it so that I could use it. I received a similar error to yours. I have no clue why as everything appeared in order as apparently yours was.
My solution or work around was to delete the Table I was appending to and recreated the table. I then started a new Query similar to yours. Noting my tables and fields are not the same as yours.

If this does not work for you then perhaps you could run your Append Query as a make table and then Append the new Table into your ProductLaunchCrossover_Tbl.

I apologize if this is of no help to you.

Perhaps someone more knowledgeable in these types of errors could be of assistance?