Warehouse Organization and movement.

[Guest]

Hello,

I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.

 

[Guest]

How about creating a table to manage all the internal moves. When a request
comes in to move something it's recorded.

In this table create a field for the productID and also have a field for
it's new location. ie ABC123 is MOVING from W1 to W2.

You could then build a query from this table to GROUP by Product ID, MAX
Date (Last date) and display the location.

I did this quickly but I think this would work. I'm sure someone will
correct if they think of a better way.

 

[Jason Lepack]

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack

 

[Guest]

Thanks! I like the idea - I had also thought to also use the Inventory
transactions Table and have two entries (move from Abc as a 'sold' or
'shrinkage' or even a new field) then having a corresponding to location on
the next line.

But I am more concerened with how to get a "final location" report.

 

[Jason Lepack]

I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

 

[Guest]

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack




- Show quoted text -

 

[Jason Lepack]

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

http://jlepack.files-upload.com/221514/Inventory.mdb.html

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

- Show quoted text -

 

[Jason Lepack]

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

http://files-upload.com/225970/Inventory.mdb.html

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

http://jlepack.files-upload.com/221514/Inventory.mdb.html

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.
Transaction Table:
itemID
FromBinID
ToBinID
qty
Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]
Cheers,
Jason Lepack
David,
Here's what I read:
A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)
The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted
Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.
If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.
Inventory Table: (this will store up to date inventory)
binID
itemID
qty
Cheers,
Jason Lepack
Hello,
I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

I greatly appreciate the link!! I will try it out, and somehow merge the
work I have already done to it. You mentioned using the Transactions table
to enter data -- can I just use the Inventory Table directly? Or would that
not update something else I would need for later? I have about 100 items on
the current database - I could update the tables I need for the product
names, and create the Warehouse, bin, shelf, section information - then enter
directly... What do you think? I like the "Inner Join" and the way that
seems to work. I don't have that much exposure to Access so this has been a
huge learning experience... Again I really apprecite the help!

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

http://files-upload.com/225970/Inventory.mdb.html

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

http://jlepack.files-upload.com/221514/Inventory.mdb.html

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack

I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

It's not really me going out of my way, because it's something I was
working on, on the side (In SQL Server, but I ported the idea for
you...)

If you use the Transactions Form to handle your item transactions
then it automatically updates the inventory table by using the two
Trigger_ Queries on the AfterInsert event of the form. This event is
triggered whenever a form makes an "insert" of a new record to it's
recordset.

Note, there are two different methods depending on how heavy use your
inventory is.

I was beginning devloping this for a warehouse that had quite a bit of
transactions, so instead of having to run a query to aggregate all
historical transactions to come up with the current inventory level, I
used the trigger to update the inventory table as I go. This will
speed up the reporting process.

The other option is the "CurrentInventoryFromTransactions" Query.
This is the query that aggregates all the historical transactions for
all items and displays the results. The result set will be exactly
the same as the Inventory Table.


As for importing your data, you could definitely do that! The only
thing that I would suggest is to create transaction records for your
current inventory and then use the "RebuildInventory" query to
initialize the Inventory Table, that way you have intial transactions
of your Inventory.

Any questions you have on the whys or hows of anything in this
database just give me a shout. Any modifications you'd like I could
look at doing as well, and we could build this together as a learning
thing for you.

Cheers,
Jason Lepack

I greatly appreciate the link!! I will try it out, and somehow merge the
work I have already done to it. You mentioned using the Transactions table
to enter data -- can I just use the Inventory Table directly? Or would that
not update something else I would need for later? I have about 100 items on
the current database - I could update the tables I need for the product
names, and create the Warehouse, bin, shelf, section information - then enter
directly... What do you think? I like the "Inner Join" and the way that
seems to work. I don't have that much exposure to Access so this has been a
huge learning experience... Again I really apprecite the help!

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.
http://jlepack.files-upload.com/221514/Inventory.mdb.html
Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.
Cheers,
Jason Lepack
Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?
:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.
Transaction Table:
itemID
FromBinID
ToBinID
qty
Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]
Cheers,
Jason Lepack
David,
Here's what I read:
A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)
The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted
Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.
If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.
Inventory Table: (this will store up to date inventory)
binID
itemID
qty
Cheers,
Jason Lepack
Hello,
I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

