Update Tables

[SGTPEP]

Currently i import 2 excel files into my database, at different times.

I am looking to update one of the tables depending on the information on the
other. i.e. The first table is stock gained and the second table is the stock
sold.

I wold therefore want to be able to update the stock table to say that the
item has been sold.


<Beginner>

 

[Jeff Boyce]

It sounds like you are using the imported Excel data directly, rather than
using queries to parse the (raw) import data into more permanent
(?well-normalized?) Access tables. If this is your situation, you will not
get as many benefits from Access' relationally-oriented features/functions.

For instance, in Access, you could consider using a SINGLE table
(transactions), into which both sold and received stock units could be
recorded.

Then you'd only need to sum up the transactions for StockItem=93 to see
stock-on-hand.

Or maybe I'm not following your situation well...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SGTPEP]

Hi,

I have resolved the problem using querys, thanks.

The reason for not using one table is that a employee may list the stock but
it may not get sold for months, therefore the listing and the sale could be
months apart, and therefore not on the same monthly report!

We operate like a franchise system so i dont actually do any buying or
selling, i collect the royalties from the 'employees'

 

[Jeff Boyce]

Whether or not a specific stock item showed up in one month or another, a
single transaction table would receive the updates from BOTH Excel feeds,
each month. That single transaction table would provide a cumulative
history, rather than having to search multiple "months" data.

Regards

Jeff Boyce
Microsoft Office/Access MVP