INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Appending a single table into 2 separate tables

Appending a single table into 2 separate tables

(OP)
Dear All,

Thanks for a great forum!! smile

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
tblDocumentLinks
  • DocLinkID - Autonumber
  • RepresentorID - Number
  • DocumentLink - Hyperlink
  • WhichConsultation - WhichConsultation - Text
tblComments
  • 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 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
  • 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
qryIMap
  • 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
tblTmpImap
  • 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
(Append Query - updates to tblTmpImap)
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
The above tables have the following relationships:

  • 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

(OP)
Any help please sadeyes

Thank you,

Kind regards

Triacona

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close