Find the most recent record of each payee by date.

A

A.S.

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
 
K

KC-Mass

Try the following for the latest payment for each expense payee:

SELECT tblExpense.[Expense Payee], Max(tblExpense.[Submit Date]) AS
[MaxOfSubmit Date]
FROM tblExpense
GROUP BY tblExpense.[Expense Payee];

Regards

Kevin
 
K

KARL DEWEY

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;
 
K

Ken Sheridan

Try this:

SELECT ID, E1.ExpensePayee,
CheckStatus, [Submit Date]
FROM tbl_Expense AS E1 INNER JOIN tbl_Payee
ON E1.ExpensePayee = tbl_Payee.ExpensePayee
WHERE ID IN (230, 268, 267, 223, 265, 263, 116, 269, 235, 270, 266, 264, 276)
AND [Submit Date] =
(SELECT MAX([Submit Date])
FROM tbl_Expense AS E2
WHERE E2.ExpensePayee = E1.ExpensePayee)
ORDER BY E1.ExpensePayee;

Ken Sheridan
Stafford, England
 
A

A.S.

Yes, your query puts out the latest date for all Expense Payees. I couldn't
filter for the only a group of payees after this query, though. (i.e. Payee
ID = 223, 265, and so on...)

KC-Mass said:
Try the following for the latest payment for each expense payee:

SELECT tblExpense.[Expense Payee], Max(tblExpense.[Submit Date]) AS
[MaxOfSubmit Date]
FROM tblExpense
GROUP BY tblExpense.[Expense Payee];

Regards

Kevin


A.S. said:
I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee,
qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit
Date?

Thank you,
Click to expand...
Click to expand...
 
A

A.S.

Your two queries give the same result as mine.

KARL DEWEY said:
Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


A.S. said:
I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
 
K

KARL DEWEY

Is the first query giving you the correct Max Date?

A.S. said:
Your two queries give the same result as mine.

KARL DEWEY said:
Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


A.S. said:
I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
A

A.S.

I tried your query as much as I could, but it didn't work. What are E1 and
E2? Are they separate queries or abbreviation of the existing tables?

Ken Sheridan said:
Try this:

SELECT ID, E1.ExpensePayee,
CheckStatus, [Submit Date]
FROM tbl_Expense AS E1 INNER JOIN tbl_Payee
ON E1.ExpensePayee = tbl_Payee.ExpensePayee
WHERE ID IN (230, 268, 267, 223, 265, 263, 116, 269, 235, 270, 266, 264, 276)
AND [Submit Date] =
(SELECT MAX([Submit Date])
FROM tbl_Expense AS E2
WHERE E2.ExpensePayee = E1.ExpensePayee)
ORDER BY E1.ExpensePayee;

Ken Sheridan
Stafford, England

A.S. said:
I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
 
A

A.S.

Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

KARL DEWEY said:
Is the first query giving you the correct Max Date?

A.S. said:
Your two queries give the same result as mine.

KARL DEWEY said:
Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

A.S. said:
Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

KARL DEWEY said:
Is the first query giving you the correct Max Date?

A.S. said:
Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

E1 and E2 are aliases for the two instances of the tbl_Expense table, and
allow the subquery to be correlated with the outer query.

The way it should work is:

1. The outer query returns the columns ID, ExpensePayee, CheckStatus and
Submit Date from tbl_Expense joined to tbl_Payee on the ExpensePayee column.
The query is first restricted to those rows with the ID values in the value
list for the IN operator (this is the equivalent of the series of OR
operations which you used).

2. The outer query is further restricted by means of the subquery, which
returns the latest (MAX) Submit Date for the outer query's current
ExpensePayee value by virtue of the correlation on this column in the two
instances of the tbl_Expense table, thus restricting the rows returned by the
outer query to the those with the latest Submit Date per ExpensePayee, which
is how I interpreted your requirements.

What do you mean by 'I tried your query as much as I could'? What you need
to do is open the query designer, but not add any tables, switch to SQL view
and paste in the SQL statement I posted. Then open the query.

Nor does simply saying that the query 'didn't work' help us diagnose the
problem. In what way didn't it work; did it fail with an error? did it open
but return no rows? did it open but return the wrong rows?

Ken Sheridan
Stafford, England

A.S. said:
I tried your query as much as I could, but it didn't work. What are E1 and
E2? Are they separate queries or abbreviation of the existing tables?

Ken Sheridan said:
Try this:

SELECT ID, E1.ExpensePayee,
CheckStatus, [Submit Date]
FROM tbl_Expense AS E1 INNER JOIN tbl_Payee
ON E1.ExpensePayee = tbl_Payee.ExpensePayee
WHERE ID IN (230, 268, 267, 223, 265, 263, 116, 269, 235, 270, 266, 264, 276)
AND [Submit Date] =
(SELECT MAX([Submit Date])
FROM tbl_Expense AS E2
WHERE E2.ExpensePayee = E1.ExpensePayee)
ORDER BY E1.ExpensePayee;

