Appending a single table into 2 separate tables 1

Dear All,

Thanks for a great forum!!

I have the task of creating a DB and 3/4 of the way they tell me there is another data input to this db from the web.

The data from the web is stored in a Postgre database table!
The table has a description field, which has name, surname, address, email, comment in it.

I have created a database that stores Comments from Representors.

The Table names are as follows:
tblRepresentor
tblDocumentLinks
tblComments
tblOfficersResponse
public_at_incidents (ODBC Postgre table)
qryImap (query as it extracts the data into fields from the web page)
tblTmpImap
qryAppndTmpImapTbl



tblRepresentor
[ul]
[li]RepresentorID - AutoNumber[/li]
[li]RepTitle - Text[/li]
[li]RepName - Text[/li]
[li]RepSurname - Text[/li]
[li]BuildingNameNo - Text[/li]
[li]StreetName - Text[/li]
[li]Locality - Text[/li]
[li]County - Text[/li]
[li]PostalCode - Text[/li]
[li]Email - Text[/li]
[li]Telephone - Text[/li]
[li]ContactType - Text[/li]
[li]CompanyOrganisation - Text[/li]
[li]Representing - Text[/li]
[li]DateAdded - Date/Time[/li]
[/ul]

tblDocumentLinks
[ul]
[li]DocLinkID - Autonumber[/li]
[li]RepresentorID - Number[/li]
[li]DocumentLink - Hyperlink[/li]
[li]WhichConsultation - WhichConsultation - Text[/li]
[/ul]

tblComments
[ul]
[li]CommentID - AutoNumber[/li]
[li]RepresentorID - Number[/li]
[li]Document - Text[/li]
[li]Section - Text[/li]
[li]Comment - Memo[/li]
[li]SummaryOfComment - Memo[/li]
[li]ProposalModifications - Memo[/li]
[li]CommentType - Text[/li]
[li]HowReceived - Text[/li]
[li]DateCommRec - Date/Time[/li]
[li]DateCommAck - Date/Time[/li]
[li]NotifySubExam - Yes/No[/li]
[li]ExamParticipation - Yes/No[/li]
[li]LegalyComp - Yes/No[/li]
[li]Sound - Yes/No[/li]
[li]ChangesReq - Yes/No[/li]
[li]AprroveReject - Yes/No[/li]
[li]Checked - Yes/No[/li]
[li]Easting - Number[/li]
[li]Northing - Number[/li]
[/ul]


tblOfficersResponse
[ul]
[li]CommentID - Number[/li]
[li]OffRespId - Autonumber[/li]
[li]Officer - Text[/li]
[li]OfficerResponse - Text[/li]
[li]CouncilResponse - Text[/li]
[/ul]

The table below is provided by an external provider and cannot be modified...
The description field has most of the integral data we need.
The description field is a long string of data separated by the delimeter %09!
Shoddy I know!
I have written some code to split the description field into the fields we could use.
This then is the qryImap that defines these fields
public_at_incidents
[ul]
[li]ogc_fid - Number[/li]
[li]uniqueid - Text[/li]
[li]type - Text[/li]
[li]assetid - Text[/li]
[li]description - Memo[/li]
[li]interaction - Memo[/li]
[li]timeLogged - Date/Time[/li]
[li]active - Number[/li]
[li]easting - Number[/li]
[li]northing - Number[/li]
[li]wkb_geometry - Number[/li]
[/ul]

qryIMap
[ul]
[li]ogc_fid[/li]
[li]Site: assetid[/li]
[li]NameSurname: Replace(split_string([description],"%09",0),"%20"," ")[/li]
[li]Name: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([NameSurname]," ",0))[/li]
[li]Surname: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([NameSurname]," ",1))[/li]
[li]Email: split_string([description],"%09",1)[/li]
[li]Address: Replace(split_string([description],"%09",2),"%20"," ")[/li]
[li]active[/li]
[li]easting[/li]
[li]northing[/li]
[li]BuildingNameNo: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([Address],", ",0))[/li]
[li]StreetName: IIf(IsNull(SplitAddress([Address],", ",1)),"",SplitAddress([Address],", ",1))[/li]
[li]Locality: IIf(IsNull(SplitAddress([Address],", ",2)),"",SplitAddress([Address],", ",2))[/li]
[li]County: IIf(IsNull(SplitAddress([Address],", ",3)),"",SplitAddress([Address],", ",3))[/li]
[li]PostalCode: IIf(IsNull(SplitAddress([Address],", ",4)),"",SplitAddress([Address],", ",4))[/li]
[li]Comment: Replace(split_string([description],"%09",3),"%20"," ")[/li]
[li]timelogged[/li]
[/ul]

tblTmpImap
[ul]
[li]ogc_fid - Number[/li]
[li]RepresentorID - Number[/li]
[li]CommentID - Number[/li]
[li]ImDateCommRec - Date/Time[/li]
[li]ImSite - Text[/li]
[li]ImName - Text[/li]
[li]ImSurname - Text[/li]
[li]ImEmail- Text[/li]
[li]ImBuildingNameNo - Text[/li]
[li]ImStreetName - Text[/li]
[li]ImLocality - Text[/li]
[li]ImCounty - Text[/li]
[li]ImPostalCode - Text[/li]
[li]ImComment - Text[/li]
[/ul]

(Append Query - updates to tblTmpImap)
qryAppndTmpImapTbl
[ul]
[li]ogc_fid - updates to - ogc_fid[/li]
[li]Site - updates to - ImSite[/li]
[li]Name - updates to - ImName[/li]
[li]Surname - updates to - ImSurname[/li]
[li]Email - updates to - ImEmail[/li]
[li]BuildingNameNo - updates to - ImBuildingNameNo[/li]
[li]StreetName - updates to - ImStreetName[/li]
[li]Locality - updates to - ImLocality[/li]
[li]County - updates to - ImCounty[/li]
[li]PostalCode - updates to - ImPostalCode[/li]
[li]Comment - updates to - ImComment[/li]
[li]timelogged - updates to - ImDateCommRec[/li]
[/ul]

The above tables have the following relationships:

[ul]
[li]tblRepresentor has one to many comments (tblComments) - PrimaryKey RepresentorID in tblRepresentor links to RepresentorID in tblComments (one to many)[/li]

[li]tblRepresentor has many document links (tblDocumentLinks) - PrimaryKey RepresentorID in tblRepresentor links to RepresentorID in tblDocumentLinks (one to many)[/li]

[li]tblComments has one Officer Response (tblOfficerResponse) - PrimaryKey CommentID in tblRepresentor links to CommentID in tblOfficerResponse (one to one)[/li]
[/ul]


The problem is that the team did not include me originally on the project...so now I have to merge the above data somehow.

I though making an interim table I could control would help...
The append works.
But the next step is what I am a little fuzzy on..

How do I append that data to the table stucture (tblRepresentor, tblComments) letting the DB know which comment belongs to which representor? (that's why I built RepresentorID and CommentID into the interim table (tblTmpImap)

I would really appreciate any help

Thank you in advance
regards
Triacona



Thank you,

Kind regards

Triacona