473,883 Members | 1,668 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

looping through recordsets for calculated fields

12 New Member
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.


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.
Oct 22 '12
26 6819
2,322 Recognized Expert Moderator Top Contributor
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
2,322 Recognized Expert Moderator Top Contributor
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?
Oct 22 '12 #12
3,653 Recognized Expert Moderator Specialist
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
12 New Member
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, 412 views)
Oct 22 '12 #14
2,322 Recognized Expert Moderator Top Contributor
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
12 New Member
Ok. I will definitely read it.

SmileyCoder.. Thanks a lot. You are very helpful :-)
Oct 22 '12 #16
2,322 Recognized Expert Moderator Top Contributor
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
  6.    'Open Recordset
  7.       Dim rs As DAO.Recordset
  8.       Set rs = CurrentDb.OpenRecordset("tblItemOrdQty", dbOpenDynaset)
  9.       If rs.EOF Then GoTo exitSub
  11.    'Loop through records
  12.       Do While Not rs.EOF
  13.          'Reset storage
  14.             varFieldValue = Null
  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
  26.          'Write calculated value to field
  27.             rs.Edit
  28.                rs("RLastQty") = varFieldValue
  29.             rs.Update
  31.          'Move to next record
  32.          rs.MoveNext
  33.       Loop
  36. exitSub:
  37.    'Cleanup objects
  38.    rs.Close
  39.    Set rs = Nothing
  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
12 New Member

My Access Guru....Brillia nt!!!
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
2,322 Recognized Expert Moderator Top Contributor
Yes, that is possible as well. I will look into it this evening (if time permits)
Oct 22 '12 #19
12,516 Recognized Expert Moderator MVP
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

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

Similar topics

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 i've tried a lot before posting. here's my situation. i have a simple form with dates on it. i have an indate and outdate
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 equivalent length and beam. You can enter lengths in either the metric box when an AfterUpdate event shows the imperial eqivalent, or converseley you can enter the lenght in feet and inches in the imperial box and on AfterUpdate the metric...
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.
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.
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 the following: =* The Subform Order fields are pulled from a Query along with each calculated Total. They are displyed in a DataSheet View. This works fine on my developement environment which includes a server set up just as our client has. ...
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 Empl_Last I have a calculated field, which is part of the record source to a report defined as:
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 av_datetime This table records every presentation of any articles in my system. The field holds the date and time like yyyymmddhhmmss (14 chars)
by: jacquesvgreunen | last post by:
Does anyone know of any limitations on Calculated fields in a select query? Thanx
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 queries. simply adds the values of several fields for each record: ExpectedPmt: CLng(+++ ++++ +++ +++ ++)
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. The Calculation would evaluate the dates in some of the other fields in the table and return a numerical result. Filemaker used a Case calculation. I need to replicate this functionality in my new Access 2007 DB on one of the forms. This is...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.