Calculations in an Inventory data Base

[Harvey Maron]

I have created a Parameter Query in my data base so I can do an inventory
check.
This allows me to Filter by 2 different criteria.
the information this brings up is as follows:
Record 1 - received 10 pcs in to location A2
Record 2 - Received 5 pcs in to location A3
Record 3 - Shipped 3 pcs from location A2

What I need is to end up with is
7 pcs left in stock at location A2 & 5 pcs left in Stock at Location A3

 

[Jeff Boyce]

Harvey

"How" depends on "what"... and I have no idea what data structure you're
using...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Harvey Maron]

Jeff
The main table I am using is called Inventory Transactions. In this table I
have the following fields.
Transaction ID
ProductID
Units Received
Units Sold
LocationID

When I do a receiving into the warehouse it will update the TranscationID,
ProductID, Units received & locationID, Then when I ship it updates the same
field except the Units Sold field instead of the Received field.
What I can also have is a specific item that can be in multiple Locations
and I can also have multiple items in one location

What I am trying to do is an Inventory check to see what I have left in
stock for a specific item and the warehouse locations they are in.

The databases that I have built before have never been this involved and I
am lost on how I should accomplish this. I down loaded a sample Inventory
database but they do not get this involved either.

Thanks
Harvey

 

[Jeff Boyce]

Harvey

I may still be misunderstanding your design/situation. See comments in-line
below...

Jeff
The main table I am using is called Inventory Transactions. In this table
I
have the following fields.
Transaction ID
ProductID
Units Received
Units Sold
LocationID

It appears that a row in your table contains data pertaining to both
incoming and outgoing units ("Received", "Sold"). Why are you considering
these two to represent a single "transaction"?

What happens if you receive 10 units, then sell 3 units, then later sell 4
more?

When I do a receiving into the warehouse it will update the TranscationID,
ProductID, Units received & locationID, Then when I ship it updates the
same
field except the Units Sold field instead of the Received field.
What I can also have is a specific item that can be in multiple Locations
and I can also have multiple items in one location

I assume you refer to "a specific item" by its [ProductID], and that you
have a table elsewhere that lists products.

I also assume the same holds for locations.

What I am trying to do is an Inventory check to see what I have left in
stock for a specific item and the warehouse locations they are in.

The databases that I have built before have never been this involved and I
am lost on how I should accomplish this. I down loaded a sample Inventory
database but they do not get this involved either.

Thanks
Harvey

The underlying table structure you've described seems to be more "Inventory"
oriented than "Transaction" oriented. If your application is only ever
overwriting the Units Received and the Units Sold fields, how do you keep
track of the individual transactions representing a new shipment received
and a new order fulfilled/shipped?

I feel I'm still not seeing the complete picture...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Harvey Maron]

Jeff
Here is a list off all the tables that I have
Size table
Tag Number Table
Description Table
Product table
The size Table, Description Table & tag nmber table are all linked into the
Product table.

Then I have the following other tables
Customer Table
Supplier table
Employee table
Freight Company Table
Receiving Table
Shipping Table
Warehouse Location table
Transaction Table

The receiving table is linked to the Supplier table, Employee Table,
Warehouse Location Table & the Transaction Table
The Shipping table is linked to Customer Table, Employee Table, Freight
Table, Warehouse Location Table & Transaction Table.

in the transaction table has the following information
Transaction ID, Tansaction date, ProductID, ReceivingID, ShippingID, Units
Received, Units Sold, LocationID

This is my main table that most of the other tables are linked to

Some of the things I need are to be able to Find a product by the size & tag
number and see all the transaction that have happened to that item, I do have
a query that tells me this.

We have 2 seperate warehouses the first is 60,000 sq.ft. and the other is
80,000 sq.ft.. The recevier will bin locate material when he recieves the
material were ever he finds empty spots.
So when the shippers need to find material to ship, I need the data base to
be able to tell them every spot that the specific item is located in.
Another report that I will have to do is to be able to do a transaction
history of everthing that has come in and gone out of a specific location.

When my shipper need to find the products to pick them, i need him to be
able to look this up and not show all the transactions, but just to show what
is left in stock and the warehouse location it is in. After a few months of
transactions the list can become very large.

Do to the nature of our business we can not assign a specific item to a
specific location. So today location A2 may hold item 1 and in 2 weeks item 1
could have been sold and now Location A2 hold item 3
Also due to the size of some of our product, we can only fit 1 piece into a
specific location so if I receive more that 1 piece it will have to be
located into multiple locations.

The main part for myself is Inventory control as you stated, but my client
has requested that I have some transaction controls so when they aduit us
they can see this.

Most of my design was taken from the sample inventory data base that you can
download from Microsofts website.

I hope I have explained this enough, let me know if you need anything else

Regards
Harvey

Harvey

I may still be misunderstanding your design/situation. See comments in-line
below...

Jeff
The main table I am using is called Inventory Transactions. In this table
I
have the following fields.
Transaction ID
ProductID
Units Received
Units Sold
LocationID

