Last not null value

[Keijo82]

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!

[Rabbit]

Have criteria in each of the number fields that excludes "-"

[Keijo82]

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]

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 "-"

[Keijo82]

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]

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.

[Keijo82]

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]

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]

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?