Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 851

# How to write a FIFO query??

Hi all,

I was wondering how to do FIFO using query.
Well, my problem is as below:
i have a list of IN n Out numbers:
IN -> 2,0,1,5
OUT-> 0,1,0,2
Balance (Sum of IN - Sum of OUT)
-> 2,1,2,5

i want it to be displayed as the calculation below:
I O   B    Output
2 0 = 2 -> 2
0 1 = 1 -> 0 1
1 0 = 2 -> 1 0 1
5 2 = 5 -> 5 0 0 0

the last line is derived from
5 ins and 2 outs...where the 1 0 1 from the previous line are brought down. So, using the FIFO concept. The 1-2=-1, the -1 is brought foward to the next column, 0-1=-1 and to the next column 1-1=0. So, calculation stopped.

So, how can i do a loopback queries, so that if there is a negative number after the difference, it'll will auto bring down to the next column?

thanks a lot.
0
glian
1 Solution

Commented:
glian

I understand FIFO
I don't understand how you derive the output column
What you refer to as balance Balance (Sum of IN - Sum of OUT) is called RunningSum in access reports.

2 in, 0 Out = Balance of 2, Output 2
0 in, 1 Out = balance of 1, Output 01
???? where did zero come from

1 in, 0 out = balance 0f 2, Output 101

Can you please clarify how 101 is derived.

Alan
0

Author Commented:
Hi Alan,

Ok, the scenario of my problem is this. I'm trying to do some inventory control with the data below. I have stored the product number, date, in and out on a same table.
IN -> 2,0,1,5
OUT-> 0,1,0,2

The balance is obtained by the calculation of
Sum of IN - Sum of OUT (both to date)
-> 2,1,2,5

i want it to be displayed as the calculation below:
I O   B    Output(Aging Per Day)
2 0 = 2 -> 2
0 1 = 1 -> 0 1
1 0 = 2 -> 1 0 1
5 2 = 5 -> 5 0 0 0

So, the first IN is 2, thus the output on Day 1 display 2.
2 0 = 2

Then there is 1 OUT and 0 IN for Day 2. Apply the FIFO concept here, 0 is display in the Day 1 column while 1 inventory is deducted from the previous 2.
0 1 = 0 2-1=1

And it goes for Day 3, with 1 IN and 0 OUT. So, the new IN goes to the Day 1 queue and the rest is aged.
1 0 = 1 0 1

Finally, the Day 4, there are 5 INs and 2 OUTs. So, 5 is queue on Day 1 and 2 of the inventories are deducted from the leftover inventories according to the oldest in stock.
5 2 = 5 1-1=0 0 1-1=0
= 5 0 0

Well, hope this helps.

0

Commented:
hi glian

i think your question is interesting, but i see no possibility of doing that in a query

for my understanding one could formulate the question that way:

I,O     are base data
B       is the running sum
Output  is the structure of B

for my opinion Output should be a string, because it is only a additional information about the inner structure of the actual inventory B. Furthermore I can see no reason for the 0-values. If you have 100 days, you will get 100 0-Values or if you have a 100 days old inventory and no demand you have 99 0-values and one positive value at the end ?
If you agree on output beeing a info-string, I would sugest a function doing the string-manipulation, wich you can use within the query.
0

Commented:
Hi glian,
This question has been abandoned and needs to be finalized (94 days since last comment).

You can accept an answer, split the points, or get a refund. Go to
http://www.cityofangels.com/Experts/Closing.htm for information and options.