Help me with a formula for this

[yorrdii]

Hi all,

I'm a bit stuck in access.

i want to create 3 tables (inbound, outbound and stocklist)

if i put a line item in 'inbound' table it should also be added to the 'stocklist' table.

and if i put a line item on the 'outbound' table it should be removed only from the 'stocklist' table not from the inbound.

is there anyone who can help me?
Thanks!

[Bob Fitz]

Why not just have one table to record in and out records. Record outs as minus figures and in as positive figures.

[orange]

In addition to Bob's suggestion, you might get more info and ideas from this Allen Browne link re: Quantity on Hand.
Good luck.

[yorrdii]

Why not just have one table to record in and out records. Record outs as minus figures and in as positive figures.

We need to be able to output the information from the tables, every one in a different excel sheet. thats why we cannot use 1 table.

[Bob Fitz]

We need to be able to output the information from the tables, every one in a different excel sheet. thats why we cannot use 1 table.

I would have thought that you could extract whatever data you need from the one table using queries and used those to export the data to excel.

[orange]

OK. Then perhaps you could describe the "business" in simple terms. What exactly are you working with? And what exactly do you do with the various data? We don't know your business nor details of what you are trying to accomplish. Tell us like you would tell an 8 year old, and readers will give you some more focused responses.

In addition some sample data, showing
-input, and
-outputs based on the inputs would be helpful.

[yorrdii]

we have a project running for another company. we are working now with 3 different excel sheets an inbound file, outbound file and a stocklist file. The inbound is to record any incoming items, the outbound is to record any outgoing items and the stocklist is a file what we have currently in our stock. so if we put something in on the inbound list, it should be copied to the stocklist automatically. if we put something on the outbound list, it should be removed from the stocklist. so the stocklist in the only list that is able to receive or remove items.

i hope i explained this right.

[yorrdii]

for example. we receive an item (blank item) we give it a barcode 12345. that barcode is then added to the inbound list for recording and that exact number (12345) should be directly copied to the stocklist, like instant.

when we ship that item we put that barcode (12345) on the outbound list. then 12345 should be removed from stocklist, instant.

[yorrdii]

For the 3 different tables.

we need to send the company 3 files.
What we received
What went out
what is currently in stock.

[orange]

How about supplying some sample for inbound, and outbound.
In a typical inventory/stock management application there are Customers, Supplier, Items/Products, ProductType etc. But, since your situation seems not so typical, some more details would be helpful. Forum members will help, but need to understand what you are trying to do.

What happens if there is insufficient X in stock to satisfy a request?
Sample data will help communications.

UPDATE:

I was writing my response while you were adding 2 posts.
In the 12345 example, what do you do with an inbound item other than putting on a barcode?
If you get 25 of 12345 and output only 20, what happens?
Do you always transfer all of the inbound to outbound?
What other conditions exist?

Typical approach:

Inbound---->(add/positive/+ quantities to) -->Current Stock
Outbound--->(minus,negative/- quantities from) -->Current Stock.

Did you look at the reference link I posted?

[yorrdii]

Here are the files. maybe it works out.

that is what we use right now.

every box has its own barcode which is unique.
quantity is always set to 1. we cant have a 30 barcodes 12345 for example.

[orange]

Is this your first Access project?
You should research Normalization --key concept with database.
You may have some hidden entities (Pallet...)?
There are many duplicated fields across your proposed tables.
For Access, you should not use names with embedded spaces.

For your own knowledge and experience, I recommend that you work through 1 or 2 or the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. If you work through 1 or 2 of the tutorials, you will learn about tables and relationships. Each tutorial will take about 45 minutes--BUT you will learn and experience a procedure that can be used with any database. Best 45 minutes you'll spend learning about databases.
Good luck.

[yorrdii]

Is this your first Access project?
You should research Normalization --key concept with database.
You may have some hidden entities (Pallet...)?
There are many duplicated fields across your proposed tables.
For Access, you should not use names with embedded spaces.

For your own knowledge and experience, I recommend that you work through 1 or 2 or the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. If you work through 1 or 2 of the tutorials, you will learn about tables and relationships. Each tutorial will take about 45 minutes--BUT you will learn and experience a procedure that can be used with any database. Best 45 minutes you'll spend learning about databases.
Good luck.

Yes this is my very first access project.
i will look at the tutorials Thanks!!!