How to run a query with newest records? 2

[0200050]

Good evening everybody!

I'm currently having a little problem with queries.

I have 5 records in a table. 4 text records and one date. I made a query with these information.

Date record can be sorted out with newest information easily, just setting its Total to MAX. But that's the point where my knowledge brakes down and stops. The Total LAST doesn't work out properly, it sometimes stops working, and that is the most pissing issue I've found from MS Access (2000 & XP). I tried to use the LAST with the other 4 records. Or maybe I haven't understood the LAST function good enough.

I guess MAX isn't appropriate at this point?

Those other 4 records are in different table. Should I try to push them into one big?

And is there any built-in function to help with this issue?

By the way, does the DateDiff work out with expressions? I thought something like the program would divide two dates
and compare these dates to for example null(0), but couldn't get that working.

Any suggestions dealed with the problem? -Any help will be highly appreciated.

Mikko from Finland

 

[lespaul]

Your terminology is confusing.

A table in a database holds records, each record is made up of fields.

Now can you restate this:

I have 5 records in a table. 4 text records and one date. I made a query with these information.

using the correct terminology so that we know what you mean?

What is the query you made using this information? Can you post the SQL?

Why don't you provide us with some details about the information you have and what you want to end up with (maybe some sample records) and expected results.

les

 

[0200050]

Thanks for correcting!

So there were 5 fields! Man, am I lost(?).

I have five fields in a table, of course(!), there are something near 500 records what of course(probably) isn't necessary detail.

I've got more than one of those queries, but this is the most used one. (Notice (or ignore) skandinavian letter ä =ae)

This query is built from the following query:
1. query: Pitkälle_Tähdätään_Kysely

The query actually has 8 FIELDS. Let's stick to it.
And here the troublemaker comes:

SELECT Pitkälle_Tähdätään_Kysely.Nokia_ser, Pitkälle_Tähdätään_Kysely.Murunda_ser, Last(Pitkälle_Tähdätään_Kysely.Ilmoitettu_vika) AS LastOfIlmoitettu_vika, Last(Pitkälle_Tähdätään_Kysely.Havaittu_vika) AS LastOfHavaittu_vika, Last(Pitkälle_Tähdätään_Kysely.KorjausPvm) AS LastOfKorjausPvm, Last(Pitkälle_Tähdätään_Kysely.Korjaustoimenpide) AS LastOfKorjaustoimenpide, Pitkälle_Tähdätään_Kysely.Malli, Pitkälle_Tähdätään_Kysely.Tuote
FROM Pitkälle_Tähtäävä_HuoltoKysely, Pitkälle_Tähdätään_Kysely
GROUP BY Pitkälle_Tähdätään_Kysely.Nokia_ser, Pitkälle_Tähdätään_Kysely.Murunda_ser, Pitkälle_Tähdätään_Kysely.Malli, Pitkälle_Tähdätään_Kysely.Tuote;

As you see it looks kinda basic one.

The query you see above this text was the query built from this one named KyselySeOnMenoa:

SELECT Korjaus_Taulu.Ilmoitettu_vika, Korjaus_Taulu.Havaittu_vika, Korjaus_Taulu.Korjaustoimenpide, Perustiedot_Taulu.Murunda_ser, Perustiedot_Taulu.Malli, Perustiedot_Taulu.Tuote, Perustiedot_Taulu.Nokia_ser, Korjaus_Taulu.KorjausPvm
FROM Perustiedot_Taulu INNER JOIN Korjaus_Taulu ON Perustiedot_Taulu.SarjaID = Korjaus_Taulu.SarjaID
WHERE (((Korjaus_Taulu.KorjausPvm) Is Not Null));

As you see here, I use a lot of finnish names. Hope they don't confuse anymore. Taulu is in english same as Table.

Now, what I'm expecting to get from this, is a report which displays the NEWEST record. I got this report ready and working already. This report should output the NEWEST record. Record/Report at this case = 8 fields. 1 DATE and 7 TEXT.

The problem is, when I run the report(which uses the information of this post's first query), the report sometimes displays the NEWEST record wrong. It actually takes some older record, and displays it instead of the REAL NEWEST ONE.

Want to hear something from the background of the project?
These tables that I'm using on these queries are first filled from one special form. At this form, I can sort the information correctly. I use there OrderBy/OrderByOn-functions. It goes good.

The problem are the queries/query.

I hope this was well enough detailed(if wasn't, just inform).


-0200050-

 

[0200050]

This(last post's first) query seems to work fine sometimes, but then it gets down suddenly. This is seen as wrong records. Searching the records is done by typing serienumbers into a form.

From this form the report gets the information of what kinda serienumbers it has to deal with.

And each serienumber has its own newest record, that i'm looking forward to search for.

Could there be something factor in the program, or database, which interrupts the LAST -criteria?

Sincerely,

-0200050-

 

[lespaul]

So what you want to do is find the record information where the date is the latest?

something like:

SELECT * FROM TABLE T1 WHERE DATEFIELD = (SELECT MAX(DATEFIELD) FROM TABLE T2 WHERE T1.PK = T2.PK)

This is a correlated subquery. It finds the latest date for each PK in the first table and returns the whole record for each of the latest dates.

HTH

Leslie

 

[0200050]

Yeah, I am looking for a solution how to find the latest dates by using the serienumber input.

This example of yours is very clear! Thank you very much of it!

I made it look like this:

SELECT *
FROM Korjaus_Taulu
WHERE (((Korjaus_Taulu.KorjausPvm)=(SELECT MAX(KorjausPvm) FROM Korjaus_Taulu WHERE Korjaus_Taulu.Ilmoitettu_vika =Korjaus_Taulu.Havaittu_vika)));

Now it does following: Finds all the latest dates(two day after current time) in the system! It is going into very right direction. Now the action I'm after is only a little different.

This is the desired way of function:

Serienumber   NewestFixingDate
-----------   --------------
1234567890    1.9.2004
0987654321    2.6.2004 - - - - > So the dates can differ
5552221110    1.5.2004

This kind of it is right now:

Serienumber   NewestFixingDate
-----------   --------------
11111111111   31.8.2004
22222222222   31.8.2004 
33333333333   31.8.2004

I noticed that the query you passed here, searched at my handling only the records saved date 31.8.2004. Nothing else. It outputted only those records that had the 31.8.2004 date saved. All records aren't saved on that day as latest. Have you any idea, could I have done something wrong?

I'd like to view a single serienumber at a time, and the newest/latest/greatest date of it. This query showed only the latest date values, without really notifying serienumbers. It still is really close the right.

And oh yeah, I'm not so good with abbreviations, so could you please inform me about the suffix .PK a little?

-0200050-

 

[0200050]

Thank you for your help! I managed to solve this problem out now. I just did two queries(both had same ID, hadn't tried out that before), and typed some criterias(MAX and LAST), and it amazingly seemed to work out.

I'm currently still getting on SQL, and I think I need to learn it lot more. It is lot of fun, thanks to those who likes to share the information.

Best regards,
-0200050-

 

[lespaul]

glad to help! although you did most of it, just a little pointer in the right direction to get you going!

have a star on me!

les