No record in a report

[groul]

Hello hneal (hopefully it's you who is reading this),
using part of your solution from my previous problem for another report, I am stuck again. Following is the contents of the "Select Query":SELECT DISTINCTROW Lib1.MANUAL, Lib1.BOOK, Lib1.TITLE, Lib1.PARTNB, Lib1.TYPE, Lib1.ATA, Lib1.VENDOR, Lib1.[LAST AUDIT], Lib1.REMARKS, Lib1.Dash, Lib1.Last_T_R, Lib1.T_R_Date, Lib1.ShouldRev, Lib1.VENDOR1, Lib1.Series, Lib1.PreparedBy, Lib1.For_Ref_Only, Lib1.SP_COPY, Shop1.Shop, Shop1.Audit_NB, Shop1.To_Shop, Shop1.ShopNb, Shop1.Cap_List, Rev_History.Rev, Rev_History.Date
FROM (Lib1 INNER JOIN Shop1 ON Lib1.MANUAL = Shop1.Manual) INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE (((Lib1.MANUAL)=[forms]![lib1]![manual]) AND ((Shop1.Shop)<>"") AND ((Shop1.Cap_List)=Yes) AND ((Rev_History.Date)=(Select Max(Rev_History.Date)from Rev_History)));
Unfortunately, there will be no record shown in the report, any idea?

 

[dhookom]

groul,
What have you tried with this query to see if you can return any records? What happens as you remove criteria one-by-one from a copy of this query? Also, if Cap_List is a yes/no field, replace the word Yes with -1.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

Hello Duane,
If I remove this:"AND ((Rev_History.Date)=(Select Max(Rev_History.Date)from Rev_History)))", the report generates all required fields/records. Except if more then one record exists in the table "Rev_History" for a particular Manual, the report generates multiple records of fields: Shop1.Shop, Shop1.Audit_NB, Shop1.To_Shop, Shop1.ShopNb, Shop1.Cap_List.
Or in other words, if only one record exist for a particular "Manual" in the table "Rev_History", the report generates a correct output.
Hope it is clear enough??

 

[dhookom]

There should be a space in:
AND ((Rev_History.Date)=(Select Max(Rev_History.Date)from Rev_History)))
before the "From":
AND ((Rev_History.Date)=(Select Max(Rev_History.Date) from Rev_History)))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

Duane,
It now looks like as foll in the SQL View:
SELECT DISTINCTROW Lib1.MANUAL, Lib1.BOOK, Lib1.TITLE, Lib1.PARTNB, Lib1.TYPE, Lib1.ATA, Lib1.VENDOR, Lib1.[LAST AUDIT], Lib1.REMARKS, Lib1.Dash, Lib1.Last_T_R, Lib1.T_R_Date, Lib1.ShouldRev, Lib1.VENDOR1, Lib1.Series, Lib1.PreparedBy, Lib1.For_Ref_Only, Lib1.SP_COPY, Shop1.Shop, Shop1.Audit_NB, Shop1.To_Shop, Shop1.ShopNb, Shop1.Cap_List, Rev_History.Rev, Rev_History.Date
FROM (Lib1 INNER JOIN Shop1 ON Lib1.MANUAL = Shop1.Manual) INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE (((Lib1.MANUAL)=[forms]![lib1]![manual]) AND ((Shop1.Shop)<>"") AND ((Shop1.Cap_List)=-1) AND ((Rev_History.Date)=(Select Max(Rev_History.Date) from Rev_History)));
But the report output is the same: Nothing.

 

[dhookom]

Do you realize that the subquery will return only one date for your entire recordset? I would expect to see something in the subquery such as:[blue]
=(Select Max(Rev_History.Date) from Rev_History rh WHERE rh.Ref = Rev_History.Ref)[/blue]
If you don't include some type of where clause in the subquery, the subquery will return only the max Date of the entire table. I don't think you want that.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

Yes, I do realize this, in fact this is what I want. One date (and its relevant revision number) only. The report is suppsed to show all shops, for which I got: (Shop1.Cap_List)=-1) and one revision/date only - the latest one.
Using your last proposal, the report generates the same problem as before. One will see all required fields, though, because the table "Rev_History" has got more than one record (revision 5 and 6, e.g.), one will see the revision number and revision date one time, but the same shop two times in the report (because of the two revision numbers/dates). The report is supposed to show the shop one time only and if the "(Shop1.Cap_List)=-1" is effective to two or more shops, the report should show each shop one time and only the latest revision/date.

 

[dhookom]

You are only returning one date from the entire table in the subquery. You should run just the subquery on its own to get the date value. Assuming it is something like #7/23/2004#, remove the subquery and hard-code the value #7/23/2004# in its place. Does the query with the date substituted for the subquery return any records?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

If I remove this:"=(Select Max(Rev_History.Date) from Rev_History)", or "=(Select Max(Rev_History.Date) from Rev_History rh WHERE rh.Ref = Rev_History.Ref)" and replace it with a fixed - existing - date, it works fine for a particular report. It works fine also for any report, if I replace above with either "[Which Date]", or "[Which Rev]" in its respective query field.

 

[dhookom]

That is what I expected from looking at your sql. By "it works fine for a particular report" do you actually mean that it works fine for a particular book or manual record? If so, is it the book or manual that was reviewed most recently?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

I probably did not express myself good enough. What I meant is that it works fine for any report, for which I use the relevant listed revision date. Not quite sure whether I understand your question concerning the book and manual, but both can be seen, like all other required fields in the report.

 

[dhookom]

Ok, since I don't know your data let me suggest a simple example and you can tell me if it applies. Assuming data like:
[tt]
Manual ReviewDate ReviewNum
ManA 4/1/2004 1
ManA 5/1/2004 2
ManB 4/15/2004 1
ManB 5/12/2004 2
ManB 6/1/2004 3
[/tt]

Now, assuming you want to create a query/report that shows the Manual, ReviewDate, and ReviewNum for only the most recent review of each manual. The following would work:

SELECT tblReviews.Manual, tblReviews.ReviewDate, tblReviews.ReviewNum
FROM tblReviews
WHERE tblReviews.ReviewDate=(Select Max(ReviewDate) FROM tblReviews r where r.Manual = tblReviews.Manual);

The following would work only for the manual with the most recent review (ManB):

SELECT tblReviews.Manual, tblReviews.ReviewDate, tblReviews.ReviewNum
FROM tblReviews
WHERE tblReviews.ReviewDate=(Select Max(ReviewDate) FROM tblReviews);

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[groul]

Duane,
I decided to keep the structure of the SQL with "[Which Date]". This way one may produce a previous report (e.g.for comparision, if wanted).
Thank you very much for your help
Ulli