return value function

[DaveCrate]

return the value
The query only returns the dates where sales have occurred.
I want to be able to the sale pattern for the entire year.

column A is a date (every day of year)
column B is a date (only days that a sale occured)
column c sale amount corresponding to date in column B

a b c d
05/23/2006 05/22/2006 5 0
05/24/2006 05/24/2006 10 10

if a (date) is = b (range of dates) return the value of c in column d else "0"

column b is currently a list of only the dates where asomething was sold

Can anyone help populate the column d with either the sale amount or 0

Thanks

 

[SkipVought]

Dave,

WHY have a column that has every date in the year? It makes no sense.

Delete Col A, sort col B.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[DaveCrate]

Forgot about column e, which is what the customer would consume on a daily basis.

Basically, what I'm trying to do is keep tabs on the customers inventory from a starting point. Say they started w/ 25. Tracking there usage, my sales to them , and there future orders. What would the inventory look like.

A running invnetory

 

[SkipVought]

Inventory is a record of various quantities at a point in time; ie On-hand, In-Transit, On-Dock etc.

To maintain the inventory, you could have a table of transactions that would be a record of Puts and Takes from Inventory -- a +/- Quantity, Trans Type Trans Date, for instance.

It's not a good idea to mix the two, IMHO.

Please post a sample of your data along with field headings.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[DaveCrate]

beginning
inventory daily usage delivered invnetory
from me at customer
5/23/2006 110
5/24/2006 20 0 90
5/25/2006 20 0 70
5/26/2006 20 24 74
5/27/2006 0 0 74
5/28/2006 20 0 54
5/29/2006 20 24 58

 

[SkipVought]

You need to read up on Inventory Management principles. Check out

http://www.apics.org/default.htm

You have a DEMAND (daily usage) forecast, and SUPPLY that can be controlled by an order policy that includes a replenishment quantity and the number of days required to respond to a replenishment flag. You need to have your system notify you when YOU need to fabricate/manufacture/procure stuff a lead time away from the replenishment signal from the customer so you'll have to to deliver.

Anyhow, your formula is
[tt]
=PrevOnHand-Daily_Usage+Delivered
[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[DaveCrate]

I'll give it a try and let you know
Thanks for all of your help thus far
I might need it some more though