Select Query

  • Thread starter Thread starter Ben
  • Start date Start date
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?
 
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
 
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
 
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?
 
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...
 
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?
 
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...
 
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?
 
--- 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...
 
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.
 
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...
 
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! ;)
 
Back
Top