How to implement LIFO and FIFO method

[anantabd]

Dear all i am using ms access database and VB in front end. How i will implement FIFO and LIFO method in inventory. Please give me the database structure and logical process.

[kkelly]

If you use an Autonumber field as the primary key in a table you could retrieve the first and last record either with the first and last aggregate functions or the min and max functions on the primary key. Like:

SELECT FIRST(ID)
FROM myTable
ORDER BY FIRST(ID);

or

SELECT MIN(ID)
FROM myTable;

If your database requires a time/date stamp then the min and max functions on the time/date field would work. Like:

SELECT MIN(DateTimeField)
FROM myTable;

Note that the above SQL only returns the value of a single field.

Another way you could write the SQL to return more than just a single field is with the TOP function. To get the first record:

SELECT TOP 1 *
FROM myTable
ORDER BY ID;

To get the last record:

SELECT TOP 1 *
FROM myTable
ORDER BY DateTimeField DESC;

You could make two queries. One called GetFirstAsset and one call GetLastAsset, then execute these queries (Stored Procedures) from the Visual Basic code.

[kkelly]

Also, I just wanted to mention that it is best to avoid compound SQL statements. A well structured database really has no need for them, and they can be prone to SQL injection.