How to track my inventory

[JAS1924]

I've searched and searched, can't find what I need. I'm a very basic user,
but am willing to write code if it's an easy walkthrough, or cut & paste.
Here's my dilemma:

I am trying to track our inventory for a wholesale nursery. Plants come in,
plants go out, and somehow I'm not able to see what I've got currently in
inventory. I've got my Invoices form tied to my products list. I've tried
performing a query that would add up all the times a particular item was
sold, then subtract that from what I've brought in. I actually know what I'm
doing wrong (for a change) but don't know how to do it right. What I've got
now is a report that's basically adding every instance of a sale (for
example: 100 - 4 sold PLUS 100 - 5 sold). My answer should be 91, but of
course, it's adding them up, so I show 191 in inventory. Very frustrating.
I'm also trying to decide if I should do an append query to update my total
available in the products table?

If anyone's got a template they use/like, or can just walk me through how to
make the query I'm looking for, I'd greatly appreciate.

Thanks.

 

[Jeff Boyce]

Are you saying you have the total number of Item#123 units brought in
somewhere in a table?

And in another table you have Item# sold & quantity sold?

If so, it would seem that you could sum up all the sold, then subtract that
from the total brought in.

Another way of looking at it, though, would be as a series of transactions.
Each new shipment of Item#123 adds a record with that many items (get 50
shipped in on 4/27, you have a row in a transaction table that shows +50 on
4/27). And each sale rights an "outgoing" transaction (?sold 3 on 4/28, new
row in the transaction table that shows -3 on 4/28).

If you have a transaction table somethig like:

Txn
TxnID
TxnDate
TxnItemNumber
TxnQuantity
TxnComment

you could write a simple query that "sums" all quantities of Item#123.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

See Allen Browne's Web site:

http://www.allenbrowne.com/AppInventory.html

 

[JAS1924]

Thanks for the response. Yes, it would seem that I should be able to sum the
sold and subtract from the units brought in, but however I have my query set
up, it seems to sum EVERY item sold, and subtract from EVERY item brought in.
I can't seem to get it to just look at the individual item.

What you suggested, though, is making more and more sense. I originally
didn't want to have my products set up that way, seemed too hard to find the
product I want to change (I've got over 2000 products). Maybe there's a quick
form I can make that would help the user find the product they'd like to
change? Also, would I be able to tie my invoices to that products table to
add a record every time something is sold, so that I'm not doing it by hand?

Thanks for your help.

 

[Jeff Boyce]

I don't have a very clear picture of how you've design your table/data
structure.

Since queries are based on tables (data), could you provide either an
example of the data you have or the table structure, if the field names are
self-explanatory?

E.g., a "Person" table might hold:

Jeff Boyce 4/1/2000 12345 Elm St. Anywhere ST 99999

and the structure might be:

FName
Lname
DOB
StreetAddr
City
State
PostalCode

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JAS1924]

Jeff, here's what I've got:

ProductsTable:

ProductID
BotanicalName
TotalPurchased [this is just a running tally at this point of all
acquisitions, so I literally add on to this field every time something's
brought in]
Price
etc...

InvoiceDetailsTable:

InvoiceID
ProductID
Qty [this is exactly how many sold for that particular transaction]
UnitPrice

My 'Availability' query is set up as follows:

ProductID
Total Available:
Sum(IIf(IsNull([Qty]),([TotalPurchased]),([TotalPurchased]-[Qty])))

I think my problem is the Sum function, because it's taking TotalPurchased -
Qty and summing that total (ie: 100Purchased - 10Sold + 100Purchased -
20Sold, etc.).

I've got another query I'm experimenting with to just sum the Qty and then
subtract from TotalPurchased, but it's giving me a really high number. I
think it must be adding up every instance of [Qty] rather than just for the
individual product.

AvailabilityQry

TotalPurchased
Available=[TotalPurchased]-(Sum([Qty]))
TotalSold=[Sum(Qty)]


Thanks,

Jessica

 

[Jeff Boyce]

Jessica

I'm wondering if you might not get some new ideas on how to set this up if
you looked at the Northwind database that comes with Access as a sample.

The Products table field [TotalPurchased] requires constant updating by you,
changing the number every time you buy more. I was suggestion a table with
one record per transaction (buy or sell). You could even make that
transaction table with a positive number of units for when you buy and a
negative number for when you sell.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, here's what I've got:

ProductsTable:

ProductID
BotanicalName
TotalPurchased [this is just a running tally at this point of all
acquisitions, so I literally add on to this field every time something's
brought in]
Price
etc...

InvoiceDetailsTable:

InvoiceID
ProductID
Qty [this is exactly how many sold for that particular transaction]
UnitPrice

My 'Availability' query is set up as follows:

ProductID
Total Available:
Sum(IIf(IsNull([Qty]),([TotalPurchased]),([TotalPurchased]-[Qty])))

I think my problem is the Sum function, because it's taking
TotalPurchased -
Qty and summing that total (ie: 100Purchased - 10Sold + 100Purchased -
20Sold, etc.).

I've got another query I'm experimenting with to just sum the Qty and then
subtract from TotalPurchased, but it's giving me a really high number. I
think it must be adding up every instance of [Qty] rather than just for
the
individual product.

AvailabilityQry

TotalPurchased
Available=[TotalPurchased]-(Sum([Qty]))
TotalSold=[Sum(Qty)]


Thanks,

Jessica

I don't have a very clear picture of how you've design your table/data
structure.

Since queries are based on tables (data), could you provide either an
example of the data you have or the table structure, if the field names
are
self-explanatory?

E.g., a "Person" table might hold:

Jeff Boyce 4/1/2000 12345 Elm St. Anywhere ST 99999

and the structure might be:

FName
Lname
DOB
StreetAddr
City
State
PostalCode

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP