Appending a single table into 2 separate tables 1

[Triacona]

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

 

[Triacona]

Any help please

Thank you,

Kind regards

Triacona

 

[Darrylles]

Bit late, but I hate loose ends.

Not sure if I'm understanding this clearly, but...

Original data: each comment must have the accompanying representor name right?

First populate tblRepresentor with all representor details (this will create unique representorID's).
Next, you need an 'interim' table (tblInterim ?) that stores all of the original data, but with an additional blank field: representor_fk.
Now, populate (update) those 'representor_fk' fields (in tblInterim) with the 'representorID' values matched on RepName. eg:

UPDATE    tblInterim AS I 
LEFT JOIN tblRepresentor AS R 
ON        I.repname = R.repname 
SET       I.representor_fk = R.representorID;

Now, you have valid Representor_fk's alongside each comment.

So, populate (append) all tblComments fields (from tblInterim) also adding the new RepresentorID values that are present alongside the comments (in tblInterim).

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Triacona]

Thanks so much for your help
I will try and look into this as soon as I can

Thank you,

Kind regards

Triacona