Single item inventory?

[pietlinden]

Hi,
Stupid question, but here goes.

Someone wants me to design a database to keep track of fuel deliveries
and orders/shipments. Essentially, he'll purchase fuel from some
supplier and then resell it. I'm assuming this is a generic product, so
I would have something like a debit/credit system. Deliveries increase
stock and decrease cash, and sales do the opposite. So is this as
simple as,,,

tblStockChange
----------------------
TransactionID
TransactionType (Purchase/Sale/WriteOff) --- writeoff for lost fuel or
something like that(?)
TransactionDate
FuelAmount
PricePerUnit
ClientID

Or would it be better to have one table for sales and another for
purchases? (Probably so).
If I did that, how would I get a running total of fuel on hand by date?
(see Allen's website for his QuantityOnHand demo?) Thinking about it,
I should probably just make the Sales and Purchases tables union
compatible and then do something like a report doing a running total of
the union query. Of course, I'd have to multiply stock by -1 for
outflows.

Am I on the right track or completely clueless?
(Umm... we don't know... what have you tried so far? has it worked? If
not, what's not working that you expect to be? Where's your code? Your
report design?)

thanks,
Pieter

[rkc]

pi********@hotmail.com wrote:

<snip>

Am I on the right track or completely clueless?
(Umm... we don't know... what have you tried so far? has it worked? If
not, what's not working that you expect to be? Where's your code? Your
report design?)

I think where's your report design is the right question to ask. I don't
know how you can possibly know what data you need to store or in what
form you need to store it if someone hasn't thought about what you need
to see on a page as an end result.

[John Welch]

I can't see any reason to have separate tables for sales and purchases. With
what you've said, the fields will be pretty much the same for both, so there
won't be any wasted space or anything. It seems a lot easier to separate the
data with queries than to put together different tables.
my 2 cents
<pi********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi,
Stupid question, but here goes.

Someone wants me to design a database to keep track of fuel deliveries
and orders/shipments. Essentially, he'll purchase fuel from some
supplier and then resell it. I'm assuming this is a generic product, so
I would have something like a debit/credit system. Deliveries increase
stock and decrease cash, and sales do the opposite. So is this as
simple as,,,

tblStockChange
----------------------
TransactionID
TransactionType (Purchase/Sale/WriteOff) --- writeoff for lost fuel or
something like that(?)
TransactionDate
FuelAmount
PricePerUnit
ClientID

Or would it be better to have one table for sales and another for
purchases? (Probably so).
If I did that, how would I get a running total of fuel on hand by date?
(see Allen's website for his QuantityOnHand demo?) Thinking about it,
I should probably just make the Sales and Purchases tables union
compatible and then do something like a report doing a running total of
the union query. Of course, I'd have to multiply stock by -1 for
outflows.

Am I on the right track or completely clueless?
(Umm... we don't know... what have you tried so far? has it worked? If
not, what's not working that you expect to be? Where's your code? Your
report design?)

thanks,
Pieter

[pietlinden]

that's what I was thinking. Then I'd have a report that did a running
sum, which would tell me the balance in my cash account and in my fuel
"account".

[PC Datasheet]

Pieter,

I used to work for a large oil company. You say single item inventory; are
you sure??

Look at a gas station. There are usually three different octane gasolines.
Each is a different product. Then there is Kerosene and it comes in
different grades. There is Diesel Fuel for the big trucks in different
grades and there is home heating oil in different grades. There is aviation
fuel and the list goes on. Just suggesting that maybe you do not have a
single item inventory after all.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

<pi********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi,
Stupid question, but here goes.

Someone wants me to design a database to keep track of fuel deliveries
and orders/shipments. Essentially, he'll purchase fuel from some
supplier and then resell it. I'm assuming this is a generic product, so
I would have something like a debit/credit system. Deliveries increase
stock and decrease cash, and sales do the opposite. So is this as
simple as,,,

tblStockChange
----------------------
TransactionID
TransactionType (Purchase/Sale/WriteOff) --- writeoff for lost fuel or
something like that(?)
TransactionDate
FuelAmount
PricePerUnit
ClientID

Or would it be better to have one table for sales and another for
purchases? (Probably so).
If I did that, how would I get a running total of fuel on hand by date?
(see Allen's website for his QuantityOnHand demo?) Thinking about it,
I should probably just make the Sales and Purchases tables union
compatible and then do something like a report doing a running total of
the union query. Of course, I'd have to multiply stock by -1 for
outflows.

Am I on the right track or completely clueless?
(Umm... we don't know... what have you tried so far? has it worked? If
not, what's not working that you expect to be? Where's your code? Your
report design?)

thanks,
Pieter

[pietlinden]

Good point. But then the design would just become more like a normal
DB.

The more I think about it, the more I think I should make separate
"sale" and "purchase" tables and just make them union-compatible. Then
in the query, just do the math to adjust the sales to be "plus cash,
minus fuel" and the purchases to be the reverse. Then I need to just
add a field for fuel type, because as you say, diesel fuel and airplane
fuel are not interchangeable.

Bear with me - I'm still teasing the complexity of this thing out. So
this represents my somewhat simple understanding of the whole thing.

Maybe I'm better off splitting the two tables and then I can have
normal "Supplier" and "Customer" tables and their related "Purchase
Order" and "Invoice" tables. Then everything follows a pretty much
standard design.

Purchased [X] qty/volume of [product] on [date] from [vendor] at
[price]

Sold [X] qty/volume of [product] on [date] to [Customer] at [price]

Then as long as my tables are reasonably union compatible, I can get a
normal running sum.

Thanks for the input!
Pieter

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:u_**************@newsread3.news.atl.earthlink .net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

Steve is a notorious job hunter here, always trying to sell his services.
But before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[PC Datasheet]

Why don't you take Salad's advise and grow up and get a life and quit
sending emails to my office.

"If you have anything to contribute to the group, contribute. Your war
with Datasheet is simply pissing and moaning in the wind for the sake of
nothing. Grow up and get a life."

Salad

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:43**********************@text.nova.planet.nl. ..

"PC Datasheet" <no****@nospam.spam> schreef in bericht
news:u_**************@newsread3.news.atl.earthlink .net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

Steve is a notorious job hunter here, always trying to sell his services.
But before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[Dissatisified Customer]

get ready for the storm
"PC Datasheet" <no****@nospam.spam> wrote in message
news:jO****************@newsread3.news.atl.earthli nk.net...

[Tim Marshall]

pi********@hotmail.com wrote:

The more I think about it, the more I think I should make separate
"sale" and "purchase" tables and just make them union-compatible. Then

I wouldn't. The question is, what is the data entity here? It's the
inventory. As Steve says, that inventory is not likely one item, but
regardless, whether or not it is one generic fuel or various types, the
nature of the types of inventory has no real bearing on the nature of
the data entity you're considering.

A sale takes from the inventory, a purchase adds to the inventory, ie,
they are both the same, just one positive, the other negative.

I think it's silly to have two separate tables for this and I'm
perplexed why someone like you who has contributed a lot to the group
would even consider it. 8) Maybe this is a new area for you? Perhaps
there's more to it than I see?

One table to rule them all, one table to find them... etc, etc.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Bob Quintal]

pi********@hotmail.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:

Good point. But then the design would just become more like a
normal DB.

The more I think about it, the more I think I should make
separate "sale" and "purchase" tables and just make them
union-compatible. Then in the query, just do the math to
adjust the sales to be "plus cash, minus fuel" and the
purchases to be the reverse. Then I need to just add a field
for fuel type, because as you say, diesel fuel and airplane
fuel are not interchangeable.

Bear with me - I'm still teasing the complexity of this thing
out. So this represents my somewhat simple understanding of
the whole thing.

Maybe I'm better off splitting the two tables and then I can
have normal "Supplier" and "Customer" tables and their related
"Purchase Order" and "Invoice" tables. Then everything
follows a pretty much standard design.

No, I've been doing inventory transaction databases since the
1970s. your original concept of a single table for incoming and
outgoing transactions will prove much more flexible in the long
run. All you need if you have to handle multipler items is add
one field, ( a foreign key to the items definitions tables)
Then as long as my tables are reasonably union compatible, I
can get a normal running sum.
Where do you put your stock adjustments (returns, spillage,
etc)? It becomes much harder to track true current inventory
with separate tables..
Thanks for the input!
Pieter

--
Bob Quintal

PA is y I've altered my email address.

[pietlinden]

Tim,
here's the _full_ story. Don't ask me about the biz rules - just doing
what I'm told!

There's a small twist to this fuel reseller thing.
Customer purchases coupons (which are redeemed for fuel). In the
"invoice", he specifies a proxy to pick up the fuel. So it's not a
pure purchase/resale thing. What the person who asked for this said
was this:

It's basically to get around corruption in an African country. Person
outside the country buys the coupons (which the recipient/proxy picks
up). The proxy then receives the coupons and can redeem them for fuel.
So the process is something like

1. Company buys fuel shipment.
2. Company issues coupons for sale.
3. Customers outside the country purchase the coupons, specifying a 3rd
party to pick up the coupons.
4. 3rd party then uses the coupons to "purchase" fuel. (Essentially,
the money is converted into coupons and then is spent like money.)

So my guess is this most closely models a gift certificate scenario -
except we're trading fuel.

Okay, so I have a purchase and sale of fuel - no problem. The
weirdness is the intermediate coupon thing. That's what the
intermediate mess was about. Otherwise, it's like a simple account
(Bulk Fuel In, Money Out), (Parcel Fuel Out, Money In). In my case,
though, it's:

Step 1: (Bulk Fuel In, Coupons "Created"),
Step 2: (Coupons Sold, Money In),
Step 3: (Coupons Redeemed, Parcel Fuel Out).

Okay, so the question now is this -
how do I manage the conversion between fuel and coupons? (Sorry, Tim,
blonde moment...)
the rest is just

tblTransaction(
TransactionType Text(1) [P]urchase, [S]ale
Transaction Date,
Volume,
TotalPrice)

Then I'd just query the table to make all sale transactions be
"negative" volumes, and purchases be "negative" sales. So far so good.
But what about the coupons? that's the part I'm having the trouble
with. Essentially, each coupon is supposed to represent some fixed
amount of fuel.

Say I have one table for the movement of fuel (sale/purchase). How do
I store info about Vendors and Customers? I could store them both in
the same field, but then how do I figure out which one I'm relating to?

I guess I'll go play with this idea and see where I get. (Stay off the
sidewalk everybody, I'm driving!)

Thanks for the help!
Pieter

[pietlinden]

Bob,
The transaction part of this database dealing with the fuel is pretty
easy. I put down the crack and it all works great. (Notice to kids -
just say no, or you could end up like me... and you don't want that!)
The part I'm not sure how to deal with is the coupon issue. The
*business* plan is to sell coupons that will be redeemed for fuel. So
the process is:

1. Buy Fuel (+Fuel, -Cash)
2. Sell coupons ( -Coupons (proxy for fuel), +Cash)
3. Redeem coupons ( +Coupons (cancelled), -Fuel)

The problem I'm having trouble with is the Coupons<-->Fuel part. Right
now I'm planning on creating coupons in the AfterInsert event a
FuelPurchase. (that works, although I'm not sure the approach is OK)

So how do I handle the coupon problem?

I guess the whole story might help a little...
Purchaser will buy coupons for fuel online. He will designate a
delegate to pick up the fuel. The delegate will present identification
and pick up the coupon book(s). He can then presnet them at a fuel
station and redeem the coupons for fuel. If this were just fuel for
money, then this would be easier. How do I deal with the coupons
wrinkle?

Thanks!
Pieter

[Bob Quintal]

pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

Bob,
The transaction part of this database dealing with the fuel is
pretty easy. I put down the crack and it all works great.
(Notice to kids - just say no, or you could end up like me...
and you don't want that!) The part I'm not sure how to deal
with is the coupon issue. The *business* plan is to sell
coupons that will be redeemed for fuel. So the process is:

1. Buy Fuel (+Fuel, -Cash)
2. Sell coupons ( -Coupons (proxy for fuel), +Cash)
3. Redeem coupons ( +Coupons (cancelled), -Fuel)

The problem I'm having trouble with is the Coupons<-->Fuel
part. Right now I'm planning on creating coupons in the
AfterInsert event a FuelPurchase. (that works, although I'm
not sure the approach is OK)

So how do I handle the coupon problem?

I guess the whole story might help a little...
Purchaser will buy coupons for fuel online. He will designate
a delegate to pick up the fuel. The delegate will present
identification and pick up the coupon book(s). He can then
presnet them at a fuel station and redeem the coupons for
fuel. If this were just fuel for money, then this would be
easier. How do I deal with the coupons wrinkle?

Thanks!
Pieter

Really, you are dealing with two articles in inventory:
fuel and coupons
1. Buy Fuel (+Fuel, -Cash)
2. Sell coupons ( -Coupons (proxy for fuel), +Cash)
3. Redeem coupons ( +Coupons (cancelled), -Cash equivalent )
4. Pump Gas (-Fuel, +cash equivalent)

so you'll need two inventory transactions when someone exchanges
coupons for fuel.
--
Bob Quintal

PA is y I've altered my email address.

[pietlinden]

Thanks, Bob. As I said, I _knew_ I was missing something! Now it
makes sense... now to see if I can't get the model to work.

Pieter