Query result is wrong?!

C

Cosmin

Hello All,
I have the following problem:
I have a Table1 with 2 columns:
Name Date
A 1-Mar-2004
B 1-Mar-2004
C 1-Mar-2004
D 3-Mar-2004
C 4-Mar-2004
E 4-Mar-2004
B 6-Mar-2004
A 9-Mar-2004
D 29-Feb-2004
E 2-Mar-2004
A 3-Mar-2004
Now I need the following result:
Name Date
A 9-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
(the most recently date for each name)
In the query I choose Table1 and each column:
Field: Name Date
Table: Table1 Table1
Sort:
Show: (selected) (selected)
Criteria: In (select max(Date) from Table1
group by Name)
or:

The result is:
Name Date
A 3-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
Insteed of A= 9-Mar-2004 I have the wrong A= 3-Mar-2004.
What is wrong here, why I do not have the expected result?
 
C

Cosmin

Thank You for your answer.
It works only if I have those 2 columns but,
if myTable contain one more column "PartNumber":
Name Date PartNumber
A 1-Mar-2004 4
B 1-Mar-2004 11
C 1-Mar-2004 13
D 3-Mar-2004 12
C 4-Mar-2004 9
E 4-Mar-2004 2
B 6-Mar-2004 1
A 9-Mar-2004 3
D 29-Feb-2004 8
E 2-Mar-2004 10
A 3-Mar-2004 7

How can I have this result? :

Name Date PartNumber
A 9-Mar-2004 3
B 6-Mar-2004 1
C 4-Mar-2004 9
D 3-Mar-2004 12
E 4-Mar-2004 2

Regards,
Cosmin
-----Original Message-----
Hi,


You don't need a IN clause.


SELECT [Name], MAX( [Date] )
FROM myTable
GROUP BY [Name]



should be all what is required. Take a look at
http://www.mvps.org/access/queries/qry0020.htm if other associated fields
have to be retrieved.




Hoping it may help,
Vanderghast, Access MVP



Cosmin said:
Hello All,
I have the following problem:
I have a Table1 with 2 columns:
Name Date
A 1-Mar-2004
B 1-Mar-2004
C 1-Mar-2004
D 3-Mar-2004
C 4-Mar-2004
E 4-Mar-2004
B 6-Mar-2004
A 9-Mar-2004
D 29-Feb-2004
E 2-Mar-2004
A 3-Mar-2004
Now I need the following result:
Name Date
A 9-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
(the most recently date for each name)
In the query I choose Table1 and each column:
Field: Name Date
Table: Table1 Table1
Sort:
Show: (selected) (selected)
Criteria: In (select max(Date) from Table1
group by Name)
or:

The result is:
Name Date
A 3-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
Insteed of A= 9-Mar-2004 I have the wrong A= 3-Mar-2004.
What is wrong here, why I do not have the expected
Click to expand...
result?


.
Click to expand...
 
P

Paul

Save the first query:
SELECT [Name], MAX( [Date] )
FROM myTable
GROUP BY [Name]
Click to expand...
Click to expand...

Then create a new query where you join the new query and your data table on
Name and Date.

Hope that helps

Cosmin said:
Thank You for your answer.
It works only if I have those 2 columns but,
if myTable contain one more column "PartNumber":
Name Date PartNumber
A 1-Mar-2004 4
B 1-Mar-2004 11
C 1-Mar-2004 13
D 3-Mar-2004 12
C 4-Mar-2004 9
E 4-Mar-2004 2
B 6-Mar-2004 1
A 9-Mar-2004 3
D 29-Feb-2004 8
E 2-Mar-2004 10
A 3-Mar-2004 7

How can I have this result? :

Name Date PartNumber
A 9-Mar-2004 3
B 6-Mar-2004 1
C 4-Mar-2004 9
D 3-Mar-2004 12
E 4-Mar-2004 2

Regards,
Cosmin
-----Original Message-----
Hi,


You don't need a IN clause.


SELECT [Name], MAX( [Date] )
FROM myTable
GROUP BY [Name]



should be all what is required. Take a look at
http://www.mvps.org/access/queries/qry0020.htm if other associated fields
have to be retrieved.




Hoping it may help,
Vanderghast, Access MVP



Cosmin said:
Hello All,
I have the following problem:
I have a Table1 with 2 columns:
Name Date
A 1-Mar-2004
B 1-Mar-2004
C 1-Mar-2004
D 3-Mar-2004
C 4-Mar-2004
E 4-Mar-2004
B 6-Mar-2004
A 9-Mar-2004
D 29-Feb-2004
E 2-Mar-2004
A 3-Mar-2004
Now I need the following result:
Name Date
A 9-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
(the most recently date for each name)
In the query I choose Table1 and each column:
Field: Name Date
Table: Table1 Table1
Sort:
Show: (selected) (selected)
Criteria: In (select max(Date) from Table1
group by Name)
or:

The result is:
Name Date
A 3-Mar-2004
B 6-Mar-2004
C 4-Mar-2004
D 3-Mar-2004
E 4-Mar-2004
Insteed of A= 9-Mar-2004 I have the wrong A= 3-Mar-2004.
What is wrong here, why I do not have the expected
Click to expand...
result?


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create new data based on date and visit 2
Excel Help with dates 2
Query To Rank Products 11
Query: Which employee had the most orders... for each day 1
Query to return data if whole months are selected based on week 2
Subquery with subraction - challenging 4
Query Corsstab with Three months roll over period 0
Joined table: Returns duplicated record 6

Top