Select Query

B

Ben

I have a table populated with Equipment items that is transferred from
contract to contract.

The table stores the ItemNo, ContractNo, DateOfTransfer -

Looks something like:-

A00001, 110001, 01/01/2006
A00002, 110001, 01/01/2006
A00003, 110001, 01/01/2006
A00001, 110001, 01/02/2006
A00002, 110001, 01/02/2006
A00003, 110001, 01/02/2006

I need to query the table and report back with only the most recent date
of each item. Can anyone help?
 
J

John Spencer

SELECT ItemNo, ContractNo, DateOfTransfer
FROM EquipmentTable as E
WHERE E.DateofTransfer =
(SELECT MAX(T.DateOfTransfer)
FROM EquipmentTable as T
WHERE T.ItemNo = E.ItemNo)

If all you need is the ItemNo and the DateOfTransfer
SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo

Another method which if it runs will be faster

SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer
FROM EquipmentTable as E INNER JOIN
(SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo) as T
ON E.ItemNo = T.ItemNo
and E.DateOfTransfer =T.LastTransfer
 
G

Guest

Hi John

I used the first script that you listed and it worked great for me the first
time in the first query I needed it in. When I used the same script in
another query it crashed Access (in 7 different trials).

Any ideas?

Mike
 
B

Ben

SELECT ItemNo, ContractNo, DateOfTransfer
FROM EquipmentTable as E
WHERE E.DateofTransfer =
(SELECT MAX(T.DateOfTransfer)
FROM EquipmentTable as T
WHERE T.ItemNo = E.ItemNo)

If all you need is the ItemNo and the DateOfTransfer
SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo

Another method which if it runs will be faster

SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer
FROM EquipmentTable as E INNER JOIN
(SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo) as T
ON E.ItemNo = T.ItemNo
and E.DateOfTransfer =T.LastTransfer
Click to expand...

Thank you ... Im using the following which works perfectly:-

SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer) FROM tblTransfer
as T WHERE T.ItemNo = E.ItemNo);

I've based a report on the query which returns one of each item (the
most recent) from the tblTransfer and totals the charge for each
contract with a grand total at the end.

Now, if we missed a charging week and wanted to run it from last weeks
date how would I edit the query to incorporate this?

Would it be to have the query work for all items upto a particular date
and ignore everything beyond that date. To ask for a date to be entered,
with todays date being the default value, but allow a previous date to
be entered if required.

Can this query be expanded to incorate that?
 
J

John Spencer

IF I understand what you want to do, then you need to add to the subquery's
where clause.

SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer) FROM tblTransfer
as T WHERE T.ItemNo = E.ItemNo AND T.DateOfTransfer <= #12/1/2005#);

You can make that a lot more flexible by using a parameter to supply the
cutoff date.
Parameter [What is the Cutoff Date] DateTime:
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);

If you leave the prompt blank, then Cutoff date will "default" to Jan 1,
2299

Even better would be to use a Form's control to supply the cutoff date.

Ben said:
SELECT ItemNo, ContractNo, DateOfTransfer
FROM EquipmentTable as E
WHERE E.DateofTransfer =
(SELECT MAX(T.DateOfTransfer)
FROM EquipmentTable as T
WHERE T.ItemNo = E.ItemNo)

If all you need is the ItemNo and the DateOfTransfer
SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo

Another method which if it runs will be faster

SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer
FROM EquipmentTable as E INNER JOIN
(SELECT ItemNo, Max(DateOfTransfer) as LastTransfer
FROM EquipmentTable
GROUP BY ItemNo) as T
ON E.ItemNo = T.ItemNo
and E.DateOfTransfer =T.LastTransfer
Click to expand...

Thank you ... Im using the following which works perfectly:-

SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer) FROM tblTransfer
as T WHERE T.ItemNo = E.ItemNo);

I've based a report on the query which returns one of each item (the
most recent) from the tblTransfer and totals the charge for each
contract with a grand total at the end.

Now, if we missed a charging week and wanted to run it from last weeks
date how would I edit the query to incorporate this?

