Working with order numbers both in and out for multiple items - new project (1 Viewer)

hullstorage

Registered User.
Local time
Today, 15:46
Joined
Jul 18, 2007
Messages
213
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.
 

Attachments

  • SCREENSHOT GOODS INWARD.png
    SCREENSHOT GOODS INWARD.png
    23.4 KB · Views: 256
  • SCREENSHOT GOODS OUTWARD.png
    SCREENSHOT GOODS OUTWARD.png
    19.3 KB · Views: 254

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:46
Joined
Jul 9, 2003
Messages
16,244
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.
 
Last edited:

hullstorage

Registered User.
Local time
Today, 15:46
Joined
Jul 18, 2007
Messages
213
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

Nifty Access Guy
Staff member
Local time
Today, 15:46
Joined
Jul 9, 2003
Messages
16,244
What about this, see video number 3 "Serial number collector":-

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:46
Joined
Jul 9, 2003
Messages
16,244
And there's this Barcode Scan video here:-

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:46
Joined
Jul 9, 2003
Messages
16,244
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

Registered User.
Local time
Today, 15:46
Joined
Jul 18, 2007
Messages
213
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
 

Attachments

  • Rico2021.accdb
    640 KB · Views: 256

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:46
Joined
Sep 12, 2006
Messages
15,613
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

Super Moderator
Staff member
Local time
Today, 11:46
Joined
Jan 23, 2006
Messages
15,364
@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

Registered User.
Local time
Today, 15:46
Joined
Jul 18, 2007
Messages
213
@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

Super Moderator
Staff member
Local time
Today, 11:46
Joined
Jan 23, 2006
Messages
15,364
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.

DraftWarehouse.PNG
 

Users who are viewing this thread

Top Bottom