It appears that a row in your table contains data pertaining to both
incoming and outgoing units ("Received", "Sold"). Why are you considering
these two to represent a single "transaction"?

What happens if you receive 10 units, then sell 3 units, then later sell 4
more?

When I do a receiving into the warehouse it will update the TranscationID,
ProductID, Units received & locationID, Then when I ship it updates the
same
field except the Units Sold field instead of the Received field.
What I can also have is a specific item that can be in multiple Locations
and I can also have multiple items in one location

I assume you refer to "a specific item" by its [ProductID], and that you
have a table elsewhere that lists products.

I also assume the same holds for locations.

What I am trying to do is an Inventory check to see what I have left in
stock for a specific item and the warehouse locations they are in.

The databases that I have built before have never been this involved and I
am lost on how I should accomplish this. I down loaded a sample Inventory
database but they do not get this involved either.

Thanks
Harvey

The underlying table structure you've described seems to be more "Inventory"
oriented than "Transaction" oriented. If your application is only ever
overwriting the Units Received and the Units Sold fields, how do you keep
track of the individual transactions representing a new shipment received
and a new order fulfilled/shipped?

I feel I'm still not seeing the complete picture...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Harvey

Most folks here in the newsgroups are, like myself, volunteering our time to
offer ideas in response to fairly specific questions.

Your situation seems more like a request for help designing and building a
complete system/application.

Perhaps one of the other newsgroup readers can devote the time it seems like
you'll need, but I cannot.

Another option you could consider would be to search out someone for hire to
consult on the design and/or to build the application.

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff
Here is a list off all the tables that I have
Size table
Tag Number Table
Description Table
Product table
The size Table, Description Table & tag nmber table are all linked into
the
Product table.

Then I have the following other tables
Customer Table
Supplier table
Employee table
Freight Company Table
Receiving Table
Shipping Table
Warehouse Location table
Transaction Table

The receiving table is linked to the Supplier table, Employee Table,
Warehouse Location Table & the Transaction Table
The Shipping table is linked to Customer Table, Employee Table, Freight
Table, Warehouse Location Table & Transaction Table.

in the transaction table has the following information
Transaction ID, Tansaction date, ProductID, ReceivingID, ShippingID, Units
Received, Units Sold, LocationID

This is my main table that most of the other tables are linked to

Some of the things I need are to be able to Find a product by the size &
tag
number and see all the transaction that have happened to that item, I do
have
a query that tells me this.

We have 2 seperate warehouses the first is 60,000 sq.ft. and the other is
80,000 sq.ft.. The recevier will bin locate material when he recieves the
material were ever he finds empty spots.
So when the shippers need to find material to ship, I need the data base
to
be able to tell them every spot that the specific item is located in.
Another report that I will have to do is to be able to do a transaction
history of everthing that has come in and gone out of a specific location.

When my shipper need to find the products to pick them, i need him to be
able to look this up and not show all the transactions, but just to show
what
is left in stock and the warehouse location it is in. After a few months
of
transactions the list can become very large.

Do to the nature of our business we can not assign a specific item to a
specific location. So today location A2 may hold item 1 and in 2 weeks
item 1
could have been sold and now Location A2 hold item 3
Also due to the size of some of our product, we can only fit 1 piece into
a
specific location so if I receive more that 1 piece it will have to be
located into multiple locations.

The main part for myself is Inventory control as you stated, but my client
has requested that I have some transaction controls so when they aduit us
they can see this.

Most of my design was taken from the sample inventory data base that you
can
download from Microsofts website.

I hope I have explained this enough, let me know if you need anything else

Regards
Harvey

Harvey

I may still be misunderstanding your design/situation. See comments
in-line
below...

Jeff
The main table I am using is called Inventory Transactions. In this
table
I
have the following fields.
Transaction ID
ProductID
Units Received
Units Sold
LocationID

It appears that a row in your table contains data pertaining to both
incoming and outgoing units ("Received", "Sold"). Why are you
considering
these two to represent a single "transaction"?

What happens if you receive 10 units, then sell 3 units, then later sell
4
more?

When I do a receiving into the warehouse it will update the
TranscationID,
ProductID, Units received & locationID, Then when I ship it updates the
same
field except the Units Sold field instead of the Received field.
What I can also have is a specific item that can be in multiple
Locations
and I can also have multiple items in one location

I assume you refer to "a specific item" by its [ProductID], and that you
have a table elsewhere that lists products.

I also assume the same holds for locations.

What I am trying to do is an Inventory check to see what I have left in
stock for a specific item and the warehouse locations they are in.

The databases that I have built before have never been this involved
and I
am lost on how I should accomplish this. I down loaded a sample
Inventory
database but they do not get this involved either.

Thanks
Harvey

The underlying table structure you've described seems to be more
"Inventory"
oriented than "Transaction" oriented. If your application is only ever
overwriting the Units Received and the Units Sold fields, how do you keep
track of the individual transactions representing a new shipment received
and a new order fulfilled/shipped?

I feel I'm still not seeing the complete picture...

Regards

Jeff Boyce
Microsoft Office/Access MVP