Would it be to have the query work for all items upto a particular date
and ignore everything beyond that date. To ask for a date to be entered,
with todays date being the default value, but allow a previous date to
be entered if required.

Can this query be expanded to incorate that?
Click to expand...
 
B

Ben

IF I understand what you want to do, then you need to add to the subquery's
where clause.

SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer) FROM tblTransfer
as T WHERE T.ItemNo = E.ItemNo AND T.DateOfTransfer <= #12/1/2005#);

You can make that a lot more flexible by using a parameter to supply the
cutoff date.
Parameter [What is the Cutoff Date] DateTime:
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);

If you leave the prompt blank, then Cutoff date will "default" to Jan 1,
2299

Even better would be to use a Form's control to supply the cutoff date.
Click to expand...

When I try to use the more flexible version with Parameter at the start
I get an error saying Invalid SQL statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Ive entered the query in the sql view, should the parameter section be
somewhere else within the query?
 
J

John Spencer

My fault, it looks as if I put a colon in the statement where I should have
placed a semi-colon (on the first line)

Parameter [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);

Ben said:
IF I understand what you want to do, then you need to add to the
subquery's
where clause.

SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer) FROM tblTransfer
as T WHERE T.ItemNo = E.ItemNo AND T.DateOfTransfer <= #12/1/2005#);

You can make that a lot more flexible by using a parameter to supply the
cutoff date.
Parameter [What is the Cutoff Date] DateTime:
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);

If you leave the prompt blank, then Cutoff date will "default" to Jan 1,
2299

Even better would be to use a Form's control to supply the cutoff date.
Click to expand...

When I try to use the more flexible version with Parameter at the start
I get an error saying Invalid SQL statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Ive entered the query in the sql view, should the parameter section be
somewhere else within the query?
Click to expand...
 
B

Ben

My fault, it looks as if I put a colon in the statement where I should have
placed a semi-colon (on the first line)

Parameter [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);
Click to expand...

Its still giving me the same and highlighting parameter as the problem.
Its access 2000 if that makes any difference?
 
J

John Spencer

--- FOREHEAD SLAP!!! --- I'm really not doing well. The word should be
Parameters not Parameter (it was missing the "s".).


Try
Parameters [What is the Cutoff Date] DateTime;
SELECT ItemNo, ...


Ben said:
My fault, it looks as if I put a colon in the statement where I should
have
placed a semi-colon (on the first line)

Parameter [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);
Click to expand...

Its still giving me the same and highlighting parameter as the problem.
Its access 2000 if that makes any difference?
Click to expand...
 
B

Ben

My fault, it looks as if I put a colon in the statement where I should have
placed a semi-colon (on the first line)

Parameter [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);
Click to expand...

Its still giving me the same and highlighting parameter as the problem.
Its access 2000 if that makes any difference?
Click to expand...

Ive searched google and 'parameter' seems to be entered as 'parameters'
on the ones ive seen so tried that and it gets further into it then
tells me missing a bracket or something ... looking through ive added
the final bracket ) and it seems to work.

Thank you very much for your help.
 
J

John Spencer

Glad you were able to troubleshoot this. I really apologize for messing
this up. I'm just not doing well today.


Ben said:
My fault, it looks as if I put a colon in the statement where I should
have
placed a semi-colon (on the first line)

Parameter [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#);
Click to expand...

Its still giving me the same and highlighting parameter as the problem.
Its access 2000 if that makes any difference?
Click to expand...

Ive searched google and 'parameter' seems to be entered as 'parameters'
on the ones ive seen so tried that and it gets further into it then
tells me missing a bracket or something ... looking through ive added
the final bracket ) and it seems to work.

Thank you very much for your help.
Click to expand...
 
B

Ben

Glad you were able to troubleshoot this. I really apologize for messing
this up. I'm just not doing well today.
Click to expand...

if only my 'not doing well' days gave me code as good as that! ;)
 

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

JOIN Query very slow 3
Unmatch return query 5
how to query data as of a specific date 3
Union Query question 1
SELECT Statement Question 5
Query for change in Responsibility 4
next date?? 1
Multiple table select query remove duplicates 2

Top