looping through recordsets for calculated fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • enigma19
    New Member
    • Oct 2012
    • 12

    looping through recordsets for calculated fields

    Hi ,

    I need help to solve this problem

    Problem definition:

    I have a table 'tblItemOrdQty' with item name and its order quantities (Lot1,Lot2,Lot3 .... etc). Each items are ordered in different lots. Some are ordered as single lot.. some in two lots.. some in three lots etc.
    My objective is to have a new column ( a calculated field) with the value of latest order quantity for each item.

    Eg.

    Item ID | Item Name | Lot1 | Lot2 | Lot3| Latest Qty (calc.field)

    1 | Item1 | 10 | 11 | 14 |14
    2 | Item2 | 5 | 4 | null | 4
    3 | Item3 |20 | null | null |20


    I have made a query 'ItemOrdQuery'w ith Item ID, Item name, Lot 1 , Lot 2, Lot 3. I need to know how to build expression for the Latest Qty calculated field for my desired objective.

    Is there any way it can be done using SQL or VBA program using recordsets?

    Waiting for your reply.

    Thank you for your time.
  • TheSmileyCoder
    Recognized Expert Moderator Top Contributor
    • Dec 2009
    • 2322

    #2
    First off, I have to ask you, has your database been properly normalized.
    From the example data you have given me, I would seem to guess that it is not the case. Database normalization shall always be the first step in designing the a database.

    Comment

    • twinnyfo
      Recognized Expert Moderator Specialist
      • Nov 2011
      • 3653

      #3
      Enigma,

      For the fields Lot1, Lot2 and Lot3, will each Item Name eventually have all three lots ordered? If not, then, to echo Smiley above, you should probably look at normalizing your db.

      Then you would have a table like this:

      Code:
      Item ID | Item Name | Qty | Date
      
      1       | Item1     | 10  | 12-Oct -12
      2       | Item1     | 14  | 13-Oct -12
      3       | Item1     | 14  | 14-Oct -12
      4       | Item2     |  5  | 12-Oct -12
      5       | Item2     |  4  | 15-Oct -12
      6       | Item3     | 20  | 12-Oct -12
      Then, when you want the latest quantity ordered for an Item, you query, based on the Item (which should be normalized in another table with ItemID | ItemName), and use the criteria of Max(Date).

      Hope this hepps.....

      Comment

      • enigma19
        New Member
        • Oct 2012
        • 12

        #4
        @Smiley Coder

        I recieve this table in this format from another team( Procurement department) and the data is huge around 450-500 items and around 10 lot numbers ( Lot 1 , Lot2 , Lot3.. .. Lot10). it is difficult for me to split the data into different tables.I just import into my database and want to use it as such. That is the reason i am trying to find a alternate method to get this done.

        I want to know how can the latest order Qty be displayed in the Calculated filed.

        Comment

        • enigma19
          New Member
          • Oct 2012
          • 12

          #5
          @twinnyfo

          Some items may have all 3 lots ordered and some may have only 1 till the end. All other column value will be null in this case

          Comment

          • enigma19
            New Member
            • Oct 2012
            • 12

            #6
            What i am looking for is to check each field from Lot1 to Lot 3 ( from left to right) if it is not null, when true it has to move to the next field till end of field (EOF). When false it has to return the value of the previous field (non null).

            Or.

            Check the fields from right to left ( Lot 3 to Lot 1) and then return the first non null value.

            Anyway would do. But i am just a beginner in Access. Not sure how to get this done programatically in SQl or VBA. It has something to do with recordsets.

            Help me to move forward in this direction.

            Comment

            • TheSmileyCoder
              Recognized Expert Moderator Top Contributor
              • Dec 2009
              • 2322

              #7
              Is the amount of Lot fields variable? Do you sometimes have only lot1, lot2 and lot3, and other times have lot1,lot2,..... lot10?

              Comment

              • TheSmileyCoder
                Recognized Expert Moderator Top Contributor
                • Dec 2009
                • 2322

                #8
                I could probably pop some code together for you in 5 minutes, but it would be ALOT easier for me, if you could provide a csv file of the table. Would that be possible?

                Comment

                • enigma19
                  New Member
                  • Oct 2012
                  • 12

                  #9
                  No. The lot fields are not variable. In order to simplfy i gave lot1 - Lot 3. I will fix the max lot number at Lot 10.It would be fixed number of fields.

                  Comment

                  • enigma19
                    New Member
                    • Oct 2012
                    • 12

                    #10
                    What is csv?
                    Not sure what format that is.

                    Will an excel file do?
                    Last edited by TheSmileyCoder; Oct 22 '12, 11:57 AM.

                    Comment

                    • TheSmileyCoder
                      Recognized Expert Moderator Top Contributor
                      • Dec 2009
                      • 2322

                      #11
                      CSV:Comma Seperated File. Access can export a table as such, and Excel can save as a CSV file as well.
                      It is similar to a text file.

                      The main reason for preferring a CSV is that (as far as I know) a CSV cannot contain virus/macros, whereas an excel file can.

                      Comment

                      • TheSmileyCoder
                        Recognized Expert Moderator Top Contributor
                        • Dec 2009
                        • 2322

                        #12
                        I would still recommend if this information is something you need to use at a later point in time, that you spend time on normalizing the data.

                        If this is just something you need to process, create a report or similar, and then toss the original data, then its ok to use non-normalized data.

                        Is the table "tblItemOrd Qty" overwritten each time you import data, or do you append to the table?

                        Comment

                        • twinnyfo
                          Recognized Expert Moderator Specialist
                          • Nov 2011
                          • 3653

                          #13
                          I agree with Smiley and normalizing. If you get the data in an excel format, it should be pretty easy to import that data into a normalized state.

                          Comment

                          • enigma19
                            New Member
                            • Oct 2012
                            • 12

                            #14
                            The table is overwritten each time.

                            I am using Excel 2007. I am not having CSV option when i do save as. I am attaching the excel file
                            Attached Files

                            Comment

                            • TheSmileyCoder
                              Recognized Expert Moderator Top Contributor
                              • Dec 2009
                              • 2322

                              #15
                              I will look at writing a piece of code for it.

                              In the meantime, if you are not familiar with normalization, I suggest you read the link I provided in post #2.
                              It is quite good.

                              Comment

                              Working...