Assigning a store to several products (...)

[opopanax666]

Hi everyone,

Difficult to find an appropriate title for this one Here it goes:

I have a table containing all our produced goods. Since we are a recycling business, no two products are the same, so they all have a unique code, included in the "Produced" table.
I have a table "Stores", which contains details of our 5 stores.
When the stockroom sends one of our stores several products (codes), I want administration to be able to input all these codes in a form, that then assigns de store_id to these codes in the table "Produced", so we can find out what has been sent to what store...
But I have no clue where to start I would like administration to be able to select a store, then add all the codes (with a listbox bound to the "Produced"-table?), and when this is done, press a button to write the store_id to a "Store"-field next to the code in the "Produced"-table?
I put question marks because I don't know what is common practice in these cases, or whether this is the logical way to go...

Could someone here point me in the right direction? Would be much appreciated!

 

[ted.martin]

Its all about deciding upon a series of logical steps. Compare your process to a simple Sales Ordering scenario where Customers = Stores, Orders = Store Deliveries and Order Details = Products Shipped.

There will be a template within your Access programme that will guide you through this. Download a few and take a look; thats how I learned initially.

At this stage it is a little difficult to be more precise but if you take a look at how the above Order template works, it should give you some pointers as to how to construct yours.

Hope this helps.

 

[opopanax666]

There will be a template within your Access programme that will guide you through this. Download a few and take a look; thats how I learned initially.

Thanks for the quick reply. The problem with templates (even Northwind) are that they are based on normal procedures in shops/stockrooms, and thus are based on "amounts" of a certain product. If that were the case in our company, I could look at it as they do in accounting, and create a transaction table (that much of a product produced, that much of a product shipped, difference = stock).
But in our company we don't work with "amounts" of a product, since no two tables (e.g.) are the same (and also we need to know the weight of each of our products for different reasons), so we need to code all our products (as you would in a normal business), but effectively attribute the amount "1" to each of them...
In short, I cannot use a "shipping details" table, the shipping details need to be directly connected to the product (code)...
See my problem?

 

[ted.martin]

Can't see how you will do what you want without a 'transaction' table of some sort. All I was suggesting, is to use the Northwind as the basis for thought and then adapt it.

It is always difficult to understand exactly what people require from their 'process' questions. As I said, its about logical steps and that you will have to think through yourself. You may need to create some sort of table to hold the Codes and the StoreID. Getting the data into this would probably require some kind of APPEND query.

 

[Simon_MT]

This is quite an interesting scenario. Firstly a curiosity question why do the 'Products' more around and what happens to them at each store?

Your problem will not be solved by Northwind rather a bill of material solution. There are two choices, one for have a Products table with every known combination which is not impossible although a little unweildy. Or a flexible bill of materials that allows combinations made up on the fly. The other difference between a chemical compound and recyclying a chemical compound can't be undone.

So what you you are really doing is moving an object with a variable composition of recycling material. I don't know whether or the individual components can be reconsitituted or what happens to them.

Simon

 

[spikepl]

If I understand your problem correctly, each product is unique, so your data is probably structured like this:

Products
--------
ProductID (PK)
ProductDescription
ProductWeight
StoreID (FK)
AssignmentDate (a suggestion)


Stores
------
StoreID (PK)
StoreName
OtherStoreData

You could in a form have a listbox AvailableProducts based on a query which selects all products not yet assigned to a store (i.e. with StoreID Null)

You could have a combobox, based on the table Stores, for selecting one store from the table Stores.

You could have a second listbox AssignedProducts, based on a query showing products assigned to the selected store with todays date, i.e. with StoreID= ID of the selected store, and AssigmentDate= today's date. It'll start off empty

The assignment could be handled by clicking on a product in AvailableProducts. In the table Products, you would write today's date and the ID of the selected store, in the record for the clicked product. Then on each click requery both listboxes, and the AvailableProducts now has one less product showing, while AssignedProducts shows one more product for the selected store.