Allocating serial numbers to orders

[Paul Watts]

I have a table of orders and a table of serial numbers of products that are available (on the shelf). I want the user to be able to allocate specific serial numbers to appropriate orders. How do I do this?

 

[DCrake]

How do you do it now?

 

[gemma-the-husky]

I have struggled to understand how serial numbers work in practice

it seems to me that the use of serial numbers is completely opposite to the idea of homogeneous stock.

lets say you have 20 widgets in stock, and you sell two of them. Now if the serial numbers of the widgets matter - then you dont have 20 of the same widgets - you have 20 different widgets, ad you need 20 different stock lines.

So it seems to me that distributors must distribute stuff without caring about the serial number actually supplied. They get to match the S/N to the order at the time that the warranty card is returned, or a service claim arises - and the s/n is not critical until that point.

On this basis - you shouldn't care what the S/N is - or if you do, then add it to the order line as a comment, and enter it manually at some point - eg when you pick the order, etc.

 

[smig]

Not sure I understand the question.
How do you search for a product by it's serial number ? it's the same as searching by the name or any other field.
simplest way is to use a combo having the serial number as the key, and showing both - serial number and description.

 

[Paul Watts]

Thanks for the responses so far. Please don't get hung up on the idea of using serial numbers - I simply want to allocate individual products to individual customers. This means the user has to view the customer order data at he same time as the product availability data (which is unrelated to the customer order data). The users then selects and allocates the product that best suits the customer based on the order and availability data. This is currently done by using paper records (with difficulty).

I don't need a detailed answer and I'm not into VBA. If I could see a customer form and an product form side-by-side, that would do it but I don't know how to achieve this.

 

[gemma-the-husky]

the thing is - why does the S/No matter at all.

If a customer wants to buy a part - then physically pick a part, inspect it, and enter the S/No somewhere at that point. Otherwise it doesn't matter surely - the last thing you want to do is do this the other way. allocate a S/No first - and then look thorugh all your stock trying to find the correct part.

In many cases the S/No may not be visible without opening a package.

That's why I think most distributors dont bother with S/Nos at point of sale. Larger systems will surely just record orders, and send picking notes through to despatch. The order clerks won't know anything about S/Nos - and the despatch clerks won't either.

What are you doing that makes it important to record the S/No at point of sale.

partial quote

I simply want to allocate individual products to individual customers.

this is the problem - what you want isn't simple, and seems unusual.

 

[Paul Watts]

Sorry guys. I wrongly thought that explaining my problem as allocating serial numbers would work. Clearly, I was wrong.
My client needs to allocate potato production batches (varying typically between 10 and 50 tonnes) to customer orders (of typically 50 to 300 tonnes). I have a table of batches (some of which are already allocated) and a table of orders (some of which are already satisfied).

 

[gemma-the-husky]

so if the batches are allocated, somewhere in the batch record - you must have a cross-reference to the customer order (or maybe the other way round)

so can you not just for a new order, show a list of the unmatched batches, pick the one you want, and record the active order number against the batch.

 

[thechazm]

Thats a lot of potatos

 

[Paul Watts]

Hi Dave.
Yes, that's what I need to do, in principle. I was trying to find a way of doing it on a single screen without having to write down order numbers or batch numbers by hand or having to flip back and forth checking that a number has been typed in correctly. I also wanted the database to add up the total batches allocated to an order so it was apparent whether any more batches were need to fill the order.

There's a lot of orders and a hell of a lots of potatoes.

P.S. The product is not really potatoes, it's atmospheric carbon dioxide savings - my client is into carbon trading. Let's stick to potatoes for now.

Paul

 

[gemma-the-husky]

have a form showing the orders
have a form showing the carbontrading offsets (or potatoes!)

select one in each form

have a button somewhere that updates the details in whichever way you want to go - eg - using the sort of code shown below

ie either
updates the carbon trading file with the order reference OR
updates the order file with the carbon trading file reference

this is OK, if everything is matched one-to-one. If the matches are more complex then you need a different arrangement with a junction table


the basic idea is to use a sql update statement - effectively

docmd.runsql "update onetable set somedetail = somevalue where recordid = someid"

 

[Paul Watts]

Nice one Dave. The problem is I'm not familiar with using SQL. Can you refer me to a simple guide?

 

[gemma-the-husky]

not really - i tend to write a visual query, then look at the SQL for it to see what it looks like (its one of the design options). Basically, each different type of query generates a slightly different SQL. Then these are further complicated by join types and totals etc. The Visual Query design simplified this process

So - you have to construct a text string with the correct syntax. If you construct a visual query to do this, (which needs to include references to your form variables) then you can execute this with code such as

docmd.openquery "myupdatequery" -

but if you have the same SQL as a string, then you can instead say

docmd.runsql "SQLStatement"

and you can do either of the above by the equivalent

currentdb.execute "SQLStatement" - although the error handling is slightly different in this latter case.


If you look at the SQL for the query, you will see it resembles the SQL I quoted, and this is not very complex SQL at all. Just examine the SQL for any queries you have - you will see how arcane the structure is - the syntax and bracketing is often tricky to get spot on - so it is easier to build visual queries where possible.

The only ones you can't construct visually are union queries.

 

[Paul Watts]

Mmmmm.
Dave.
By "Visual Query" do you just mean a query. I see that there's an option to look at the SQL of an ordinary query in Access 2007.

 

[Paul Watts]

... or can I use the macro or code generators available to the button (through button properties > event) to perform this action (told you I was a novice)?

 

[gemma-the-husky]

What I meant was that if you construct a visual query, by pulling in tables and fields .... what Access acually does is turns this into SQL, and then uses the SQL but it insulates you form having to enter the SQL directly

So you have 2 choices - either use the drag and drop approach, or type in the SQL directly

Now, if you have a look at the SQL, using the design option, you will see very quickly

Drag and Drop = easy
Write SQL = hard

but for some simpler things, "write SQL" is easy enough to use in practice. And for union queries, you HAVE to use SQL, as you can't construct them with drag and drop.

And some users are good enough with SQL that they don't have problems writing it straight off.

Having constructed a query, you can then either select it and use it directly - which is not recommended, as you want to have a clean interface that again insulates users (and yourself) from having to remember the details of what every query does ....

so instead you can "call" the query either with a macro, or in code, and attach the macro or code to a button.

You can then give advisory messages to the user, confirm they do want to undertake what ever action they are contemplating - importantly validate any settings that might affect what they are doing, and thereby stop them doing it in some cases ......

Macros are OK, but code is more flexible. Again, anything you can do in a macro, you can do in code, (with a couple of minor exceptions) - but not the other way round. Once you start getting more advanced you will see that code is far more useful and flexible than macros.