473,900 Members | 4,105 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 6821
2,322 Recognized Expert Moderator Top Contributor
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
2,322 Recognized Expert Moderator Top Contributor
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
12 New Member

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

I will go through the video. Thanks.
Oct 23 '12 #24
2,322 Recognized Expert Moderator Top Contributor
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
12 New Member

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
2,322 Recognized Expert Moderator Top Contributor
You are welcome.
Oct 25 '12 #27

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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
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: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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.