Prev: Access database job: how much money can I ask?
Next: Export 3 Page Report To Powerpoint
From: Roger on 26 Mar 2010 05:04
On Mar 25, 11:57 pm, "Poul" <givskov(nospam)@adslhome.dk > wrote:
> 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- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

yes to all your questions
From: "Poul" givskov on 27 Mar 2010 04:35
Roger wrote:
>
> yes to all your questions

Thank you Roger you have been at great help.

Poul

--

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