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********@hotm ail.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********@hot mail.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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******@pcdata sheet.com
www.pcdatasheet.com

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

<pi********@hot mail.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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_******** ******@newsread 3.news.atl.eart hlink.net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.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******@pcdata sheet.com
www.pcdatasheet.com

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

"StopThisAdvert ising" <StopThisAdvert ising@DataShit> wrote in message
news:43******** **************@ text.nova.plane t.nl...

"PC Datasheet" <no****@nospam. spam> schreef in bericht
news:u_******** ******@newsread 3.news.atl.eart hlink.net...

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.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******** ********@newsre ad3.news.atl.ea rthlink.net...

[Tim Marshall]

pi********@hotm ail.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