Fifo batch allocation

[haroon.mansha]

Hi all

There no idea coming to my mind to how to perform me. That is what I need.

Suppose I have purchase table with following data

Batch no. Quantity. Date.
1001. 100. 01/01/2014
1002. 50. 02/01/2014

Now supposingly sales as follows

Date. Quantity invoice no

05/01/2014. 125 1
10/01/3014. 25. 2


Now the batch reports result I need as follows

Sales. Invoice no 1 100. Batch 1001
25. Batch. 1002

Invoice no 2. 25. Batch 1002

Thanks in advance

 

[vbaInet]

What's the meaning of "Fifo" in this context? Fifo normally means first-in first-out.

The tables aren't well presented.
And what links the Purchase table with the Sales table?

 

[haroon.mansha]

Fifo desired for report for batch means first quantity out of first purchase.

Tables are linked to maintain stock means total sales - total purchased = stock

 

[vbaInet]

I see, I don't know that lingo. First-in first-out is a data structure in programming.

You want to have a query based on the two tables, so my question is what field(s) links those two tables?

 

[haroon.mansha]

Apparently no link as each have its own calculations.

 

[vbaInet]

How do you hope to achieve this without some sort of relationship?

 

[haroon.mansha]

This is exactly what I need but I failed to understand the logic
access-programmers.co.uk/forums/showthread.php?t=153802

 

[vbaInet]

If it explains the entire logic there then you should follow it.

Otherwise, clearly show sample records.

 

[haroon.mansha]

The records are from two different tables or can be from one table. In fact if you download this file and check.he made make relations for query if possible I need to know only fifo batch report

 

[vbaInet]

Unfortunately no link was included haroon. Can you show the sample records in one Excel sheet in this format:

1. Table 1
2. Table 2
3. Expected result.

 

[haroon.mansha]

Thanks for helping. Pls find the attached file. Infact orally for small data I can understand the way but for large set no idea.
Pls find the link to download
https://www.dropbox.com/s/7malgcn5gdy8bp2/Fifo by sales.xlsx

 

[haroon.mansha]

Dear can you help me

 

[jdraw]

Just to say I agree with vbaInet --fifo FirstIn FirstOut is a technique to ensure older stock is moved first.

I recall a post on FIFO here.

Good luck.

 

[vbaInet]

@jdraw: I think he's trying to understand the logic in post #7. Maybe your post will help explain the process better.

@haroon: Have you tried replicating what's done in the db? That's doing it one by one. You can't understand it by just looking at it, you need to put the writing of the queries into practice then perhaps it will make sense.

 

[haroon.mansha]

I will look them one by one and will try to understand . After going I will share my experience .
But I am thankful for helping me

 

[vbaInet]

If there's a particular query you don't understand, give us a shout. I think there might be a quicker way, I just haven't had time to look at it properly, but I will later.

 

[haroon.mansha]

I will .even I will put some real data and will share file

 

[haroon.mansha]

I have checked the sample database but I need the reverse means .the file shows the closing stock from batches with price while I need only the sold with batch no

 

[haroon.mansha]

@jdraw can you give idea that I need sold quantity batch reference

@vbalnet if you find time I hope you can guide me

 

[vbaInet]

Hopefully but just not yet. I need to understand how FIFO works in this context and that will be time consuming so perhaps later.