Ken Sheridan
Stafford, England

A.S. said:
I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
A

A.S.

Sorry, my mistype, first of all. "tbl_CheckStatus" should have been
tbl_Expense.CheckStatus. When I just copied your query to SQL, it didn't
work, so I went back to design view and built it, adding
tbl_Expense.CheckStatus.

KARL DEWEY said:
You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

A.S. said:
Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

KARL DEWEY said:
Is the first query giving you the correct Max Date?

:

Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

When I just copied your query to SQL, it didn't work,
What did not work? Nothing? Not last date?
How did you modify the query? Why did you add tbl_Expense.CheckStatus? By
adding that you will get a date for every different status and not just the
last date.

Post your modified query.

A.S. said:
Sorry, my mistype, first of all. "tbl_CheckStatus" should have been
tbl_Expense.CheckStatus. When I just copied your query to SQL, it didn't
work, so I went back to design view and built it, adding
tbl_Expense.CheckStatus.

KARL DEWEY said:
You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

A.S. said:
Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

:

Is the first query giving you the correct Max Date?

:

Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
A

A.S.

OK, I tried your query by simply copying and pasting. It gave duplicate
records, so I changed the Unique Values in query property to "Yes", then it
worked beautifully... Thank you very much for your time to write the code
and explain it to me. You are a truly query expert, I must say.

A.S., New York

Ken Sheridan said:
E1 and E2 are aliases for the two instances of the tbl_Expense table, and
allow the subquery to be correlated with the outer query.

The way it should work is:

1. The outer query returns the columns ID, ExpensePayee, CheckStatus and
Submit Date from tbl_Expense joined to tbl_Payee on the ExpensePayee column.
The query is first restricted to those rows with the ID values in the value
list for the IN operator (this is the equivalent of the series of OR
operations which you used).

2. The outer query is further restricted by means of the subquery, which
returns the latest (MAX) Submit Date for the outer query's current
ExpensePayee value by virtue of the correlation on this column in the two
instances of the tbl_Expense table, thus restricting the rows returned by the
outer query to the those with the latest Submit Date per ExpensePayee, which
is how I interpreted your requirements.

What do you mean by 'I tried your query as much as I could'? What you need
to do is open the query designer, but not add any tables, switch to SQL view
and paste in the SQL statement I posted. Then open the query.

Nor does simply saying that the query 'didn't work' help us diagnose the
problem. In what way didn't it work; did it fail with an error? did it open
but return no rows? did it open but return the wrong rows?

Ken Sheridan
Stafford, England

A.S. said:
I tried your query as much as I could, but it didn't work. What are E1 and
E2? Are they separate queries or abbreviation of the existing tables?

Ken Sheridan said:
Try this:

SELECT ID, E1.ExpensePayee,
CheckStatus, [Submit Date]
FROM tbl_Expense AS E1 INNER JOIN tbl_Payee
ON E1.ExpensePayee = tbl_Payee.ExpensePayee
WHERE ID IN (230, 268, 267, 223, 265, 263, 116, 269, 235, 270, 266, 264, 276)
AND [Submit Date] =
(SELECT MAX([Submit Date])
FROM tbl_Expense AS E2
WHERE E2.ExpensePayee = E1.ExpensePayee)
ORDER BY E1.ExpensePayee;

Ken Sheridan
Stafford, England

:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
A

A.S.

When I copied and pasted, I got an error message, "Invalid bracketing of name
'Submit DateOfMax'." I will post the modified query.

KARL DEWEY said:
What did not work? Nothing? Not last date?
How did you modify the query? Why did you add tbl_Expense.CheckStatus? By
adding that you will get a date for every different status and not just the
last date.

Post your modified query.

A.S. said:
Sorry, my mistype, first of all. "tbl_CheckStatus" should have been
tbl_Expense.CheckStatus. When I just copied your query to SQL, it didn't
work, so I went back to design view and built it, adding
tbl_Expense.CheckStatus.

KARL DEWEY said:
You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

:

Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

:

Is the first query giving you the correct Max Date?

:

Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

The error "Invalid bracketing of name 'Submit DateOfMax'." would have been
due to word wrap adding a hard return after 'Submit'.

A.S. said:
When I copied and pasted, I got an error message, "Invalid bracketing of name
'Submit DateOfMax'." I will post the modified query.

KARL DEWEY said:
When I just copied your query to SQL, it didn't work,
Click to expand...
What did not work? Nothing? Not last date?
How did you modify the query? Why did you add tbl_Expense.CheckStatus? By
adding that you will get a date for every different status and not just the
last date.

Post your modified query.

A.S. said:
Sorry, my mistype, first of all. "tbl_CheckStatus" should have been
tbl_Expense.CheckStatus. When I just copied your query to SQL, it didn't
work, so I went back to design view and built it, adding
tbl_Expense.CheckStatus.

