none
Appending field values to a new table or query Access RRS feed

  • Question

  • Hi Everyone,

    I am trying to gather data from certain fields in two different tables and put that data into a different table or query. The first table has a field with a unique number that matches a like field with the same unique number in the second table.

    I need to pull data from fields in the tables based off of the unique number of the record and put/organize that data into a new table or query.


    So - if unique number is 14, then copy the data in fields X,Y, and Z of record 14 in one table, and data P, D, and Q of record 14 in the other table and put that data into a new table or query. XYZ and PDQ are not like fields and contain different data.

    Then, I need to export that table or query into Excel. I am trying to completely automate this if possible. Perhaps VBA is in order, but I really don't know VBA much.

    The first table is a linked table to a SharePoint site and autopopulates the table when changes, additions or deletions are made in either SharePoint or Access.

    Thanks for any help.


    Tuesday, April 3, 2018 8:03 PM

Answers

  • Okay, just to be sure, could you please go to the List Settings in SharePoint and check what is the Column Type for Device Number? Does it say Text or Number?
    • Marked as answer by cruiserbill Wednesday, April 4, 2018 4:42 PM
    Wednesday, April 4, 2018 3:26 PM

All replies

  • Hi,

    Maybe a query like so?

    SELECT T1.X, T1.Y, T1.Z, T2.P, T2.D, T2.Q
    FROM Table1 AS T1
    INNER JOIN Table2 AS T2
    ON T1.UniqueNumberFieldName=T.UniqueNumberFieldName

    If it works, you can right-click on the query from the Nav Pane and then select Export > Excel.

    Hope it helps...

    Tuesday, April 3, 2018 8:20 PM
  • I guess I should add that I do not want all of the records from the tables. If the first table's "Processed" field is not checked (=No), then that record needs to be included along with the data from the second table pertaining only to the unprocessed records from the first table.

    Thanks for the response DBguy. That does give me something to go on. I don't understand the last "ON" statement, but I don't know SQL well either. 

    Tuesday, April 3, 2018 8:53 PM
  • Hi,

    The ON clause is how you link two tables together using a common field. What did you get when you tried it?

    If you wanted "all" records from one table, then you'll need an OUTER JOIN (instead of an INNER JOIN). For example:

    SELECT T1.X, T1.Y, T1.Z, T2.P, T2.D, T2.Q
    FROM Table1 AS T1
    RIGHT JOIN Table2 AS T2
    ON T1.FieldName=T2.FieldName
    WHERE T1.Processed = False

    Hope it helps...

    Tuesday, April 3, 2018 9:00 PM
  • I keep getting "Type mismatch in expression"

    Below is a redacted version of my SQL code:

    SELECT T1.DeviceType, T1.DeviceMake, T1.DeviceModel, T1.DisplayName, T1.InstancePath, T1.DeviceSerialNumber, T2.[PC Name], T2.[MAC Address], T2.User, T2.[Email Address],
    FROM Table1 AS T1 
    INNER JOIN [Table 2] AS T2 
    ON T1.DeviceNumber = T2.[Device Number]
    WHERE T2.Processed = False;

    Wednesday, April 4, 2018 12:30 PM
  • Hi,

    What are the data types of T1.DeviceNumber and T2.[Device Number]?

    Wednesday, April 4, 2018 2:34 PM
  • They are numbers, such as 652 or 1652.
    Wednesday, April 4, 2018 2:42 PM
  • Oh, and what about T2.Processed? If it's Text or Short Text then what value is stored in it? If something like a "Yes" or a "No," then modify the last part of the SQL as such:

    WHERE T2.Processed="No"

    Hope it helps...

    Wednesday, April 4, 2018 2:50 PM
  • T2.Processed is a check box. I changed it to "No" and it still gave me the Type mismatch in expression error.
    Wednesday, April 4, 2018 2:55 PM
  • If T2.Processed is a check box, meaning a Yes/No data type, then T2.Processed=False was correct.

    Would any of the two tables happen to be a linked Excel spreadsheet?

    Wednesday, April 4, 2018 3:17 PM
  • No, none of the tables are linked to anthing else with the exception of the T2 table that is linked to SharePoint.
    • Edited by cruiserbill Wednesday, April 4, 2018 3:22 PM
    Wednesday, April 4, 2018 3:22 PM
  • Okay, just to be sure, could you please go to the List Settings in SharePoint and check what is the Column Type for Device Number? Does it say Text or Number?
    • Marked as answer by cruiserbill Wednesday, April 4, 2018 4:42 PM
    Wednesday, April 4, 2018 3:26 PM
  • Excellent find! The Sharepoint list is set as a number and the Access field is set as Short Text. There is a relationship between the two tables for that field. I deleted the relationship, changed the field to Number, readded the relationship, and now it works! A few more tweaks and it will be perfect.

    Thanks alot!

    Wednesday, April 4, 2018 4:25 PM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, April 4, 2018 4:51 PM