如何实现查询多个表里包括的相同字段

我有多个表，表里都有PartNumber、Quantity字段，但Quantity字段内容不同，另外还有一个Location字段，这三个字段同时存在在表Inventory里。现在我想通过在文本框中输入某一个零件号，点击按钮，并通过DataGrid1控件显示Inventory表中的这三个字段的内容。请大家帮忙这该如何写呢，我写了代码，但有错误，不知道应该如何写

Dim filter As String
Private Sub Command1_Click()
Dim strQuery As String
strQuery = "select * from [Inventory] where [PartNumber] = 零件号 and [Quantity] =库存数量 and [Location]=存放位置 WHERE Inventory.PartNumber='" & Text1.Text & "'"
Adodc1.RecordSource = strQuery
Adodc1.Refresh
End Sub



Private Sub DataGrid1_Click()

End Sub

Private Sub Form_Load()
Dim strQuery As String
Adodc1.ConnectionString = conn.ConnectionString
strQuery = "SELECT Inventory.PartNumber AS 零件号,Inventory.Quantity AS 库存数量, Inventory.Location AS 存放位置"
Adodc1.RecordSource = strQuery
Adodc1.Refresh
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then Command1_Click
End Sub

strQuery = "select * from [Inventory] where [PartNumber] = '"& 零件号 &"' and [Quantity] ="& 库存数量 &" and [Location]='"& 存放位置 &"' WHERE Inventory.PartNumber='" & Text1.Text & "'"