473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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'with 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.
Oct 22 '12 #1

✓ answered by TheSmileyCoder

I imported the excel sheet into a mdb, and named the table the same as you indicated in your original post.

I added an extra field to the table RLastQty and then used the following code to calculate the field:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CalculateLast()
  2.    Dim i As Integer
  3.    Dim varFieldValue As Variant
  4.    Dim strFieldName As String
  5.  
  6.    'Open Recordset
  7.       Dim rs As DAO.Recordset
  8.       Set rs = CurrentDb.OpenRecordset("tblItemOrdQty", dbOpenDynaset)
  9.       If rs.EOF Then GoTo exitSub
  10.  
  11.    'Loop through records
  12.       Do While Not rs.EOF
  13.          'Reset storage
  14.             varFieldValue = Null
  15.  
  16.          'Loop through fields in reverse
  17.             For i = 10 To 0 Step -1
  18.                strFieldName = "r" & Format(i, "00") & " Qty"
  19.                If Not IsNull(rs(strFieldName)) Then
  20.                   varFieldValue = rs(strFieldName)
  21.                   'Found a value, so exit loop
  22.                   Exit For
  23.                End If
  24.             Next
  25.  
  26.          'Write calculated value to field
  27.             rs.Edit
  28.                rs("RLastQty") = varFieldValue
  29.             rs.Update
  30.  
  31.          'Move to next record
  32.          rs.MoveNext
  33.       Loop
  34.  
  35.  
  36. exitSub:
  37.    'Cleanup objects
  38.    rs.Close
  39.    Set rs = Nothing
  40.  
  41.  
  42.  
  43. End Sub
I have included the database sample here.

This calculates it and stores it in the table itself. Again not a normalized approach but since the table itself is not normalized I don't see the harm.

26 6754
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Oct 22 '12 #2
twinnyfo
3,653 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. Item ID | Item Name | Qty | Date
  2.  
  3. 1       | Item1     | 10  | 12-Oct -12
  4. 2       | Item1     | 14  | 13-Oct -12
  5. 3       | Item1     | 14  | 14-Oct -12
  6. 4       | Item2     |  5  | 12-Oct -12
  7. 5       | Item2     |  4  | 15-Oct -12
  8. 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.....
Oct 22 '12 #3
@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.
Oct 22 '12 #4
@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
Oct 22 '12 #5
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.
Oct 22 '12 #6
TheSmileyCoder
2,322 Expert Mod 2GB
Is the amount of Lot fields variable? Do you sometimes have only lot1, lot2 and lot3, and other times have lot1,lot2,.....lot10?
Oct 22 '12 #7
TheSmileyCoder
2,322 Expert Mod 2GB
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?
Oct 22 '12 #8
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.
Oct 22 '12 #9
What is csv?
Not sure what format that is.

Will an excel file do?
Oct 22 '12 #10
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Oct 22 '12 #11
TheSmileyCoder
2,322 Expert Mod 2GB
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 "tblItemOrdQty" overwritten each time you import data, or do you append to the table?
Oct 22 '12 #12
twinnyfo
3,653 Expert Mod 2GB
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.
Oct 22 '12 #13
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
File Type: xlsx Piping_Engg All Rev.xlsx (10.6 KB, 404 views)
Oct 22 '12 #14
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Oct 22 '12 #15
Ok. I will definitely read it.

SmileyCoder.. Thanks a lot. You are very helpful :-)
Oct 22 '12 #16
TheSmileyCoder
2,322 Expert Mod 2GB
I imported the excel sheet into a mdb, and named the table the same as you indicated in your original post.

I added an extra field to the table RLastQty and then used the following code to calculate the field:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CalculateLast()
  2.    Dim i As Integer
  3.    Dim varFieldValue As Variant
  4.    Dim strFieldName As String
  5.  
  6.    'Open Recordset
  7.       Dim rs As DAO.Recordset
  8.       Set rs = CurrentDb.OpenRecordset("tblItemOrdQty", dbOpenDynaset)
  9.       If rs.EOF Then GoTo exitSub
  10.  
  11.    'Loop through records
  12.       Do While Not rs.EOF
  13.          'Reset storage
  14.             varFieldValue = Null
  15.  
  16.          'Loop through fields in reverse
  17.             For i = 10 To 0 Step -1
  18.                strFieldName = "r" & Format(i, "00") & " Qty"
  19.                If Not IsNull(rs(strFieldName)) Then
  20.                   varFieldValue = rs(strFieldName)
  21.                   'Found a value, so exit loop
  22.                   Exit For
  23.                End If
  24.             Next
  25.  
  26.          'Write calculated value to field
  27.             rs.Edit
  28.                rs("RLastQty") = varFieldValue
  29.             rs.Update
  30.  
  31.          'Move to next record
  32.          rs.MoveNext
  33.       Loop
  34.  
  35.  
  36. exitSub:
  37.    'Cleanup objects
  38.    rs.Close
  39.    Set rs = Nothing
  40.  
  41.  
  42.  
  43. End Sub
