Append query, multiple tables (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 10:16
Joined
Jul 9, 2015
Messages
424
I am wondering if I can take data from table Sheet1 and append it to table ItemLocations, using the MasterNumber as a flag/link for the records.

BinText is simply the textual location identifier, ie: WH1 A A1 (warehouse, isle, section, shelf)

I imported and manipulated the data from Excel to create Sheet1, and would rather not enter each record manually, as there are about 3000. I already had to do that for vendor part numbers - because the old system uses the Master number as a PK of sorts, but that does not work with this system.

Thanks.

See attached picture for tables.
 

Attachments

  • ItemLocAppend.PNG
    ItemLocAppend.PNG
    5.1 KB · Views: 151

mjdemaris

Working on it...
Local time
Today, 10:16
Joined
Jul 9, 2015
Messages
424
Here is the query I used to solve this:
Code:
INSERT INTO ItemLocations ( ItemID_FK, BinID_FK, WinterLevel, SummerLevel )
SELECT Items.ItemID, [Q-BinCmb].BinID, Sheet1.[Winter Stock Level], Sheet1.[Summer Stock Level]
FROM Items INNER JOIN (Sheet1 INNER JOIN [Q-BinCmb] ON Sheet1.Bin = [Q-BinCmb].Location) ON Items.MasterNum = Sheet1.[Master Part Number]
WHERE (([Items].[MasterNum]=[Sheet1].[Master Part Number]) AND ([Sheet1].[Bin]=[Q-BinCmb].[Location]) AND (Not (Sheet1.Bin) Is Null));
 

Users who are viewing this thread

Top Bottom