Help with query in MS Access 2

[PhpDeveloper]

I am trying to write a query to retrieve records that contain the latest (maximum) version number and action can’t be “Deletion”.

Sample table is listed below named as Mytable:

ID Version Action Name
1 1 Data1
2 1 Data2
1 2 Modification Data1
3 1 Data3
2 2 Modification Data2
1 3 Deletion Data1

My query is like this:

Select * from Mytable where (Action<>’Deletion’ or Action IS NULL)

But you know this query only return the following records with one Data3 and two Data2 like this:
ID Version Action Name
2 1 Data2
3 1 Data3
2 2 Modification Data2

So which means I need the query that can retrieve records as following:

ID Version Action Name
3 1 Data3
2 2 Modification Data2


Since the record of Data1 is marked as “Deletion” with its latest verion number, so it should NOT be displayed. But I only need the record of Data2 containing the latest version number 2. I know I am stuck with adding the selection of max(Version_Num) in this query, could someone kindly help me out?

 

[traingamer]

Typed, not tested:

Select ID, Max(Version), Last(Action), Last(Name) from Mytable where (Action<>’Deletion’ or Action IS NULL)
Group by ID
order by Action

or sonething along those lines.

traingamer

 

[cliffhanger9]

Do you not have a primary key?

 

[cliffhanger9]

Why can we not edit posts here???!?!?! thats rediculous.


Anyway...you also may want to "normalize" your database structure to eliminate the redundancies you have displayed above.

 

[PHV]

SELECT A.ID, A.Version, A.Action, A.Name
FROM Mytable A
WHERE A.Version = (SELECT Max(B.Version) FROM Mytable B WHERE B.ID=A.ID)
AND Nz(A.Action <> ’Deletion’);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

As usual, PH has a better solution.

traingamer

 

[PhpDeveloper]

First of all, I have to say, I do have a primary key in the table which is set to auto number; secondly, my table doesn't look like a normalized one because I only do insert or update in this application, even the action is marked as “Deletion”, it still has to be stored in the able, sort of symbolic deletion. That's why it looks like there are redundancies in this table, but it has to be like that. But once a data is marked as “Deletion” with the latest version, this data should NOT be displayed with ANY version in the query. Well, PHV‘s answer is getting close, but misunderstood my request. Thanks also go to traingamer, but your selection only return one record though.

 

[PHV]

retrieve records that contain the latest (maximum) version number and action can’t be “Deletion”.
Where is the misunderstanding ?

 

[PhpDeveloper]

Dear PHV:

The result returns two records, Data1 with its version 2, which it should NOT be displayed since it is marked as “Deletion” in version 3. The other returned record is correct: Data2 with latest version 2. But your query did not return Data3 with its version 1 as I expected, even its action value is null. Could you please read my original message again? It should only return like the following:

ID Version Action Name
3 1 Data3
2 2 Modification Data2


Thanks anyway!

 

[PHV]

Sorry for the typo, replace this:
AND Nz(A.Action <> ’Deletion’);
By this:
AND Nz(A.Action) <> ’Deletion’;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Or:

SELECT A.ID, A.Version, A.Action, A.Name
FROM mytable A
WHERE A.Version = (SELECT Max(B.Version) FROM mytable B WHERE B.ID=A.ID)
AND (A.Action<>"Deletion" or A.Action IS NULL);

traingamer

 

[PhpDeveloper]

Great thanks go to PHV, actually I modified your query a bit like this and it works now!

SELECT A.ID, A.Version, A.Action, A.Name
FROM Mytable A
WHERE A.Version = (SELECT Max(B.Version) FROM Mytable B WHERE B.ID=A.ID AND Nz(A.Action )<> "Deletion");

And I also really appreciate traingamer’s suggestion, his works the same way.
Thanks for all of your help!

 

[PhpDeveloper]

Hi,

I am running a very simple insert query in order to insert a record to a table named as "mytable". Here is the design view:

FieldName DataType Reqired DefaultValue
Tap_Name Text No
Max_number Long Integer No 0

Here is my query, and please notice that the value for Max_Number is null:
Insert into mytable (Tap_Name, Max_Number) values ('Tap1', '')

First it says it is about to append 1 record to the table, then it gives me an error message, saying:"Microsoft set 1 field to Null due to a type conversion failure... "

I tried to change the data type to single or integer, it generates the same message. Is there any solution without replacing the Null value with "0" in order to append a record to this table?

Thanks!

 

[PHV]

Please, start a new thread as your last post has nothing to do with the actual topic.