Appending one table to multiple relational tables

[Aldricla]

I have imported data from MS Excel into a table wherein I would like to
append the data to the relational tables existing in the database. Several
tables already exist of which I need to only match the records up to:
tblOrbiter, tblEngine, tblFlowliners, tblSlots, tblRuns, tblSlots, tblMolds.
I would like to append fields to the related tables: tblSections, tblZones,
tblFindings, tblPhotos.

When I attempt to test an append query for 'tblFindings' I receive the
message:

Cannot join on Memo, OLE, or Hyperlink Object
(AnalysisImport2.FindingNo=Dev_tblFindings.FindingNo And
AnalysisImport2.Length=Dev_tblFindings.Length And
AnalysisImport2.FindingComments=Dev_tblFindings.FindingCmt And
AnalysisImport2.Disposition=Dev_tblFindings.Dispo

The query looks as follows:

INSERT INTO Dev_tblFindings ( FindingNo, Finding, Length, FindingCmt,
SEMInspect, Dispo, DispoDate, ZoneID, Last_Upd, Last_Upd_By )

SELECT DISTINCT AnalysisImport2.FindingNo, AnalysisImport2.FindingType,
AnalysisImport2.Length, AnalysisImport2.FindingComments,
AnalysisImport2.SEMInspector, AnalysisImport2.Disposition,
AnalysisImport2.DispoDate, Dev_tblZones.ZoneID, Now() AS LastUpd,
[Dev_tblFindings].[NetworkUserID()] AS LastUpdBy

FROM ((((((((Dev_tblOrbiter
INNER JOIN Dev_tblEngine ON Dev_tblOrbiter.OrbID = Dev_tblEngine.OrbID)
INNER JOIN Dev_tblFlowliners ON Dev_tblEngine.EngID =
Dev_tblFlowliners.EngID) INNER JOIN Dev_tblSlots ON Dev_tblFlowliners.FlowID
= Dev_tblSlots.FlowID) INNER JOIN Dev_tblRuns ON Dev_tblSlots.SlotID =
Dev_tblRuns.SlotID)
INNER JOIN Dev_tblMolds ON Dev_tblRuns.RunID = Dev_tblMolds.RunID)
INNER JOIN AnalysisImport2 ON (Dev_tblMolds.Center = AnalysisImport2.Center)
AND (Dev_tblOrbiter.VehID = AnalysisImport2.OrbNo)
AND (Dev_tblOrbiter.Flight = AnalysisImport2.FlightNo)
AND (Dev_tblEngine.EngNo = AnalysisImport2.EngNo)
AND (Dev_tblFlowliners.Flow = AnalysisImport2.Flow)
AND (Dev_tblSlots.SlotNo = AnalysisImport2.SlotNo)
AND (Dev_tblRuns.RunNo = AnalysisImport2.RunNo)
AND (Dev_tblMolds.MoldNo = AnalysisImport2.MoldNo))

INNER JOIN Dev_tblSections ON (Dev_tblMolds.MoldID = Dev_tblSections.MoldID)
AND (AnalysisImport2.SectionNo = Dev_tblSections.SectionNo))

INNER JOIN Dev_tblZones ON (AnalysisImport2.ZoneNo = Dev_tblZones.ZoneNo)
AND (Dev_tblSections.SectionID = Dev_tblZones.SectionID))

INNER JOIN Dev_tblFindings ON (AnalysisImport2.SEMInspector =
Dev_tblFindings.SEMInspect)
AND (AnalysisImport2.DispoDate = Dev_tblFindings.DispoDate)
AND (AnalysisImport2.Disposition = Dev_tblFindings.Dispo)
AND (AnalysisImport2.FindingComments = Dev_tblFindings.FindingCmt)
AND (AnalysisImport2.Length = Dev_tblFindings.Length)
AND (AnalysisImport2.FindingType = Dev_tblFindings.Finding)
AND (AnalysisImport2.FindingNo = Dev_tblFindings.FindingNo)
AND (Dev_tblZones.ZoneID = Dev_tblFindings.ZoneID)

WHERE (((Dev_tblOrbiter.VehID)=[AnalysisImport2]![OrbNo])
AND ((Dev_tblOrbiter.Flight)=[AnalysisImport2]![FlightNo])
AND ((Dev_tblEngine.EngNo)=[AnalysisImport2]![EngNo])
AND ((Dev_tblFlowliners.Flow)=[AnalysisImport2]![Flow])
AND ((Dev_tblSlots.SlotNo)=[AnalysisImport2]![SlotNo])
AND ((Dev_tblRuns.RunNo)=[AnalysisImport2]![RunNo])
AND ((Dev_tblMolds.MoldNo)=[AnalysisImport2]![MoldNo])
AND ((Dev_tblSections.SectionNo)=[AnalysisImport2]![SectionNo])
AND ((Dev_tblZones.ZoneNo)=[AnalysisImport2]![ZoneNo]));

Any help would be appreciated! Thanks!

 

[John Vinson]

When I attempt to test an append query for 'tblFindings' I receive the
message:

Cannot join on Memo, OLE, or Hyperlink Object

Well, are any of the fields used in the JOIN clauses Memo, OLE, or
Hyperlink objects? If so you cannot use them for a JOIN.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[aldricla]

Yes, The FindingComments field is a memo field. But, I need to append that
data to the 'Findings' table. How do I go about that?

 

[John Vinson]

Yes, The FindingComments field is a memo field. But, I need to append that
data to the 'Findings' table. How do I go about that?

You can APPEND it - you just can't use it to join two tables. Since I
do not understand your (apparently very complex) table relationships I
can't edit your SQL to be correct, but you should be joining your
tables by joining the primary key of each "one" table to a related
foreign key in each "many" table; it looks like you're joining on
multiple unrelated fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps