date & quantity query help

[IngridN]

Hi all,

I need help with what I think is a relatively simple DB. I'm creating a DB with 2 tables:

1) Inventory Balance with the following fields:
*Month
*FiFo Date
*Article number
*Quantity

2) Transactions with the following fields:
*Month
*FiFo Date
*Article number
*Adjustments qty (positive or negative)
*Receipts qty
*Invoice Create qty
*Invoice Void qty (negative)

I've added the month so I can filter any given month.What I want is a query that will calculate my closing balance (in qty) following the FIFO method and I haven't got a clue how to start.

Can someone help?

Thanks,

Ingrid

 

[flixx]

Just a thought:
You might want to give this concept a try, Start by naming the
Inventory Balance table - tblinb-Inventory Balance
the add a field and call it inb-id , mke this a key field with no duplicates
this is your one to many relationship or one to one works for both.
Field names for the other Inventory Balance Table are
inb-id ID Identifier
inb-mon Month
inb-fdat FiFo Date
inb-arn Aerticle Number
Quanity is not needed in table because it can change brcause of updates
1) Inventory Balance with the following fields:
*Month
*FiFo Date
*Article number
*Quantity
Now create your Transaction table name it tbltran-Transactions
field names
tran-id ID Identifier key Field no Duplicates
tran-inb-id ID Identifier Key Field no Duplicates Lonk to tblinb
tran-adqy Adjustment Quanity
tran-rcqy Reciepts Quanity
tran-icqy Invoice Create Quanity
tran-ivqy Invoice Void Quanity
2) Transactions with the following fields:
*Month
*FiFo Date
*Article number
*Adjustments qty (positive or negative)
*Receipts qty
*Invoice Create qty
*Invoice Void qty (negative)
Notice i left out the first 3 fields since they have the same data as the
Inventory Table. You will use querys and forms reports to generate and
control data flow. This is just a thought hope it helps you get started in the
right direction, if you need more help just post or email me. Flixx.