Working with order numbers both in and out for multiple items - new project

[hullstorage]

Hi all,
I will keep this as simple as i can so not to confuse anyone i hope lol

I am trying to create a database to log all stock items coming in and going out to and from carriers or couriers as currently this is all done manually.

When goods come in this can be 1 item or 20 items and they will have 1 unique order number and 1 unique waybill, this also applies for goods going out ?

(Goods In)
All goods that come in will have an Order number and waybill that must be logged against every item(s)
(Goods Out)
All goods that go out will have a Shipment Number and Waybill also that must be logged (these will be from above goods in that have previously been booked in)

I want to say enter order number, waybill then have a subform to scan in the part details and dont want to keep having to scan in order number and waybill numbers for every part if there are multiple items (please see attached pic to give some idea)

I also cant figure out what is best for goods out, do i simply duplicate goods in and rename a few fields ? or do i need relationships

Ideally i would like to enter the shipment number, waybill number then when i scan the su number in the subform this will pull data from goods in table like part number, serial number etc ?

Any advice will be much appreciated.

 

[Uncle Gizmo]

Goods out would normally go to a customer?

Therefore you would have an invoice with line items to record the actual goods.

If there is an error and a customer returns something then you have the ability to create a credit note, based on the invoice.

What I am trying to say is if you incorporate the normal standard methods of doing business in your database then many of the problems you will face have been already been handled.

 

[hullstorage]

sorry i maybe should have mentioned but wanted to keep as simple as possible
actually there are no invoices or charges involved and stock simply gets delivered and collected
all stock is owned by another company and we simply store and ship for them.
all these are is for our records only

 

[Uncle Gizmo]

What about this, see video number 3 "Serial number collector":-

Check List Extras – Nifty Access

www.niftyaccess.com

 

[Uncle Gizmo]

And there's this Barcode Scan video here:-

NEW TO ACCESS PLEASE HELP

Hello everyone I'm trying to make a POS cashing form(i have a database table).i have 3 fields(UPC,UPC1,UPC2) in the table with different codes. I would like to create a form that contains a text box and a list box when i input a code into text box , it will automatically search in UPC then UPC1...

www.access-programmers.co.uk

 

[Uncle Gizmo]

I'm still of the opinion you need something like an invoice with line items because you must have addresses where you send the things to, which is basically the same as having a customer.

If you follow a tried and tested approach things are usually much simpler or at least these solutions to your problems easier to find.

 

[hullstorage]

here is were i am at, the goods in works fine and it is now just when we are sending the parts out as getting duplicate error ?

we need the same as goods in for goods out but when we scan the su number this then pulls data in from the goods inward for part number and serial...ive tried different ways but still keep getting duplicate error as it must be trying to create a new su number

 

[gemma-the-husky]

you will probably have the same part number on multiple goods inwards notes. Unless you do back to back sales, the sales and the purchases are different entities.

purchase --- in ---- stock record ----out ----- sales

you can rarely attribute a sale to a specific purchase(s)

what you are getting is that for each sale, you are getting every possible goods inwards match, which isn't what you want.

so your purchase gets stored in the stock transactions table, and the sale gets stored in the transaction table. The stock transaction, purchase and sale should all be linked to a single part record, and that's where you hold the description, cost (maybe), supplier (maybe), and any other relevant information. (I say maybe, because these data elements may be kept in the part table, but may need a separate table of their own, if there might be multiple options)

 

[jdraw]

@hullstorage

all stock is owned by another company and we simply store and ship for them.
all these are is for our records only

Can you give a few examples of Stock coming in and Stock going out?
I'm trying to understand the level of detail and any processing of Stock while in your storage facility.
What exactly do you need for your records?

 

[hullstorage]

@hullstorage


Can you give a few examples of Stock coming in and Stock going out?
I'm trying to understand the level of detail and any processing of Stock while in your storage facility.
What exactly do you need for your records?

please see above there are a few examples and also what the stock is its basically just booking in part in and out with no costs, suppliers etc..just for our records what comes in and when sent out but muliple parts against 1 ref no in and same as going out

 

[jdraw]

I read the posts by others and still wonder what you do, if anything, with the materials you receive before shipping them out.

For clarity, is this your use of RMA number?
RMA is an acronym for Return Material Authorization.
A RMA number is assigned to the service order that is created when a customer requests repair, or service of product believed to be defective.

When goods come in this can be 1 item or 20 items and they will have 1 unique order number and 1 unique waybill, this also applies for goods going out ?

I don't see an Order number in your tables??
This direct quote from post #1 ends with a question mark --is this really a question?

You show ShipmentNo for outgoing, but isn't there an equivalent term for incoming?

As Dave said in post #8, you should consider a Transaction table. I realize you said

I will keep this as simple as i can

but I think there are some details needed in order to offer focused advice.

I think your structure would be more along this setup, but would need additional facts re Shipment, SUnumber... and what you need exactly for your purposes.