looping through recordsets for calculated fields

[enigma19]

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]

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.

[twinnyfo]

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:

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

[enigma19]

@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.

[enigma19]

@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

[enigma19]

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.

[TheSmileyCoder]

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

[TheSmileyCoder]

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?

[enigma19]

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.

[enigma19]

What is csv?
Not sure what format that is.

Will an excel file do?

[TheSmileyCoder]

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.

[TheSmileyCoder]

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?

[twinnyfo]

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.

[enigma19]

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

[TheSmileyCoder]

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.