By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

looping through recordsets for calculated fields

P: 12
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.

Share this Question
Share on Google+
26 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Expert Mod 2.5K+
P: 3,035
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

P: 12
@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

P: 12
@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

P: 12
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
Expert Mod 100+
P: 2,321
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
Expert Mod 100+
P: 2,321
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

P: 12
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

P: 12
What is csv?
Not sure what format that is.

Will an excel file do?
Oct 22 '12 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Expert Mod 100+
P: 2,321
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
Expert Mod 2.5K+
P: 3,035
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

P: 12
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, 325 views)
Oct 22 '12 #14

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

P: 12
Ok. I will definitely read it.

SmileyCoder.. Thanks a lot. You are very helpful :-)
Oct 22 '12 #16

TheSmileyCoder
Expert Mod 100+
P: 2,321
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, 98 views)
Oct 22 '12 #17

P: 12
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
Expert Mod 100+
P: 2,321
Yes, that is possible as well. I will look into it this evening (if time permits)
Oct 22 '12 #19

Rabbit
Expert Mod 10K+
P: 12,315
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
Expert Mod 100+
P: 2,321
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
Expert Mod 100+
P: 2,321
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

P: 12
@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

P: 12
@SmileyCoder

I will go through the video. Thanks.
:-)
Oct 23 '12 #24

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

P: 12
@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
Expert Mod 100+
P: 2,321
You are welcome.
Oct 25 '12 #27

Post your reply

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