Serial Number Search query

[G1ZmO]

Hope someone can help

I have a small test database which I'm doing for our dispatch dept.

It contains: Order No. / Customer / Ship_Date / Serial numbers (multiple)

I'm looking for a way to create a search query that will search though all the records to find a particular serial number.

There fields in the table are:
Shipping_Date
Customer
Order_No
Pallet_ID
Serial_1
Serial_2
Serial_3
Serial_4
etc

I need it to come back with the order number and pallet that it was shipped on.

Any ideas?

Thanks,

Paul

 

[David Eagar]

Should not your Serial Numbers be in a one-to-many table, linked to the Order table? Then your query is very much easier

 

[RuralGuy]

You can now see why having separate records for each serial number (normalized) would make this task trivial.

 

[G1ZmO]

Yes, I do see that. However, our order system is not on a database. Nor is the stock system (excel sheets). Mad or what?

I don't have the db skills to build a stock/orders system from scratch and the company wont pay for one. (Although they probably lose money in terms of lack of efficiency lol)

 

[David Eagar]

You do make it tough, don't you?

 

[gemma-the-husky]

just a bit of background

why would there be multiple serial numbers in each row of the table? do these represent serial numbers of items included in the order? does this mean you have description columns in this table as well? or are you just recording serial numbers?

having said all that, how many columns of serial numbers are there?

just 4?, or more?

does that mean an order can only consist of a maximum number of items, corresponding to the serial number columns?

does it also mean it would be possible to duplicate a serial number
a) in the same column or
b) in multiple columns
----eg in column1 of 1 order, and then in column 4 of another order

---------
sorry theres a lot here, but your structure makes this difficult, as you appreciate

 

[G1ZmO]

why would there be multiple serial numbers in each row of the table? do these represent serial numbers of items included in the order? does this mean you have description columns in this table as well? or are you just recording serial numbers?

They just record the serial numbers currently in an excel sheet and call the sheet CUSTOMER_ORDERNUMBER.XLS

having said all that, how many columns of serial numbers are there? just 4?, or more?

It can vary from anything between 1 item and 100 items

does that mean an order can only consist of a maximum number of items, corresponding to the serial number columns?

I initially set up this new database to cope with 40 line items but if theres a better way of doing it I'd be pleased to change it.

does it also mean it would be possible to duplicate a serial number
a) in the same column or
b) in multiple columns
----eg in column1 of 1 order, and then in column 4 of another order

Theoretically this should not be possible unless an item is returned and resold.

.....

Just thinking: Maybe I could create a record for every item by setting up the Order-number as a dafault for each serial number?

I could have a separate table for Order number / Customer name / Ship Date. and do a lookup.

Any thoughts on that?

 

[gemma-the-husky]

when you say you have a database then, do you mean you are using a spreadsheet, or an access database

does that mean you have defined 100+ columns as

serial1
serial2
serial3 etc

you say you have allowed for 4o items, but you mention it might need to deal with over 100 items - sorry, just not clear

 

[G1ZmO]

At the moment the dispatchers are using excel sheets and they just scan serial numbers into 1 column of the sheet

I just started to make a database for them but I realise now that defining 40 to 100 fields for serial numbers is a bit of a mad way to do things.

I'd be pleased if someone could suggest a better way

Thanks

Paul

 

[gemma-the-husky]

right

you need at least these

customers table(id, name, address, phone etc)
products (id, description etc)
orders table (order no, date, customerid, etc)
order lines(orderno, lineno, productid, SERIALNO)

so you then end up with one column with ALL the serial numbers you have sold, instead of over 40 different columns. so if you have 500 orders, you might get 8000 order lines. The number of lines doesnt matter - Access will handle the display so that for each order, you only see the corresponding lines. It will also handle greater volumes than your spreadsheet, and be inordinately quicker. But there is quite a sharp learning curve, and you need in many ways to lose/change Excel thinking methods

depending how far you take it you may end up with various further tables, such as lookup tables for order status, despatch method, etc etc, but this outline will give you a base.

 

[G1ZmO]

Thanks Gemma

I dont think there is any way I'll be able to persuade the whole company to change their working methods but if I can improve the way dispatch does their stuff then maybe we'll get there eventually.

Is there a way that I can open a form - Select/Enter an order number and then enter serial numbers sequentially so that each serial number goes into a record with the associated order number? i.e. set the order number as a default until another one is selected.

 

[RuralGuy]

Gemma has been doing an outstanding job so far and I do not want to step on anyone's toes. The answer to your question is absolutely. It is the normal way things are done in a database. I'll let Gemma follow up with more description and just monitor the thread.