如果数字匹配，需要 SQL 使用另一个表中的数据更新表 [英] Need SQL to update table with data from another table IF numbers match

问题描述

我在下面有这个功能，让用户选择excel文件并将数据导入表(MyTable).它只是一个单列excel文件.它导入的表包含 2 列(F1，F2).

I have this function below that is letting user choose excel file and it imports the data into a table(MyTable). Its just a single column excel file. The table it imports into contains 2 columns(F1,F2).

我需要 DoCmd.RunSQL 命令来将以下信息输入到第二列中.

I need the DoCmd.RunSQL command to get the following info into that 2nd column.

MyTable.F1 是 OEM 部件号我需要将这个数字与我设置的 (JDSubs) 表中的 2 列 (OEMPartNumer, OEMSub) 进行比较如果找到匹配项，我需要它比较 (JDSubs) 表中的这 2 个匹配项，并尝试在列 (OEMItem) 的 (AMI) 表中找到它如果找到匹配项，我需要从表 (AMI) 的列 (Item) 返回值并将其插入 (MyTable) 列 (F2)

MyTable.F1 is the OEM part number I need to take that number and compare it to 2 columns (OEMPartNumer, OEMSub) in a (JDSubs) table i have setup If it finds a match i need it to compare those 2 matches from (JDSubs) table and try to find it in (AMI) table in column (OEMItem) If it finds that match i need to return value from column (Item) from table (AMI) and insert it into (MyTable) column (F2)

表格内容示例

MyTable
----------------
F1       | F2
AR77530  | 
AR12345  |

JDSubs
---------------------------
OEMPartNumer    | OEMSub
AR65123         | AR77530
AR12345         | AR56242

AMI
---------------------------
Item            | OEMItem
AMAR77530       | AR77530
AMAR56242       | AR12345

所以从 excel 文件中导入的数字可能是 2 个数字之一(有时没有子数字)

So the number being imported from the excel file could be one of 2 numbers(sometimes there is no sub number)

我只需要将我公司的零件编号 (AMI) 与 OEM 编号相匹配

I just need to match up my companies part number (AMI) to the OEM number

这是我将工作表导入 MyTable 的功能我只需要用匹配的 AMI 编号填充 F2 列并导出

Here is the function i am importing the worksheet with into MyTable I just need to get F2 column filled with matching AMI numbers and export back out

 Sub Import()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim CustomerFile As String
    Dim LUser As String

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    .AllowMultiSelect = False
    .Title = "Please select your OEM part number file."
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xlsx"
    .Filters.Add "Excel Spreadsheets", "*.xls"
    .InitialFileName = "C:\Users\" & LUser & "\Desktop"
    If .Show = True Then
          'Loop through each file selected and add it to the list box.
          For Each varFile In .SelectedItems
             CustomerFile = varFile
          Next
    End If
    End With
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", CustomerFile, False, "sheet1!A:A"
    DoCmd.RunSQL ?????


    Exit Sub

End Sub

此外，我有一个表单设置，因此用户可以一次查找一个数字.这是它的功能.我只需要一个自动化的流程

Also, I have a form setup so the user can look up one number at a time. Here is the function for it. I just need to have an automated process for it

Function fnSearchAndPopulate() As Boolean
Dim d As DAO.Database, r As DAO.Recordset, strSQL As String
Set d = CurrentDb
If Me.txtEnterNumber.Value = "" Then
    MsgBox "Please Enter Number", , "Error"
    Exit Function
End If
strSQL = " SELECT * FROM JDSubs Inner Join AMI on " & _
         " AMI.OEMItem=JDSubs.OEMPartNumber WHERE " & _
         " JDSubs.OEMPartNumber= '" & txtEnterNumber.Value & "' or " & _
         " JDSubs.OEMSub= '" & txtEnterNumber.Value & "
Debug.Print strSQL
Set r = d.OpenRecordset(strSQL)
If r.EOF Then
   MsgBox "OEM # " & Me.txtEnterNumber & " does not exist!", , "No AMI #"
  Set d = Nothing
  Exit Function
End If
'get here if there is a record
r.MoveFirst
'populate whatever textboxes
Me.txtAMINumber = r!Item
Me.txtDescription = r!Description
Me.txtOEMsubnumber = r!OEMSub

Set d = Nothing
Exit Function
End Function

推荐答案

这可以通过 2 个具有不同联接的更新查询来解决.另外，我建议使用 Currentdb.Execute 而不是 DoCmd.Runsql 因为 Execute 不会抛出任何警告弹出框:

This can be solved with 2 update queries with different joins. Also, I recommend using Currentdb.Execute instead of DoCmd.Runsql because Execute doesn't throw any warning pop up boxes:

Currentdb.Execute 
    "UPDATE 
    (MyTable INNER JOIN JDSubs ON MyTable.F1 = JDSubs.OEMPartNumber) 
    INNER JOIN AMI ON JDSubs.OEMPartNumber = AMI.OEMItem 
    SET MyTable.F2 = [AMI].[Item];"

Currentdb.Execute
    "UPDATE 
    (MyTable INNER JOIN JDSubs ON MyTable.F1 = JDSubs.OEMSub) 
    INNER JOIN AMI ON JDSubs.OEMSub = AMI.OEMItem
    SET MyTable.F2 = [AMI].[Item];"

这行得通吗?

这篇关于如果数字匹配，需要 SQL 使用另一个表中的数据更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！