473,836 Members | 1,521 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 #1
26 6812
2,322 Recognized Expert Moderator Top Contributor
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
3,653 Recognized Expert Moderator Specialist

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

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


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
2,322 Recognized Expert Moderator Top Contributor
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
2,322 Recognized Expert Moderator Top Contributor
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
12 New Member
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
12 New Member
What is csv?
Not sure what format that is.

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

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: 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: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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: 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.