Stock management

[Guest]

I have a d/b with a table containing over 1000 tropical fish, I need to
manage the stock of this table. Ie I need to know what my current stock is,
how many of a certain species has died , what sort and how many of a species
I have sold. My staff need to be able to type a name in and then input the
ammount of that fish they have sold, at the end of the day I need to run a
report on the days activity - many thanks Colin

 

[Guest]

Hi Colin,

This article, written by Access MVP Allen Browne, should get you started:

Inventory Control: Quantity on Hand
http://allenbrowne.com/AppInventory.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a d/b with a table containing over 1000 tropical fish, I need to
manage the stock of this table. Ie I need to know what my current stock is,
how many of a certain species has died , what sort and how many of a species
I have sold. My staff need to be able to type a name in and then input the
ammount of that fish they have sold, at the end of the day I need to run a
report on the days activity - many thanks Colin

 

[Guest]

I am sorry Tom I am getting nowhere- I have just spent over 5 hours trying to
input the data in from the example you shown, I dont even think I have enterd
the data correctly.If I was to create a new database what tables would I need
to accomplish my aim. Ie to find out how many fish I have sold In 1 week -
How many deaths I have in that week And the ammount of stock left.My staff
would need to input the fish into the d/b as they sell them.Try to be idiot
proof it would be much appreciated, I have been stuck on this for 2 weeks now
- thanks Colin

 

[Ed Warren]

Colvin, you're asking a lot if you have little or no experience with
MsAccess to be able to build a robust application that is 'idiot proof' and
suitable for a commerical application.

You might want to take a look at: Microsoft Business Solutions at :
http://www.microsoft.com/BusinessSolutions/RetailManagementSystem/rms_productoverview.mspx#EDAA
as a 'standard'. They list a lot of the features you need to consider in
building your application.

Now to your question restated:

I need to maintain an inventory of fish. Fish come in about 1000 different
types. I need to start with an inventory of each type of fish I currently
hold. As time passes, I need to add any fish that I receive from my
suppliers. I need to record the number of fish removed. Fish can be
removed by selling them, by dying. This needs to be a simple application
that poorly trained clerks can interact with.


You will need at least the following tables and forms.

Table:

FishType (actually there may be additional tables like WaterType
(fresh/salt), FishCategory (goldfish, minnows, darters, catfish, etc.
(super categories of fish to help sort out your 1000 different fish types).

FishTransactionType
TransactionTypeID (pk) TransactionType(text) Credit_Debit (true/false)
1 Died Negative
2 Received Positive
3 Sold Negative
4 Lost By Inventory Negative
5 Gained by Inventory Positive
etc.

FishType
FishTypeID(key) FishTypeText
1 feeder goldfish
2 Koi Carp
3 Striper Bass
etc.

FishInventory Primary Key (FishTypeID,InventoryDate)
FishTypeID(fk to FishType Table) InventoryDate (date)
FishCount(integer)
1 09/01/2005
1000
2 09/02/2005
50
etc.

FishTransaction:
FishTransactionID (Primary Key) FishTypeID (fk) TransactionTypeID
TransactionDate FishNumber (new fish added as a positive number, fish
lost, sold as negative numbers)
1 1
3 10/18/2005 30 (30 feeder
goldfish sold)
2 2
3 10/18/2005 2 (2 koi carp
sold)
3 1
1 10/18/2005 5 (5 goldfish
died)
4 1
2 10/18/2005 300 (300 goldfish
received from supplier and added)
etc.


Table Relationships:
FishType 1--M FishInventory (on FishTypeID)
FishTransactionType 1--M FishTransaction (on FishTransactionTypeID)
FishType 1--M FishTransaction (on FishtypeID)

Forms:

FishTransactionType
FishType
FishInventory
FishTransaction (TransactionDate -->Default to now(), FishType(comboBox
lookup from the FishType (show the text sorted by name, store the
FishTypeID)) TransactiontypeID (comboBox from TransactionType (show the
Text, store the TransactiontypeID)

Reports
You will have to build a set of queries to deliver the reports you want, but
with the table structure above you will be able to get all the answers you
have posted here.
You will start with the most recent Inventory count for each fish type,
query the transactions after that date, add the Positive type and substract
the negative type.

This should be enough to get you started.

Ed Warren.

 

[Guest]

WoW Ed
Thank you very much for taking the time to do that for me, I am now going to
sit down and try and work through it - I will let you know How I got on -
Thank you Colin

 

[PC Datasheet]

Colvin,

If you aren't able to get it done on your own and need help with this, I can
help you. I charge a fee however but it is very reasonable, Try hard but
then if you need help, contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.

 

[John Marshall, MVP]

Ambulance chasing again?

John... Visio MVP

 

[Guest]

Hello Ed
Well I have put the tables in you suggested and I can pull a report on my
deaths and stock sold. (on the report it shows minus figures is there anyway
to convert that into a positive number ie the report says I have lost -6
Tiger barbs as opposed to I have lost 6 Tiger barbs) . Also I cannot get a
query to show me my current stock ( it comes up with 18 lines when there
should be 6 - everything is in triplicate although not showing the same
result). Thanks Colin

 

[Ed Warren]

1. You can use positive numbers in all transactions and that will correct
the -6 (then use the Credit_Debit field to calculate the +,- values as shown
beloe)
or
2. In your report where you show the -6 value you can use a 'calculated
value' = iif([Credit_Debit] = false,-[value],[value]) (assume
Credit_Debit = false means fish gone and true means fish added) [The
statement above says if the field [Credit_Debit] is false then show the
value as -(-6) --> 6 otherwise show the value.
---------------------
I expect the problem with the duplicate records is failure to use a groupby
query and/or inappropriate groups.

You need to base your report on a query (set of queries) that return the
following. (Note: I'm not giving the specific sql here: 1)It gets tedious
and complex to write out, 2) You need to know how to build queries to
retrieve the information you want).

Go find the most recent inventory date and fishcount for each type of fish
(not all will be counted on the same day).
Go find all the transactions for each type of fish for dates greater than
the inventory date and transaction date/time for that type of fish, then
calculate a sum for each fishtype and transactiontype.
Give me a total for fishLost and a total for fishGained for each fish type
since the last inventory date for that fishtype. (gained --> Credit_Debit
=True, lost -> Credit_Debit=false)
(You will use a Field in the query like FishLost:Sum(iif(Credit_Debit =
true,0,[FishNumber])) FishGained:Sum(iif(Credit_Debit =
False,[FishNumber],0))

Give me a current inventory for each fishtype
(StartingInventory+FishGained-FishLost)

You will have to spend some time messing about with the query designer to
get the records you want. This includes how to handle 'null values' (you
did not have any of this type of fish die during the period should return a
value: died = 0

You also may want to 'mess about' with the Pivot Table (Access 2002 or
greater) it does a nice job of grouping data for you.

Your final report should look something like

Tiger Barbs InventoryDate: 8/1/2005 Starting Inventory: 100 Current
Inventory: 82
Died: 10
Sold: 20
Total Lost: 30
Received: 10
Gained by Inventory: 2
Total Gained: 12
Koi Carp InventoryDate: 7/1/2005 Starting Inventory: 10 Current
Inventory: 8
Sold: 2
Total Lost: 2
Total Gained: 0
Hope this helps more than it hurts

Ed Warren.