I just thought of another "quirk" for you or anyone. Part of the issue what
my manager needs a report for is this -- Say we get 600 boxes of viewbooks
that have 225 in each box - then one departments starts using them and
depleating supply - then a couple months later we might get another 300 boxes
of viewbooks, this time with 375 - and some supply is used... But my manager
askes "How many viewbooks to we have now on hand, and where are they? (He
wants total "piece" number). I think the location issue it taken care of...
On the DB I modified, I added a "perbox" field, and added/modified a "Sum of
...." to calculate Qty * Perbox. (And when we send boxes out to the
department - I have the perbox as part of the transaction information)... I
will see if I can do the same or simular modifications.

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

http://files-upload.com/225970/Inventory.mdb.html

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

http://jlepack.files-upload.com/221514/Inventory.mdb.html

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack

I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

No, I wouldn't suggest you do it that way. You can if you want, but I
wouldn't. If I received 600*225 into an inventory location then I
have 135000 in that location. If I then issue 10000 to the assembly
floor, then I have 10000 in the assmbly floor location and 125000 in
the original location.

The reason why I say not do it in your way is that you would have to
perform another transaction for every quantity of box that you sent to
the floor. If in one location you had a whole bunch of one part with
many different quanities per case, then if you sent 2 boxes @ 10 and 1
box @ 5 and 20 boxes @ 30 then you have 3 different transactions. I
would just do one issue of 2*10 + 1*5 + 20*30 = 625 from that
location.

Cheers,
Jason Lepack

I just thought of another "quirk" for you or anyone. Part of the issue what
my manager needs a report for is this -- Say we get 600 boxes of viewbooks
that have 225 in each box - then one departments starts using them and
depleating supply - then a couple months later we might get another 300 boxes
of viewbooks, this time with 375 - and some supply is used... But my manager
askes "How many viewbooks to we have now on hand, and where are they? (He
wants total "piece" number). I think the location issue it taken care of...
On the DB I modified, I added a "perbox" field, and added/modified a "Sum of
..." to calculate Qty * Perbox. (And when we send boxes out to the
department - I have the perbox as part of the transaction information)... I
will see if I can do the same or simular modifications.

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.
http://jlepack.files-upload.com/221514/Inventory.mdb.html
Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.
Cheers,
Jason Lepack
Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?
:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.
Transaction Table:
itemID
FromBinID
ToBinID
qty
Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]
Cheers,
Jason Lepack
David,
Here's what I read:
A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)
The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted
Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.
If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.
Inventory Table: (this will store up to date inventory)
binID
itemID
qty
Cheers,
Jason Lepack
Hello,
I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

I guess that brings up an issue that I might not have understood or conveyed
to clearly... Of the 600*225 - they might be placed into 10 different
locations of the warehouse - then as we need them, move 60 from one location
to another (staging) area - before sent out to the requesting department.
Other warehouse locations might have 2-5 different items, depending on size
of the box (which, unfortunately, might be a pick work box where they might
take out 1000 out of a box that has 10,000 to start with) -- that puts the
twist that I have been having problems with as well... Transaction-wise, I
don't think having individual transactions would be that big of an issue --
just not sure if you realized the 1 item being in multiple location problem
(or the piece work). And your right about the different qty box issue - the
main department I am trying to get this set up for just returned some of the
partially used boxes - so they go back up on the shelf with a different qty
than the rest -- the the manager then asks -- how many total units we have
now.... It's been fun!!! I really appreciate your time!!!

No, I wouldn't suggest you do it that way. You can if you want, but I
wouldn't. If I received 600*225 into an inventory location then I
have 135000 in that location. If I then issue 10000 to the assembly
floor, then I have 10000 in the assmbly floor location and 125000 in
the original location.

The reason why I say not do it in your way is that you would have to
perform another transaction for every quantity of box that you sent to
the floor. If in one location you had a whole bunch of one part with
many different quanities per case, then if you sent 2 boxes @ 10 and 1
box @ 5 and 20 boxes @ 30 then you have 3 different transactions. I
would just do one issue of 2*10 + 1*5 + 20*30 = 625 from that
location.

Cheers,
Jason Lepack

I just thought of another "quirk" for you or anyone. Part of the issue what
my manager needs a report for is this -- Say we get 600 boxes of viewbooks
that have 225 in each box - then one departments starts using them and
depleating supply - then a couple months later we might get another 300 boxes
of viewbooks, this time with 375 - and some supply is used... But my manager
askes "How many viewbooks to we have now on hand, and where are they? (He
wants total "piece" number). I think the location issue it taken care of...
On the DB I modified, I added a "perbox" field, and added/modified a "Sum of
..." to calculate Qty * Perbox. (And when we send boxes out to the
department - I have the perbox as part of the transaction information)... I
will see if I can do the same or simular modifications.

I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

On May 11, 1:50 pm, David <[email protected]> wrote:

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack

I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

Example of receipt of shipment:
1000 cases of Item1 (10 pcs per case) - 10000 pcs received into
"0.0.0.Receiving"
600 cases - 6000 pcs moved from "0.0.0.Receiving" into "A.1.1.1"
300 cases - 3000 pcs moved from "0.0.0.Receiving" into "A.1.2.1"
100 cases - 1000 pcs moved from "0.0.0.Receiving" into
"0.0.0.Assembly"

10 cases of Item2 (10 pcs per case) - 100 pcs received into
"0.0.0.Receiving"
5 cases - 50 pcs moved from "0.0.0.Receiving" into "A.1.1.1"
2 cases - 20 pcs moved from "0.0.0.Receiving" into "A.1.2.1"
3 cases - 30 pcs moved from "0.0.0.Receiving" into "0.0.0.Assembly"

The assembly floor then returns 30 pcs of item1 and 5 pcs of item2 to
the warehouse, so there's another transaction:
30 pcs of item1 moved from "0.0.0.Assembly" to "A.1.2.1"
5 pcs of item2 moved from "0.0.0.Assembly" to "A.1.1.1"

Boss asks what we've got where, we go into the database, go to the
current inventory form (that we haven't built yet - but the
information is in the "Inventory" Table or
"CurrentInventoryFromTransactions" Query.

And we tell him, "Boss, here it is:
6000 pcs item1 in "A.1.1.1"
3030 pcs item1 in "A.1.2.1"
970 pcs item1 in "0.0.0.Assembly"

55 pcs item2 in "A.1.1.1"
20 pcs item2 in "A.1.2.1"
25 pcs item2 in "0.0.0.Assembly"

All of that stuff is done using the transactions form. As long as the
transactions are performed in the database, you always have an
accurate inventory. There is even the capability to view the data in
the "TransactionList" query to see historical transactions of parts.

The next step is to build in some sort of "Work Order Close" logic to
consume inventory that is used in a job, but for now, it's just going
to continue to live in "0.0.0.Assembly"

Cheers,
Jason Lepack

I guess that brings up an issue that I might not have understood or conveyed
to clearly... Of the 600*225 - they might be placed into 10 different
locations of the warehouse - then as we need them, move 60 from one location
to another (staging) area - before sent out to the requesting department.
Other warehouse locations might have 2-5 different items, depending on size
of the box (which, unfortunately, might be a pick work box where they might
take out 1000 out of a box that has 10,000 to start with) -- that puts the
twist that I have been having problems with as well... Transaction-wise, I
don't think having individual transactions would be that big of an issue --
just not sure if you realized the 1 item being in multiple location problem
(or the piece work). And your right about the different qty box issue - the
main department I am trying to get this set up for just returned some of the
partially used boxes - so they go back up on the shelf with a different qty
than the rest -- the the manager then asks -- how many total units we have
now.... It's been fun!!! I really appreciate your time!!!

No, I wouldn't suggest you do it that way. You can if you want, but I
wouldn't. If I received 600*225 into an inventory location then I
have 135000 in that location. If I then issue 10000 to the assembly
floor, then I have 10000 in the assmbly floor location and 125000 in
the original location.

The reason why I say not do it in your way is that you would have to
perform another transaction for every quantity of box that you sent to
the floor. If in one location you had a whole bunch of one part with
many different quanities per case, then if you sent 2 boxes @ 10 and 1
box @ 5 and 20 boxes @ 30 then you have 3 different transactions. I
would just do one issue of 2*10 + 1*5 + 20*30 = 625 from that
location.

Cheers,
Jason Lepack

I just thought of another "quirk" for you or anyone. Part of the issue what
my manager needs a report for is this -- Say we get 600 boxes of viewbooks
that have 225 in each box - then one departments starts using them and
depleating supply - then a couple months later we might get another 300 boxes
of viewbooks, this time with 375 - and some supply is used... But my manager
askes "How many viewbooks to we have now on hand, and where are they? (He
wants total "piece" number). I think the location issue it taken care of...
On the DB I modified, I added a "perbox" field, and added/modified a "Sum of
..." to calculate Qty * Perbox. (And when we send boxes out to the
department - I have the perbox as part of the transaction information)... I
will see if I can do the same or simular modifications.
:
I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.
http://files-upload.com/225970/Inventory.mdb.html
Cheers,
Jason Lepack
You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.
http://jlepack.files-upload.com/221514/Inventory.mdb.html
Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.
Cheers,
Jason Lepack
Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?
:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.
Transaction Table:
itemID
FromBinID
ToBinID
qty
Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]
Cheers,
Jason Lepack
David,
Here's what I read:
A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)
The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted
Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.
If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.
Inventory Table: (this will store up to date inventory)
binID
itemID
qty
Cheers,
Jason Lepack
Hello,
I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

