Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2015
    Posts
    6

    Unanswered: Merging data from 2 different tables using reference field

    Hi all,

    I'm a bit of an Access newbie, so my apologies if this is a really simple question with an even simpler answer!

    I am trying to add data from one table (let's call it New_Table) to the central table I have been building for my database (Central_Table)

    New_Table fields: Student_ID (primary key), Results

    Central_Table fields: Student_ID (primary key), Results(currently empty) , ....

    The issue is that Central Table contains more records than New_Table, as it is for a larger sample of students. I'm therefore trying to populate the field 'Results' in Central_Table with the data from 'Results' in New_Table, using the Student_ID to ensure that records match. This doesn't need to be a live link - just a one-off copy job. In Excel I would have used a VLOOKUP for this to ensure referential integrity, but I've been scouring the net and can't find a simple answer as to how to get this done - I'm sure it can't be that unusual or difficult a requirement!

    I've tried simply copy-pasting, but obviously the records don't match up so that's no good. I'm going to guess it has something to do with a DLOOKUP, but I don't know how to use this to populate the table - I know this is usually used to populate forms...

    I'm using Access 2010.

    Many thanks in advance for any help with this!

    Simon

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create an update query
    providing you have some unique piece of information in BOTH tablesw you can use the value from one table to update the value of the permanent table

    when developing your query first off use it as a select, till you are happy the results are correct. eg:-
    Code:
    select m.student_id, m.course_id, t.results from central_table as M
    left join new_table as t on (m.student_id = t.student_id AND  m.course_id =  t.course_id)
    ive aliased the tables to make it shorter and I hoip easier to read and or understand
    central_table aliased as M (as in M for Master)
    new_table aliased as T (as in T for Termporary)
    ..please tell me those aren't you real table names, no please do....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2015
    Posts
    6
    Thanks for the quick reply!

    So I put in the query as:

    SELECT M.student_id, t.French
    FROM French_Data AS M
    LEFT JOIN sheet1 AS t
    ON M.student_id = t.student_id;

    (with appropriate changes!)

    This creates a query table with the correct values, which I've sorted and copied into the original table - all good!

    Now let me ask you the follow-up question, oh wise one! How do I convert the syntax of this into the form of an Update Query, so that it will update the Central_Table (oh fine, French_Data!) without me having to copy-paste?

    Thanks again,

    Simon

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well first oiff I'd read the help for using an update query


    offhand its going to be something like

    update mytable set mycolumn = t.results
    left join mytable on ON M.student_id = t.student_id

    ..but you'd need to check on the precise syntax for doing an update query on a joined table in MS Access
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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