Appending a single table into 2 separate tables
Appending a single table into 2 separate tables
(OP)
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
tblOfficersResponse
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
qryAppndTmpImapTbl
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
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
- RepresentorID - AutoNumber
- RepTitle - Text
- RepName - Text
- RepSurname - Text
- BuildingNameNo - Text
- StreetName - Text
- Locality - Text
- County - Text
- PostalCode - Text
- Email - Text
- Telephone - Text
- ContactType - Text
- CompanyOrganisation - Text
- Representing - Text
- DateAdded - Date/Time
- DocLinkID - Autonumber
- RepresentorID - Number
- DocumentLink - Hyperlink
- WhichConsultation - WhichConsultation - Text
- CommentID - AutoNumber
- RepresentorID - Number
- Document - Text
- Section - Text
- Comment - Memo
- SummaryOfComment - Memo
- ProposalModifications - Memo
- CommentType - Text
- HowReceived - Text
- DateCommRec - Date/Time
- DateCommAck - Date/Time
- NotifySubExam - Yes/No
- ExamParticipation - Yes/No
- LegalyComp - Yes/No
- Sound - Yes/No
- ChangesReq - Yes/No
- AprroveReject - Yes/No
- Checked - Yes/No
- Easting - Number
- Northing - Number
tblOfficersResponse
- CommentID - Number
- OffRespId - Autonumber
- Officer - Text
- OfficerResponse - Text
- CouncilResponse - Text
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
- ogc_fid - Number
- uniqueid - Text
- type - Text
- assetid - Text
- description - Memo
- interaction - Memo
- timeLogged - Date/Time
- active - Number
- easting - Number
- northing - Number
- wkb_geometry - Number
- ogc_fid
- Site: assetid
- NameSurname: Replace(split_string([description],"%09",0),"%20"," ")
- Name: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([NameSurname]," ",0))
- Surname: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([NameSurname]," ",1))
- Email: split_string([description],"%09",1)
- Address: Replace(split_string([description],"%09",2),"%20"," ")
- active
- easting
- northing
- BuildingNameNo: IIf(IsNull(SplitAddress([Address],", ",0)),"",SplitAddress([Address],", ",0))
- StreetName: IIf(IsNull(SplitAddress([Address],", ",1)),"",SplitAddress([Address],", ",1))
- Locality: IIf(IsNull(SplitAddress([Address],", ",2)),"",SplitAddress([Address],", ",2))
- County: IIf(IsNull(SplitAddress([Address],", ",3)),"",SplitAddress([Address],", ",3))
- PostalCode: IIf(IsNull(SplitAddress([Address],", ",4)),"",SplitAddress([Address],", ",4))
- Comment: Replace(split_string([description],"%09",3),"%20"," ")
- timelogged
- ogc_fid - Number
- RepresentorID - Number
- CommentID - Number
- ImDateCommRec - Date/Time
- ImSite - Text
- ImName - Text
- ImSurname - Text
- ImEmail- Text
- ImBuildingNameNo - Text
- ImStreetName - Text
- ImLocality - Text
- ImCounty - Text
- ImPostalCode - Text
- ImComment - Text
qryAppndTmpImapTbl
- ogc_fid - updates to - ogc_fid
- Site - updates to - ImSite
- Name - updates to - ImName
- Surname - updates to - ImSurname
- Email - updates to - ImEmail
- BuildingNameNo - updates to - ImBuildingNameNo
- StreetName - updates to - ImStreetName
- Locality - updates to - ImLocality
- County - updates to - ImCounty
- PostalCode - updates to - ImPostalCode
- Comment - updates to - ImComment
- timelogged - updates to - ImDateCommRec
- tblRepresentor has one to many comments (tblComments) - PrimaryKey RepresentorID in tblRepresentor links to RepresentorID in tblComments (one to many)
- tblRepresentor has many document links (tblDocumentLinks) - PrimaryKey RepresentorID in tblRepresentor links to RepresentorID in tblDocumentLinks (one to many)
- tblComments has one Officer Response (tblOfficerResponse) - PrimaryKey CommentID in tblRepresentor links to CommentID in tblOfficerResponse (one to one)
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
RE: Appending a single table into 2 separate tables
Thank you,
Kind regards
Triacona
RE: Appending a single table into 2 separate tables
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:
CODE
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.
RE: Appending a single table into 2 separate tables
I will try and look into this as soon as I can
Thank you,
Kind regards
Triacona