:

You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

:

Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

:

Is the first query giving you the correct Max Date?

:

Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
A

A.S.

I solved the cause of error message (the name was split into two lines) and
changed the Unique Values in property for the second query to "Yes", and then
it worked!! I'm not totally a beginner for Access, but haven't used SQL this
closely. Thank you very much for spending your time for all of this. You
got me learned a lot and I enjoyed it very much. I'm really amazed there are
people who understand coding precisely Microsoft intended.

A.S., New York

A.S. said:
When I copied and pasted, I got an error message, "Invalid bracketing of name
'Submit DateOfMax'." I will post the modified query.

KARL DEWEY said:
When I just copied your query to SQL, it didn't work,
Click to expand...
What did not work? Nothing? Not last date?
How did you modify the query? Why did you add tbl_Expense.CheckStatus? By
adding that you will get a date for every different status and not just the
last date.

Post your modified query.

A.S. said:
Sorry, my mistype, first of all. "tbl_CheckStatus" should have been
tbl_Expense.CheckStatus. When I just copied your query to SQL, it didn't
work, so I went back to design view and built it, adding
tbl_Expense.CheckStatus.

:

You just lost me here. My first query does not have tbl_CheckStatus in it.
My first query is to pull list of tbl_Expense.ExpensePayee and Max
tbl_Expense.[Submit Date] only.

:

Yes, if there is no input in tbl_CheckStatus. No, if there is input in
tbl_CheckStatus; it shows all records for one payee, not the record with
latest date.

:

Is the first query giving you the correct Max Date?

:

Your two queries give the same result as mine.

:

Try these two queries --
qryCheckStatus ---
SELECT tbl_Expense.ExpensePayee, Max(tbl_Expense.[Submit Date]) AS [Submit
DateOfMax]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
GROUP BY tbl_Expense.ExpensePayee;

SELECT tbl_Expense.ExpensePayee, tbl_Expense.CheckStatus,
tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN qryCheckStatus ON (tbl_Expense.ExpensePayee =
qryCheckStatus.ExpensePayee) AND (tbl_Expense.[Submit Date] =
qryCheckStatus.[Submit DateOfMax])
ORDER BY tbl_Expense.ExpensePayee;


:

I've been trying the examples discussed in this forum, but no success.
There are two tables:
table (tbl_Expense) - ExpensePayee, CheckStatus, Submit Date
table (tb_Payee) - ExpensePayee, ID.

In the first query below (qryCheckStatus), only a group of IDs is used to
extract data from tbl_Expense. This query is working:

SELECT DISTINCT tbl_Payee.ID, tbl_Expense.ExpensePayee,
tbl_Expense.CheckStatus, tbl_Expense.[Submit Date]
FROM tbl_Expense INNER JOIN tbl_Payee ON tbl_Expense.ExpensePayee =
tbl_Payee.ExpensePayee
WHERE (((tbl_Payee.ID)=230 Or (tbl_Payee.ID)=268 Or (tbl_Payee.ID)=267 Or
(tbl_Payee.ID)=223 Or (tbl_Payee.ID)=265 Or (tbl_Payee.ID)=263 Or
(tbl_Payee.ID)=116 Or (tbl_Payee.ID)=269 Or (tbl_Payee.ID)=235 Or
(tbl_Payee.ID)=270 Or (tbl_Payee.ID)=266 Or (tbl_Payee.ID)=264 Or
(tbl_Payee.ID)=276))
ORDER BY tbl_Expense.ExpensePayee;

Second query below (qryCheckStatus2) uses Max to find the latest Submit
Date, but it shows ALL records instead:

SELECT DISTINCT qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
Max(qryCheckStatus.[Submit Date]) AS [Submit DateOfMax]
FROM qryCheckStatus
GROUP BY qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus
ORDER BY qryCheckStatus.ExpensePayee;

So I wrote another query below to find only one record for each Payee, but
it gives me the same reqult as the 2nd query above:

SELECT DISTINCTROW qryCheckStatus.ExpensePayee, qryCheckStatus.CheckStatus,
qryCheckStatus2.[Submit DateOfMax]
FROM qryCheckStatus INNER JOIN qryCheckStatus2 ON
(qryCheckStatus2.ExpensePayee = qryCheckStatus.ExpensePayee) AND
(qryCheckStatus.[Submit Date] = qryCheckStatus2.[Submit DateOfMax])
ORDER BY qryCheckStatus.ExpensePayee;

Could anyone show me way to find only the record with the latest Submit Date?

Thank you,
Click to expand...
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

Find most recent record by date and time 2
Query to find the most recent record for each duplicate record 3
convert access date Format() to French date , use of ORDER BY SQL 4
Most Recent Date Query 2
Access 2003 query for listbox sorts by Date but not most recent Time 8
DMax to return most recent date isn't working 2
Query record with most recent date 1
Bringing back the 20 most recent entries for each group by 4

Top