×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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.

Students Click Here

Appending a single table into 2 separate tables

thread700-1750827

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 smile

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

RE: Appending a single table into 2 separate tables

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:

CODE

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.

RE: Appending a single table into 2 separate tables

(OP)
Thanks so much for your help bigsmile
I will try and look into this as soon as I can thumbsup

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! Already a Member? Login
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