Near and dear to my heart is this project... Similar to things that I've
failed to do in the past. Thanks for posting! Just a small comment: I
believe that you might be able to come up with a way to track all of the
assembly locations and to keep up with all of the transactions necessary to
update your "Work In Progress" location, but honestly, will you ever get your
material handlers to actually do the updates? I've found that in a busy
warehouse/ production shop that things can move very fast and that there is a
lot of pressure on the material handlers to get their duties done quickly.
So, asking these guys to keep up with all these extra transactions will take
a buy in from the top down. Unless you have this, I wouldn't feel too bad
about not having a method to track this activity. Still, for you, coming up
with a way to do it shouldn't be too big of a task. I would go ahead and
come up with a way for it to work, then leave it for others to get everyone
to use it. Unless that's your job too...

Good luck!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Example of receipt of shipment:
1000 cases of Item1 (10 pcs per case) - 10000 pcs received into
"0.0.0.Receiving"
600 cases - 6000 pcs moved from "0.0.0.Receiving" into "A.1.1.1"
300 cases - 3000 pcs moved from "0.0.0.Receiving" into "A.1.2.1"
100 cases - 1000 pcs moved from "0.0.0.Receiving" into
"0.0.0.Assembly"

10 cases of Item2 (10 pcs per case) - 100 pcs received into
"0.0.0.Receiving"
5 cases - 50 pcs moved from "0.0.0.Receiving" into "A.1.1.1"
2 cases - 20 pcs moved from "0.0.0.Receiving" into "A.1.2.1"
3 cases - 30 pcs moved from "0.0.0.Receiving" into "0.0.0.Assembly"

The assembly floor then returns 30 pcs of item1 and 5 pcs of item2 to
the warehouse, so there's another transaction:
30 pcs of item1 moved from "0.0.0.Assembly" to "A.1.2.1"
5 pcs of item2 moved from "0.0.0.Assembly" to "A.1.1.1"

Boss asks what we've got where, we go into the database, go to the
current inventory form (that we haven't built yet - but the
information is in the "Inventory" Table or
"CurrentInventoryFromTransactions" Query.

And we tell him, "Boss, here it is:
6000 pcs item1 in "A.1.1.1"
3030 pcs item1 in "A.1.2.1"
970 pcs item1 in "0.0.0.Assembly"

55 pcs item2 in "A.1.1.1"
20 pcs item2 in "A.1.2.1"
25 pcs item2 in "0.0.0.Assembly"

All of that stuff is done using the transactions form. As long as the
transactions are performed in the database, you always have an
accurate inventory. There is even the capability to view the data in
the "TransactionList" query to see historical transactions of parts.

The next step is to build in some sort of "Work Order Close" logic to
consume inventory that is used in a job, but for now, it's just going
to continue to live in "0.0.0.Assembly"

Cheers,
Jason Lepack

I guess that brings up an issue that I might not have understood or conveyed
to clearly... Of the 600*225 - they might be placed into 10 different
locations of the warehouse - then as we need them, move 60 from one location
to another (staging) area - before sent out to the requesting department.
Other warehouse locations might have 2-5 different items, depending on size
of the box (which, unfortunately, might be a pick work box where they might
take out 1000 out of a box that has 10,000 to start with) -- that puts the
twist that I have been having problems with as well... Transaction-wise, I
don't think having individual transactions would be that big of an issue --
just not sure if you realized the 1 item being in multiple location problem
(or the piece work). And your right about the different qty box issue - the
main department I am trying to get this set up for just returned some of the
partially used boxes - so they go back up on the shelf with a different qty
than the rest -- the the manager then asks -- how many total units we have
now.... It's been fun!!! I really appreciate your time!!!

No, I wouldn't suggest you do it that way. You can if you want, but I
wouldn't. If I received 600*225 into an inventory location then I
have 135000 in that location. If I then issue 10000 to the assembly
floor, then I have 10000 in the assmbly floor location and 125000 in
the original location.

The reason why I say not do it in your way is that you would have to
perform another transaction for every quantity of box that you sent to
the floor. If in one location you had a whole bunch of one part with
many different quanities per case, then if you sent 2 boxes @ 10 and 1
box @ 5 and 20 boxes @ 30 then you have 3 different transactions. I
would just do one issue of 2*10 + 1*5 + 20*30 = 625 from that
location.

Cheers,
Jason Lepack

I just thought of another "quirk" for you or anyone. Part of the issue what
my manager needs a report for is this -- Say we get 600 boxes of viewbooks
that have 225 in each box - then one departments starts using them and
depleating supply - then a couple months later we might get another 300 boxes
of viewbooks, this time with 375 - and some supply is used... But my manager
askes "How many viewbooks to we have now on hand, and where are they? (He
wants total "piece" number). I think the location issue it taken care of...
On the DB I modified, I added a "perbox" field, and added/modified a "Sum of
..." to calculate Qty * Perbox. (And when we send boxes out to the
department - I have the perbox as part of the transaction information)... I
will see if I can do the same or simular modifications.

