Prev: Access database job: how much money can I ask?
Next: Export 3 Page Report To Powerpoint
From: Poul Erik on 23 Mar 2010 13:04
Hallo

Is there someone here who could help me?
I don�t anything about access, but I need a template which I could use
when I buy / sell parts.
I also should know how many of the parts I have in the warehouse.

I don�t know if I should use three tables one for both buying and
selling parts, and one for keeping me updated on how many of these
parts is in the warehouse.

The information I need in the table is Date, Ware description,
Quantity, Price each, Costs, Total,

Poul

--

From: Salad on 23 Mar 2010 14:43
Poul Erik wrote:

> Hallo
>
> Is there someone here who could help me?
> I don�t anything about access, but I need a template which I could use
> when I buy / sell parts.
> I also should know how many of the parts I have in the warehouse.
>
> I don�t know if I should use three tables one for both buying and
> selling parts, and one for keeping me updated on how many of these
> parts is in the warehouse.
>
> The information I need in the table is Date, Ware description,
> Quantity, Price each, Costs, Total,
>
> Poul
>
When you open Access you can open a database or you can select a
template. There are Order Mgt and Inventory Mgt database templates you
can dl from MS.

You could build Excel files and then import (File/GetExternalData/Import
or Link) them. Import you can modify the data, link is Read-only.

If you don't know anything about Access, you might want to consider
hiring someone that can put it together. Maybe use CraigsList under
computer gigs.

From: Roger on 23 Mar 2010 15:45
On Mar 23, 11:04 am, "Poul Erik" <> wrote:
> Hallo
>
> Is there someone here who could help me?
> I don’t anything about access, but I need a template which I could use
> when I buy / sell parts.
> I also should know how many of the parts I have in the warehouse.
>
> I don’t know if I should use three tables one for both buying and
> selling parts, and one for keeping me updated on how many of these
> parts is in the warehouse.
>
> The information I need in the table is Date, Ware description,
> Quantity, Price each, Costs, Total,
>
> Poul
>
> --

I would use the following tables, not need to capture totals since it
can be
calculated using a query (or two)
tblItem
itemId
description
qtyOnHand

tblItemTransaction
transactionId (autonumber)
itemId
transType (S - sale, P - purchase, A - adjustment)
transDate
transQty
transCost
transPrice

qryTransQty
-----------
select itemId
, sum(IIf(transType = 'S', -transQty,
IIf(transType = 'P', transQty,
transQty) as tQty
from tblItemTransaction
group by itemId


qtyQtyOnHand
------------
select tblItem.itemId
, tblItem.qtyOnHand + qryTransQty.tQty as onHand
from tblItem left join
qryTransQty on tblItem.itemId = qryTransQty.itemId

the reason, tblItem as qtyOnHand, it allows you to flush transactions
out of tblItemTransaction and store the next quantity as qtyOnHand

of course, the flushing activity needs to deal with costing, be it
average cost, fifo cost, last cost,....
From: "Poul" givskov on 24 Mar 2010 12:06
Roger wrote:

>
> I would use the following tables, not need to capture totals since it
> can be
> calculated using a query (or two)
> tblItem
> itemId
> description
> qtyOnHand
>
> tblItemTransaction
> transactionId (autonumber)
> itemId
> transType (S - sale, P - purchase, A - adjustment)
> transDate
> transQty
> transCost
> transPrice
>
> qryTransQty
> -----------
> select itemId
> , sum(IIf(transType = 'S', -transQty,
> IIf(transType = 'P', transQty,
> transQty) as tQty
> from tblItemTransaction
> group by itemId
>
>
> qtyQtyOnHand
> ------------
> select tblItem.itemId
> , tblItem.qtyOnHand + qryTransQty.tQty as onHand
> from tblItem left join
> qryTransQty on tblItem.itemId = qryTransQty.itemId
>
> the reason, tblItem as qtyOnHand, it allows you to flush transactions
> out of tblItemTransaction and store the next quantity as qtyOnHand
>
> of course, the flushing activity needs to deal with costing, be it
> average cost, fifo cost, last cost,....


Hallo Roger and thank you.

But as I write I'm totaly novice on ms-access, I bought a little book
for beginner, but there was nothing i could use in this execpt for some
simple tables, query and reports.

I know it is much to ask about, but is it possibly you could put your
information in a db and mail it to me for analysing.

Poul
givskov(nospam)@adslhome.dk
--

From: "Poul" givskov on 26 Mar 2010 01:57
Roger wrote:

>
> I would use the following tables, not need to capture totals since it
> can be
> calculated using a query (or two)
> tblItem
> itemId
> description
> qtyOnHand
>
> tblItemTransaction
> transactionId (autonumber)
> itemId
> transType (S - sale, P - purchase, A - adjustment)
> transDate
> transQty
> transCost
> transPrice
>
> qryTransQty
> -----------
> select itemId
> , sum(IIf(transType = 'S', -transQty,
> IIf(transType = 'P', transQty,
> transQty) as tQty
> from tblItemTransaction
> group by itemId
>
>
> qtyQtyOnHand
> ------------
> select tblItem.itemId
> , tblItem.qtyOnHand + qryTransQty.tQty as onHand
> from tblItem left join
> qryTransQty on tblItem.itemId = qryTransQty.itemId
>
> the reason, tblItem as qtyOnHand, it allows you to flush transactions
> out of tblItemTransaction and store the next quantity as qtyOnHand
>
> of course, the flushing activity needs to deal with costing, be it
> average cost, fifo cost, last cost,....

Hello Roger
As I write I'm totaly newbee in access, is the first two parts tables,
and the last tow sections is this queryes?

Poul

Is this table one?

tblItem
> itemId
> description
> qtyOnHand
--

and this table two?

> tblItemTransaction
> transactionId (autonumber)
> itemId
> transType (S - sale, P - purchase, A - adjustment)
> transDate
> transQty
> transCost
> transPrice

 |  Next  |  Last
Pages: 1 2
Prev: Access database job: how much money can I ask?
Next: Export 3 Page Report To Powerpoint