How to write a FIFO query??

Posted on 2003-03-13
Medium Priority
Last Modified: 2007-12-19
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.
Question by:glian
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 26

Accepted Solution

Alan Warren earned 750 total points
ID: 8134219

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.


Author Comment

ID: 8146310
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.

LVL 18

Expert Comment

ID: 8184723
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.
LVL 12

Expert Comment

ID: 8785705
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.


EE Cleanup Volunteer for Microsoft Access

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question