Hi,
What kind of query I should do if I have table like below and I want to get last written values both tanks? Not null values ( - equals null values).
id date tank number1 number2 number3
1 28.3.2007 1 1 1 1
2 28.3.2007 2 2 2 2
3 29.3.2007 1 3 - -
4 30.3.2007 1 - 4 -
So I want query to give me answers like below:
date tank number1 number2 number3
28.3.2007 2 2 2 2
30.3.2007 1 3 4 1
I know how to get query to give me id 4 and 2. But id 4 doesn't contain all values. Number1 and number2 is null value.
Thanks!
8  5045  Rabbit 12,516
Recognized Expert Moderator MVP
Have criteria in each of the number fields that excludes "-"
Have criteria in each of the number fields that excludes "-"
Thanks for your answer!
I don't quite understand what you are saying? What kind of criteria should I use? If I use "is not null" query only give me id 2 row. And I think thats because there is null values in last tank 1 row.
You probably meant something else...
Rabbit 12,516
Recognized Expert Moderator MVP
Thanks for your answer!
I don't quite understand what you are saying? What kind of criteria should I use? If I use "is not null" query only give me id 2 row. And I think thats because there is null values in last tank 1 row.
You probably meant something else...
Technically, none of your examples had null values. A null value is the absense of a value. Rather, you have the character "-" making it non null. So for the criteria, you can use Is Not Null. You have to use Not "-"
Technically, none of your examples had null values. A null value is the absense of a value. Rather, you have the character "-" making it non null. So for the criteria, you can use Is Not Null. You have to use Not "-"
I'm sorry. I meant that in real table there is null values and in example table which I post in this topic "-" means null value. I have to put something in example table because else numbers would been in wrong column like below:
id date tank number1 number2 number3
1 28.3.2007 1 1 1 1
2 28.3.2007 2 2 2 2
3 29.3.2007 1 3
4 30.3.2007 1 4
Number 4 should be in number 2 column.
Rabbit 12,516
Recognized Expert Moderator MVP
Sorry, my fault, I misunderstood what you wanted. Create 3 calculated fields for the 3 numbers. - Number1: DLookup("Number1", "[Table Name]", "id = " & DMax("id", "[Table Name]", "Number1 Is Not Null And Tank = " & Tank))
Repeat 2 more times for the other 2 numbers.
Sorry, my fault, I misunderstood what you wanted. Create 3 calculated fields for the 3 numbers.
- Number1: DLookup("Number1", "[Table Name]", "id = " & DMax("id", "[Table Name]", "Number1 Is Not Null And Tank = " & Tank))
Repeat 2 more times for the other 2 numbers.
Thank you Rabbit!
That's working fine in my test table. But there's problem with the real data. In real program I have to use two tables (union). Table1 have like 10000 records and table2 have only 5000 records. I have to find last value so if I use id query always find values in table1. And other problem is that sometimes users write down newest date first and then day older and so on. That happens often after weekend. They first write sunday values and then saturday and friday. If I now use Dmax function and id there could come old data. Friday values even if there is newer value in sunday. Can you help me with this. I think I have to use date not id to get this work. I tried but could not understand what to do...
Real world is never as "easy" as test world
NeoPa 32,568
Recognized Expert Moderator MVP
Keijo,
What do values 1, 2 & 3 refer to?
Why do you have three values stored in each record rather than each stored in its own record (with maybe a type field to differentiate between them)?
It is possible to work it the way you currently have the data stored but it's much harder as that's not how Access is designed to work.
 Rabbit 12,516
Recognized Expert Moderator MVP
Thank you Rabbit!
That's working fine in my test table. But there's problem with the real data. In real program I have to use two tables (union). Table1 have like 10000 records and table2 have only 5000 records. I have to find last value so if I use id query always find values in table1. And other problem is that sometimes users write down newest date first and then day older and so on. That happens often after weekend. They first write sunday values and then saturday and friday. If I now use Dmax function and id there could come old data. Friday values even if there is newer value in sunday. Can you help me with this. I think I have to use date not id to get this work. I tried but could not understand what to do...
Real world is never as "easy" as test world
There's going to be no way to do this with the current setup. You'll need them to write down the date AND time for each record they enter. Then can you find the most current number.
Right now you only have date. Multiple records with the same date, how can you ever know which one is the most recent?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: John Morgan |
last post by:
In an SQL statement which concatenates several fields I get a null
value returned if any one of the fields are null.
Is this to be expected?
For example :
SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' +
tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' +
tblMember.memberCountry + '<br> ' + tblMember.memberInstitution AS
|
by: Lynn |
last post by:
Hi all,
I am having problem when did the validation of XML document with Schema.
my schema is like the following:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="schema.xsd"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
elementFormDefault="qualified" targetNamespace="schema.xsd">
<xsd:element name="bookstore" type="bookstoreType" />
<xsd:complexType name="bookstoreType">
<xsd:sequence maxOccurs="unbounded">...
|
by: Robb Gilmore |
last post by:
Hello,
We have a C#.NET app which is calling a Java webservice. We use the wsdl
file exportted from the java webservice to create our web-reference in Visual
Studio. We are able to create the parameter classes and call the webservice
just fine.
Our problem is, within our .Net app, we have some value objects ( like
floats, for instance ) which are meant to be null. Since there is no null
float, we use float.minvalue to indicate a...
|
by: TC |
last post by:
What does it mean for an integer to have a null value? I am trying to use
the DataView.Find method. That method has an integer return type which
contains the "index of the row in the DataView containing the sort key value
specified; otherwise a null value if the sort key value does not exist."
By "null value", does it mean System.DBNull? (I thought only objects could
evaluate to System.DBNull.) How can I test whether an integer variable...
|
by: Martin R |
last post by:
Hi,
How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
thx,
Martin
*** Sent via Developersdex http://www.developersdex.com ***
| | |
by: Veeru71 |
last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?
I can't use identity_val_local() as the INSERTS are happening in a
different session.
Eg, We have the following table....
|
by: Eric Layman |
last post by:
Hi everyone,
Im puzzled by a NULL behaviour in SQL 2000 server.
There is a column in the table that does not allow NULL.
During data mining, the staff noted that, for that particular column, there
are a few records that are empty.
I do not specifically know whether they are "alt + 0160" character.
|
by: prabirchoudhury |
last post by:
CRITERIA;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| CritCode | int(4) | NO | PRI | 0 | |
| Description | varchar(150) | YES | | NULL | |
| CritGroup | varchar(10) | YES | | NULL | |
| Detail | varchar(30) | YES | | NULL | ...
|
by: qwedster |
last post by:
Folk!
How to programattically check if null value exists in database table (using stored procedure)?
I know it's possble in the Query Analyzer (see last SQL query batch statements)?
But how can I pass null value as parameter to the database stored procedure programattically using C#?
Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
|
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: 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: 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: 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: 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...
| |
