Need help with a date on a querie, please!!!

I have an "Equipment" table and an "Issue/Return" table:

Equipment:
l_ Device ID _l_ Device Description _l

Issue/Return:
l_ Issue/Return ID _l_ Device ID _l_ Issue To _l_ Issue Date _l

I created a form from the "Equipment" table with a subform in the bottom
using the "Issue/Return".

Every time I issue a piece of equipment to somebody, I enter it in the
"Issue/Return" subform.

I want to create a querie using both tables and be able to display only the
latest record of each piece of equipment that I issued, using a formula in
the "Issue Date" field.

Tha formula has to say something like: show only latest issue date from
"device id".

What formula do I need to enter in the querie???

--
Thank you,
Cesar Urquidi

Cesar Urquidi wrote:

I have an "Equipment" table and an "Issue/Return" table:

Equipment:
l_ Device ID _l_ Device Description _l

Issue/Return:
l_ Issue/Return ID _l_ Device ID _l_ Issue To _l_ Issue Date _l

I created a form from the "Equipment" table with a subform in the bottom
using the "Issue/Return".

Every time I issue a piece of equipment to somebody, I enter it in the
"Issue/Return" subform.

I want to create a querie using both tables and be able to display only the
latest record of each piece of equipment that I issued, using a formula in
the "Issue Date" field.

Tha formula has to say something like: show only latest issue date from
"device id".


SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID])
--
Marsh
MVP [MS Access]

Hello,
I don't understand!
I created the querie.
Can you please tell me the criteria I need to enter in the design view of
the querie?
--
Thank you,
Cesar Urquidi


"Marshall Barton" wrote:

Cesar Urquidi wrote:

I have an "Equipment" table and an "Issue/Return" table:

Equipment:
l_ Device ID _l_ Device Description _l

Issue/Return:
l_ Issue/Return ID _l_ Device ID _l_ Issue To _l_ Issue Date _l

I created a form from the "Equipment" table with a subform in the bottom
using the "Issue/Return".

Every time I issue a piece of equipment to somebody, I enter it in the
"Issue/Return" subform.

I want to create a querie using both tables and be able to display only the
latest record of each piece of equipment that I issued, using a formula in
the "Issue Date" field.

Tha formula has to say something like: show only latest issue date from
"device id".


SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID])
--
Marsh
MVP [MS Access]

that is an sql statement what you do is open a new query then click on
view in the menu bar and then click on sql view then paste

SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID])


after deleting all text that in in that window then you have to run
the query

Regards
Kelvan

Hello Kelvan,
I tried it, didn't work!!!!!!!!!!!!!!!!!!

I get the following message:

Missing ), ], or Item in query expression 'IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID]'.

Please help me!!!
Please just send me the criteria I need to use!!!

--
Thank you,
Cesar Urquidi


"Lord Kelvan" wrote:

that is an sql statement what you do is open a new query then click on
view in the menu bar and then click on sql view then paste

SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID])


after deleting all text that in in that window then you have to run
the query

Regards
Kelvan

to be honest i dont know i havent read the post just helped you
understand that marshall wrote

(SELECT Max(X.[Issue Date] FROM [Issue/Return] As X WHERE X.[Device
ID] = IR.[Device ID])

should be the criteria for the issue date box

SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID]))

that is the sql seems marshall just missed a ) at the end

IF THE CRITERIA ABOVE DOSNT WORK try the sql

hope this helps

Regards
Kelvan

Hello Kelvan/Marshall,
Please read the post from the beginning and help me figure it out.
--
Thank you,
Cesar Urquidi


"Lord Kelvan" wrote:

to be honest i dont know i havent read the post just helped you
understand that marshall wrote

(SELECT Max(X.[Issue Date] FROM [Issue/Return] As X WHERE X.[Device
ID] = IR.[Device ID])

should be the criteria for the issue date box

SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID]))

that is the sql seems marshall just missed a ) at the end

IF THE CRITERIA ABOVE DOSNT WORK try the sql

hope this helps

Regards
Kelvan

Cesar Urquidi wrote:
I don't understand!
I created the querie.
Can you please tell me the criteria I need to enter in the design view of
the querie?


The criteria is the subquery.

If you would Copy/Paste the SQL I posted into a new query's
SQL view (and change to the names of your table/fields),
then you can switch to design view to see what it looks like
in the query designer.

--
Marsh
MVP [MS Access]

i read it and i read marshalls sql and it shoudl do the job

just open a query then click on view then sql view and replace what it
there with

SELECT IR.*, E.[Device Description]
FROM Equipment As E INNER JOIN [Issue/Return] As IR
ON IR.[Device ID] = E.[Device ID]
WHERE IR.[Issue Date] = (
SELECT Max(X.[Issue Date]
FROM [Issue/Return] As X
WHERE X.[Device ID] = IR.[Device ID]))

what you want is not simple it is quite complex so you need to do it
from the sql view after you have entered it in then you can edit it in
design view but paste the above sql

regards
Kelvan

It wooooooooorked!!!

Thank you Marshall!
Thank you Kelvan!
--
Thank you,
Cesar Urquidi


"Marshall Barton" wrote:

Cesar Urquidi wrote:
I don't understand!
I created the querie.
Can you please tell me the criteria I need to enter in the design view of
the querie?


The criteria is the subquery.

If you would Copy/Paste the SQL I posted into a new query's
SQL view (and change to the names of your table/fields),
then you can switch to design view to see what it looks like
in the query designer.

--
Marsh
MVP [MS Access]