:
I made a mistake in the last file, I didn't quite finish off one of
the Trigger queries. This one works better.

Cheers,
Jason Lepack

You might want to take a look at this. It's something that I ahd been
starting, but I touched it up for you. If you want more info about it
then post back here.

Please note that if you don't use the transaction form to add
transactions then it won't update the Inventory Table. If you were to
use SQL server you could have triggers attached to the table do this
but JET (the guts of Access) don't do that.

Cheers,
Jason Lepack

On May 11, 1:50 pm, David <[email protected]> wrote:

Thanks! I will try this. I have added a subform equation to calculate the
"total units on hand" (so in the Products form, it is calculated) - I know I
should be able to add that to a report - but how do I do that again?

:
I was thinking as I clicked send that all transactions are pretty much
moves... So I came up with another way to do it that makes it easier
at the input stage.

Transaction Table:
itemID
FromBinID
ToBinID
qty

Then you would use a UNION query to come up with a result set exactly
like the TransactionTable I originally mentioned:
SELECT
itemID,
FromBinID AS BinID,
-qty as amt
FROM
[Transaction Table]
UNION ALL
SELECT
itemID,
ToBinID as BinID,
qty as amt
FROM
[Transaction Table]

Cheers,
Jason Lepack

David,

Here's what I read:

A Warehouse has many Sections (Warehouse = warehouseID, warehouseName)
A Section has many Shelves (Section = sectionID, wareHouseID,
sectionName)
A Shelf has many Bins (Shelf = shelfID, sectionID, shelfName)
A Bin has many Items (Bin = binID, shelfID, binName)
An Item has many Bins (Item = itemID, itemName, etc)

The best way for the transaction table to work would be like this:
Transaction Table:
itemID - number - joined to item table
binID - number - joined to a bin
TransDateTime - date/time - when the transaction occurred
qty - number - the amount transacted

Now a move will have two records in the transaction table. One with a
negative quantity to remove it from the old location and one with a
positive quantity to insert it into the new loaction. Then if you
want to see how much inventory you have of a specific item then you
just create a query that looks for the ItemID, BinID, and sums the
quantity.

If this is to be used for a large business you will definitely want to
upgrade to something larger than Ms Access but it will be fine to
prove concept. You will want to learn about Indexes as the
transaction table will grow quickly, and you might want to even keep
another table for reporting, ie when a transaction is completed update
the quantities that were modified in a transaction.

Inventory Table: (this will store up to date inventory)
binID
itemID
qty

Cheers,
Jason Lepack

I am working on a project to keep track of the items in a warehouse. I am
modifying the "Inventory Control" template from the MS website. I am trying
to solve a location issue. The issue is -- we receive some items (e.g. 10
pallets of paper having 40 cases each). Which then goes into 10 areas of the
warehouse (which I have set up as tables -- Warehouse, Section, Bin, and
shelf). Then as requests are made for the items, they might need to be moved
to another location as we use them (or just moved to more organize the space
available). The manager occasionally wants a "current location" report to
print - so if we are looking for something, we can print the report and know
its (final) location and quantity. I have been using the Inventory
Transactions table to keep track of what comes in and what goes out - but not
sure how to handle the "internal" moves - from one location to another. And
how to report the 'final' location of something.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[MrEd]

I am having a similar experience. I upgraded to R2 and promoted a new server
to be the RMS. After I did that I started getting this error.

Event Type: Error
Event Source: Health Service Modules
Event Category: Data Warehouse
Event ID: 31552
Date: 7/29/2009
Time: 6:25:33 AM
User: N/A
Computer: OPMGR
Description:
Failed to store data in the Data Warehouse. Exception 'SqlException':
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

One or more workflows were affected by this.

Workflow name:
Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance
Instance name: State data set
Instance ID: {FB2C874C-F774-A9E5-EB99-23BA24544B07}
Management group: OpsMgr

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

 

[MedeBay]

Did you ever find a resolution. I am having the same problem.

Brian