I have included the database sample here.

This calculates it and stores it in the table itself. Again not a normalized approach but since the table itself is not normalized I don't see the harm.
Attached Files
File Type: zip Lots.zip (25.9 KB, 148 views)
Oct 22 '12 #17
TheSmileyCoder,

My Access Guru....Brilliant!!!
This is exactly what i wanted.

I have one more question. Can i have the same result in a query? In your example you had designed a form with calculate cmd button. I would like to have it as a query with a calculated field, so that i can use this field in another query to calculate the difference between Quantity required and Quantity ordered. That would help me to reach the final objective.

Thanks :-)

Suggest me some good learning materials for this level of Access VB Programming. I am really interested.
Oct 22 '12 #18
TheSmileyCoder
2,322 Expert Mod 2GB
Yes, that is possible as well. I will look into it this evening (if time permits)
Oct 22 '12 #19
Rabbit
12,516 Expert Mod 8TB
This is a function I use to replicate the functionality of coalesce in SQL Server.
Expand|Select|Wrap|Line Numbers
  1. Public Function coalesce(ParamArray inputs() As Variant) As Variant
  2.     Dim item As Variant
  3.     coalesce = Null
  4.     For Each item In inputs
  5.         If Not IsNull(item) Then
  6.             coalesce = item
  7.             Exit Function
  8.         End If
  9.     Next
  10. End Function
It returns the first non-null parameter passed into the function.
Oct 22 '12 #20
TheSmileyCoder
2,322 Expert Mod 2GB
I presume you could just call the function that rabbit has provided and provide the inputs in reverse order.
Expand|Select|Wrap|Line Numbers
  1. LastQty: coalesce([r10 Qty],[r09 Qty]......[r00 qty])
If you are unsure on the basics of using a custom function in a query, check out the link below:
How To Write and use a Custom Function
Oct 22 '12 #21
TheSmileyCoder
2,322 Expert Mod 2GB
enigma19
Suggest me some good learning materials for this level of Access VB Programming. I am really interested.
Well a good starting resource if you are new to programming is actually the "Access VBA Programming For Dummies". That is where I started, and look where I am now :)
Oct 22 '12 #22
@Rabbit

Thank you for the reply.

I am a newbie in Access programming. I really have no idea have to use this piece of code to get the desired outcome.

I will go through the Coalesce function (its new to me) and try to understand the code.
Oct 23 '12 #23
@SmileyCoder

I will go through the video. Thanks.
:-)
Oct 23 '12 #24
TheSmileyCoder
2,322 Expert Mod 2GB
The video should give you all the information you need in order to implement Rabbit's solution & code.

If not, you know where to ask :)
Oct 23 '12 #25
@SmileyCoder

Thank you for the video on using a function in a query.

I have used Rabbits' Coalesce function in the query. It is working great. Very simple solution ( though it took some time to fill in the array input values)

Thank you Guys.

I will get back to you all again for more help in my Access learning :-)

Keep Rocking
Oct 25 '12 #26
TheSmileyCoder
2,322 Expert Mod 2GB
You are welcome.
Oct 25 '12 #27

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Miguelito Bain | last post by:
hi everybody. i'm trying to find out what you can and can't do with/in calculated fields. any help, pointers, and/or advice would be greatly appreciated. i'm a newbie, but i want to learn, and...
2
by: Phil Stanton | last post by:
I have a form of club members with a continuous subform on it showing boat details. I hold the boat length and beam (in meters) in a table. On the subform I also calculate and show the imperial...
5
by: John Bahran | last post by:
I am trying to use calculated fields in my query but all the results are zero ven when they're not. Please help. Thanks.
2
by: John Bahran | last post by:
I get all zeros on my query under the calculated fields. Any ideas? I would appreciate the input. Thanks.
1
by: tconway | last post by:
I have an Access program that displays Customer data into a form and OrderID data into a subform. The totals in the Subform are based on calculated fields, i.e. the Total Amount field Calculates...
3
by: Mr. California | last post by:
I'm still a little new to Access, and I'm sure I'm missing some key piece of information, but I can boil it all down to this: I have an Employee table. Two of the fields are Empl_First...
3
by: gullguten | last post by:
Hello folks. I'm new in this forum, but I have not found my question answered anywhere here so I guess it's OK for me to post it. I have the following case. Table av_id av_ip ...
0
by: jacquesvgreunen | last post by:
Does anyone know of any limitations on Calculated fields in a select query? Thanx
3
NatronA111
by: NatronA111 | last post by:
I am having a problem with calculated fields (...the most important one in the database!) in an MS Access query that pulls info from one table and a few other calculated fields stored in other...
0
by: notmoonlighting | last post by:
I am new to Access 2007, a former FileMaker Pro database user. I am trying to replicate some functionality that was available in the FM DB, which involved Calculated Fields in one of the Tables. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.