Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    99

    Unanswered: Linked in Table problems using MID function

    I am trying to create a relationship between two tables.

    One table is a Linked table from an Outlook folder and the other is a standard table I have built up.

    The linked in table from Outlook has emails that all start with the subject as such:-

    Page "*PEN 0009000555" has changed: 236 changed words (http://newsvote.bbc.co.uk/1/shared/f...et/default.stm) - (as the Subject field)

    The standard table has entries with the reference *PEN 0009000555

    Now what I want to do is link the two tables together and build a relationship and query so that the email subject in the linked in table relate to the respective entries in the standard table....

    I guessed creating 1) the relationship 2) the query that used the MID function as such MID([Subject] , 6, 15) would do the trick and create the relationship but no joy.

    I was hoping that the MID function would ignore everything before the 6 (ie Page ") and everything after 15 (ie " has changed etc)

    Any pointers? thanks all
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    MID Problem

    Using Page "*PEN 0009000555" Is there a space between Page and "*
    If there is a space then it should be mid([subject],7,15). otherwise
    mid([subject],6,15) will work.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I assume that "the query that used the MID function" is a Select query, and that you have successfully run it while in Design view. What do the results look like? Does running the query return any records? Do they have the information you want?

    Sam

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Link problem using MID

    Sam

    See attached sample.

    Tables
    tbl_email_ref is your own table
    tbl_emails is the linked table to your email folder.

    Queries
    qry_mid obtains the string *PEN 0009000555
    qry_linked links qry_mid to tbl_emails giving the records that match. Note, I have one extra record in tbl_emails that will not appear in qry_linked as *PEN is not listed in tbl_email_ref.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Posts
    99
    Quote Originally Posted by Sam Landy
    I assume that "the query that used the MID function" is a Select query, and that you have successfully run it while in Design view. What do the results look like? Does running the query return any records? Do they have the information you want?

    Sam
    Thanks Sam. Haven't had any results with the MID function. In monosyllabic words (my techie skills are limited!) what is a Select query as opposed to a normal query?
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  6. #6
    Join Date
    Feb 2004
    Posts
    99
    Quote Originally Posted by Poppa Smurf
    Sam

    See attached sample.

    Tables
    tbl_email_ref is your own table
    tbl_emails is the linked table to your email folder.

    Queries
    qry_mid obtains the string *PEN 0009000555
    qry_linked links qry_mid to tbl_emails giving the records that match. Note, I have one extra record in tbl_emails that will not appear in qry_linked as *PEN is not listed in tbl_email_ref.
    Thanks Poppa Smurf, the alternatives of 6,15 or 7,15 didn't work so I must be doing something fundamentally wrong. Logically it should work and I have limited techie skills so can't work out the issues!!

    The database you sent would not open, could you please send again as this will probably hold the answers for me?

    I've attached the database I am trying to get to work to see if you can make any sense of it.

    The logic of the whole process is to see how many times a website changes and keep a record of those changes by linking in the file. Eventually if I can get the first stage cracked a report could be generated to show a running total of how many changes occurred per URL but one stage at a time.

    Thanks for your help
    Attached Files Attached Files
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    MID Function

    Please resend your database BUT do not include the linked table to your outlook as the database was looking for the source data from your outlook file.. Could you please have a table with the same data that is in your linked table? Or send send the source data and I will link it to your database.

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    MID Function

    Here is another copy.
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2004
    Posts
    99
    Quote Originally Posted by Poppa Smurf
    Please resend your database BUT do not include the linked table to your outlook as the database was looking for the source data from your outlook file.. Could you please have a table with the same data that is in your linked table? Or send send the source data and I will link it to your database.
    Thanks Poppa Surf. Have attached new mdb with the two tables I want to link. The table TrackChangesNotLinked has the data but is not linked.

    The query wants to link the PenEntries (PEN field) and TrackChangesNotLinked (Subject field) by using the MID or other function as this thread has described.

    Thanks
    Attached Files Attached Files
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    MID function

    In the attached database

    qry_mid_function is a query used to extract the string *PEN 0009000555 using the MID function it is a new field called email_subject.

    qry_result is a query that links the table PENEntries with the query qry_mid_function using the PEN field in table PENEntries and email_subject field in qry_mid_function.

    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Posts
    99
    Quote Originally Posted by Poppa Smurf
    In the attached database

    qry_mid_function is a query used to extract the string *PEN 0009000555 using the MID function it is a new field called email_subject.

    qry_result is a query that links the table PENEntries with the query qry_mid_function using the PEN field in table PENEntries and email_subject field in qry_mid_function.

    Thanks Poppa Smurf, that works well and gives me something to get my teeth into to try and figure out some other things. If i could buy you a beer I would!

    Next thing I need to figure out is to make things a bit more unique, again in non-techie terms would only want one entry for *PEN 0009000555 with all the changes that occurred to *PEN 0009000555. Also some function that would count the number of times *PEN 0009000555 received an alert of change. If you have any thoughts much appreciated.

    Thanks again
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Count the number of entries

    Next you have a beer have one for me.

    would only want one entry for *PEN 0009000555 with all the changes that occurred to *PEN 0009000555
    In your table PENEntries set PEN as the primary key.

    count the number of times *PEN 0009000555 received an alert of change
    Have look at the new query qry_count. This uses the TOTALS which can be found by using the design view of the query, then select View on the top menu then Totals from the drop down menu.

    In the query the Group by groups all the entries for each PEN and then we use the COUNT from the drop down in the total row under the second column to count the entries for each PEN.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •