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

[Cesar Urquidi]

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???

 

[Marshall Barton]

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])

 

[Cesar Urquidi]

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

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])

 

[Lord Kelvan]

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

 

[Cesar Urquidi]

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!!!

 

[Lord 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

 

[Cesar Urquidi]

Hello Kelvan/Marshall,
Please read the post from the beginning and help me figure it out.

 

[Marshall Barton]

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.

 

[Lord Kelvan]

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

 

[Cesar Urquidi]

It wooooooooorked!!!

Thank you Marshall!
Thank you Kelvan!