Inventory in Excel, reporting in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I keep track of inventory of about 100 items. Every month I do inventory.
Currently I have an Excel sheet that has these columns: Item #, Name, On Hand
That sheet currently has a lot of formulas to track how many moved, what the
average is and suggest when to order and how many (based on average and on
hand). I'm trying to migrate all the calculations into a Access program I
already have for tracking orders, but keep plugging the numbers into a Excel
sheet (I do inventory onto my PPC - WM2003, but others are using WM5 which
doesn't sync Access files...).
Two questions - how do I compose my excel sheet so that Access realizes what
is new data? The most obvious in excel is to simply have the first 2 column
be the Item # and then the columns to the right be the successive months
inventory. But how will Access deal with the data being in that format?
Am I making ANY sense at all?
 
Try linking the Excel spreadsheet directly in Access then run a query which
selects all the records not in the Access table and inserts them into the
Access table. Something like this:

INSERT INTO tblAccess
SELECT XLSheet.*
FROM tblAccess RIGHT JOIN XLSheet ON tblAccess.[Item#] = XLSheet.[Item#]
WHERE (((tblAccess.[Item#]) Is Null));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top