Need to assign data into a table automatically

  • Thread starter Thread starter vestax2000
  • Start date Start date
V

vestax2000

Hello All

I am currently trying to create a database with the following
requirements but am finding it to be quite a nightmare............

Layout:

three tables ( one named product table, and the second named location
table, and the Storage Type table)

Product table consits of usual product information

Product = text field (example = G21564)
Description = text field
QTY = number field
Storage Type = text field

Location consits of the following

Location Name = text field (example = ACE105)
Storage Type = text field

Storage Type table consists of the following

Storage Type ID = Autonumber
Storage Type = text field (example = Wide Aisle)

My problem:

I am trying to create a database that will automatically assign me the
right product to the right location using the storage type field as a
lookup value, this value will seperate my large products from my small
products and place them into the correct aisle, I will be needing this
for approximately 5000 products to be added over a couple of months
timeframe, so when I enter say 100 products into the product table I
can run a query that will drop the new info from the product table
into another table without overwriting the info that is already there
but also grabbing the correct info out of the location table along the
way..........

Can this be done by an update query and a where clause???

Any help would be greatly appreciated as this is driving me wild at
the moment...

Thanks in advance for anyone who can help.
 
Well, you need to include a flag in the Location record which is set to YES
once the location is used. That location is then ignored when new locations
are qualified as potential locations for new products.

I'd code this up in VBA behind a form. That way, EVERY TIME you go to enter
a new product, it IMMEDIATELY assigns a location to it.

The trick (for those not well versed in database design) is to write down on
a sheet of paper, what steps nned to be followed and in what order, to
accomplish the task. It's far easier doing that in human language first, then
convert those "specs" into a routine in the application.

Seems pretty straight-forward...
 
Back
Top