Jump to content





Recent Status Updates

  • Photo
      16 Apr
    Kadence

    If you're reading this, you're on my profile and I know you're on my profile because I'm probably viewing yours.

    Show comments (6)
  • Photo
      10 Apr
    Poe

    Finally (and hopefully) i'm getting a team together that knows a little of this and a little of that; and maybe all my open source projects that are half written can begin to be released. :)

View All Updates
Photo
- - - - -

How to implement LIFO and FIFO method


  • Please log in to reply
2 replies to this topic

#1 anantabd

anantabd

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 11 October 2007 - 08:18 AM

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.
  • 0

#2 kkelly

kkelly

    CC Regular

  • Just Joined
  • PipPipPip
  • 46 posts

Posted 11 October 2007 - 03:46 PM

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.
  • 0

#3 kkelly

kkelly

    CC Regular

  • Just Joined
  • PipPipPip
  • 46 posts

Posted 11 October 2007 - 03:50 PM

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.
  • 0