On Apr 14, 4:09 am, "breadon" <breadon...@yahoo.co.ukwrote:
Hello Bill
Probably its easiest to start from your query in design view. Show
both [Table A] and [Table B], joining them on [statement id]
graphically. Ensure that you have [Table A].Date in the data grid.
Remove any grouping that has been applied.
The subquery itself is the bit in brackets:
(select max(a.statement_date) from tableA
a, tableB b where a.statement_id = b.statement_id and b.statement_id
=
tableB.statement_id)
This needs to be entered in as a CRITERIA under
[Table A].Date. You MUST include the parentheses () at the beginning
and end of the subquery. With spaces in table and field names, as in
your example, the subquery will need to be more like (AIR CODE):
(select max(a.[date]) from [table A]
a, [table B] b where a.[statement id] = b.[statement id] and b.
[statement id] =
[table B].[statement id])
Hope this helps.
Regards
Breadon
Hi Breadon,
Here is my subquery
(select max(StatementDate) from tblBankStatements, tblMemberRepayments
where tblBankStatements.StatementID = tblMemberRepayments.StatementID
and tblMemberRepayments.StatementID = tblBankStatements.StatementID)
I created a test query and the subquery works however it returns only
records for one day.
tblBankStatements has SatementDate and tblMemberRepayments have LoanID
and RepaymentAmt
There are some 50,000 records when the query has not criteria.
The result should be a list of LoanID once only with the Last Date and
Amount. I should get some 6,000 records however only rec'd 7 for one
specific day.
Any idea?? appreciate your help and best regards,
Bill