Too many records shown in a report 1

[groul]

Hello,
My Access knowledge is not that good to solve following problem: I have two tables/forms. The main form (called "Lib1") includes an embedded form (called "Rev_History", Default View: Data Sheet). Basically the "Lib1" details do not change, but the "Rev_History" details will be updated. So one record of the "Lib1" may refer to more than 1 record in "Rev_History". When I print out a report, only the last revision details should be seen, but it shows all previous revision details too. Here is what can be seen in the SQL View of the query:
SELECT DISTINCT Lib1.BOOK, Lib1.PARTNB, Lib1.MANUAL, Lib1.ATA, Lib1.TITLE, Lib1.VENDOR, Rev_History.Date, Rev_History.Rev
FROM Lib1 INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE (((Lib1.BOOK)=[Forms]![Lib1]![BOOK]))
ORDER BY Lib1.MANUAL;

Can you give me a hint how to correct this.
Thank you
Ulli

 

[hneal98]

Do you have a date field for the Rev_History? IF so, you should be putting that in the query to get the max date.

 

[groul]

Hello hneal,
Thank you for your fast reply, yes, there is a date field in "Rev_History". However, can you explain a bit more in detail concerning "max date".

 

[hneal98]

SELECT DISTINCT 
   Lib1.BOOK, Lib1.PARTNB, 
   Lib1.MANUAL, Lib1.ATA, 
   Lib1.TITLE, Lib1.VENDOR, 
   Rev_History.Date, Rev_History.Rev
FROM 
   Lib1 
   INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE 
   (((Lib1.BOOK)=[Forms]![Lib1]![BOOK]))
   AND [HISTDATE].text = (Select max([HISTDATE].text) from Rev_History)
ORDER BY 
   Lib1.MANUAL;

the [HISTDATE] would actually be whatever you named that date field.

 

[hneal98]

Correction: I just noticed a couple of problems with the query above. What links the history table to the Lib table?

 

[groul]

Both tables are linked with "Ref" = AutoNumber

 

[hneal98]

Not sure what you mean by "Ref" = AutoNumber. Does that mean that both tables have a field called "Ref" and it is an autonumber?

 

[groul]

Correct, both tables got a field called "ref" and both are of Data Type "AutoNumber".

 

[hneal98]

Ok, then it should be like this:

SELECT DISTINCT 
   Lib1.BOOK, Lib1.PARTNB, 
   Lib1.MANUAL, Lib1.ATA, 
   Lib1.TITLE, Lib1.VENDOR, 
   Rev_History.Date, Rev_History.Rev
FROM 
   Lib1 
   INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE 
   Lib1.BOOK=[Forms]![Lib1]![BOOK]
   lib1.Ref = h1.Ref 
   AND [HISTDATE].text = (Select max([HISTDATE].text) from Rev_History h2 where h1.Ref = h2.Ref)
ORDER BY 
   Lib1.MANUAL;

I think this should work. It is hard to tell without seeing what you are actually trying to do in your report.

 

[groul]

Sorry, if I am bothering you, but this does not work. As soon as I like to save the query, an error message comes up: Syntax error(missing operator) in query expression 'Lib1.BOOK=[Forms]![Lib1]![Book] Lib1.Ref = h1.Ref
AND [HISTDATE].text= (Select max([HISTDATE].text from Rev_History h2 where h1.Ref=h2.Ref)'

The Report, as is at the moment, looks like:
BOOK#: 274
Manual PartNB Title Vendor ATA Rev Date
AK32 1-x00-181- chime Telephonics 23-42-30 5 30.12.1978
AK46 008.100- Heater B/E Aerosp 25-30-47 6 01.07.2004
AK46 008.100- Heater B/E Aerosp 25-30-47 5 01.06.1982

The 'Manual', 'PartNB', 'Title', 'Vendor', 'ATA' are obtained from table "Lib1", the 'Rev' and 'Date' from table "Rev_History".
The report is used to be inserted in front of a Book, e.g. Book# 274, as a List of Manuals (which can be found in this Book). The List should NOT show any previous revision, but the latest ones only (like in this sample it shows rev. 5 and 6 but should show rev.6 only.
I really hope this is more clear and you may find a solution to the problem.

 

[hneal98]

Sorry, I left out an AND. It should be like this:

SELECT DISTINCT 
   Lib1.BOOK, Lib1.PARTNB, 
   Lib1.MANUAL, Lib1.ATA, 
   Lib1.TITLE, Lib1.VENDOR, 
   Rev_History.Date, Rev_History.Rev
FROM 
   Lib1 
   INNER JOIN Rev_History ON Lib1.REF = Rev_History.Ref
WHERE 
   Lib1.BOOK=[Forms]![Lib1]![BOOK]
   AND lib1.Ref = h1.Ref 
   AND [HISTDATE].text = (Select max([HISTDATE].text) from Rev_History h2 where h1.Ref = h2.Ref)
ORDER BY 
   Lib1.MANUAL;

 

[groul]

Very sorry, it still does not work out.
As soon as I try to generate the report, following appears:
Enter Parameter Value
HISTDATE.Text (or Date.Text, if I change HISTDATE to Date, because the date field is named "Date"), and thereafter,
Enter Parameter Value
h1.Ref

Of course the result is an empty report. Any other idea, please?

 

[hneal98]

You put [date].text, right? I just put HistDate for an example. It should be whatever you named the field.

 

[groul]

I used both, [HISTDATE].text and [Date].text, but in both cases the result is as described in my last message. I am very sorry that my problem creates such a lot of difficulties, though I still hope to get a solution.
Ulli

 

[hneal98]

Don't know what happened, but somewhere I left off the h1 and h2. Look here:

SELECT DISTINCT 
   Lib1.BOOK, Lib1.PARTNB, 
   Lib1.MANUAL, Lib1.ATA, 
   Lib1.TITLE, Lib1.VENDOR, 
   h1.Date
FROM 
   Lib1 
   INNER JOIN Rev_History h1 ON Lib1.REF = h1.Ref
WHERE 
   Lib1.BOOK=[Forms]![Lib1]![BOOK]
   AND h1.[DATE].text = (Select max(h2.[DATE].text) from Rev_History h2 where h1.Ref = h2.Ref)
ORDER BY 
   Lib1.MANUAL;

 

[groul]

Hello hneal,
It is really kind of you to spend such a lot of time, however, the last solution doesn't work either. I added to "SELECT DISTINCT" h1.Rev.
However it still does not work. As soon as I try to generate the report, following appears:
Enter Parameter Value
h2.Date.Text and thereafter,
Enter Parameter Value
h1.Date.Text.

 

[hneal98]

I am not sure what you mean by "SELECT DISTINCT" h1.Rev.

Can you explain to me what you mean? If you are getting the Enter Parameter Value, that generally means that you Access does not see that field. In that case, you need to go back and make sure you have the correct field names.

 

[groul]

Actually your last code does not include the field "Rev", that is why I added "h1.Rev". I copied your code and pasted it into the Query Builder (SQL View), the table name "Rev-History" changes then to "h1". The Control Source for the field names "Rev" and "Date" are coming then from table "h1". But the "Enter Parameter Value" "h2.Date.Text" and "h1.Date.Text" still appear.

 

[hneal98]

SELECT DISTINCT 
   Lib1.BOOK, Lib1.PARTNB, 
   Lib1.MANUAL, Lib1.ATA, 
   Lib1.TITLE, Lib1.VENDOR, 
   h1.Date,
   h1.Ref
FROM 
   Lib1 
   INNER JOIN Rev_History h1 ON Lib1.REF = h1.Ref
WHERE 
   Lib1.BOOK=[Forms]![Lib1]![BOOK]
   AND h1.DATE = (Select max(h2.DATE) from Rev_History h2 where h1.Ref = h2.Ref)
ORDER BY 
   Lib1.MANUAL;

New Day. Got rid of the .text, that is for form fields. Sorry. I knew there was something stupid in there somewhere

 

[groul]

Hello hneal,
This makes sense. It works!
Thank you so much.
Ulli