By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

Select Query Returns Incorrect Data

P: n/a
I have simple Select Query using two small tables.
Table A includes Statement ID & Date.
Table B includes Statement ID, Loan ID & Payment Amount.
The Query selects payments and amounts from Table B and the Date from
Table A of each Payment.
When I use Group to Select the Latest Payment for a given Loan ID it
often selects the 2nd, 3rd or 4th Payment when the ungrouped result
shows all repayments.

Apr 2 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Apr 2, 7:59 pm, bill.mckins...@clubgroup.com.pg wrote:
I have simple Select Query using two small tables.
Table A includes Statement ID & Date.
Table B includes Statement ID, Loan ID & Payment Amount.
The Query selects payments and amounts from Table B and the Date from
Table A of each Payment.
When I use Group to Select the Latest Payment for a given Loan ID it
often selects the 2nd, 3rd or 4th Payment when the ungrouped result
shows all repayments.
Sorry, forgot to mention - ms access 2000. Appreciate any assistance.

Apr 2 '07 #2

P: n/a
Hi Bill

You need to use a subquery rather than a group by query. There is a
help topic 'SQL Subqueries' that describes subqueries - not sure its
in your version of Access though.

Solution should look something like:

SELECT tableB.loan_id, tableB.payment_amount
FROM tableA INNER JOIN tableB ON tableA.statement_id =
tableB.statement_id
WHERE tableA.statement_date=(select max(a.statement_date) from tableA
a, tableB b where a.statement_id = b.statement_id and b.statement_id =
tableB.statement_id)

You would have to use square brackets around table and column names if
you really do have spaces in your example.

Regards
Breadon
Apr 2 '07 #3

P: n/a
On Apr 2, 10:10 pm, "breadon" <breadon...@yahoo.co.ukwrote:
Hi Bill

You need to use a subquery rather than a group by query. There is a
help topic 'SQL Subqueries' that describes subqueries - not sure its
in your version of Access though.

Solution should look something like:

SELECT tableB.loan_id, tableB.payment_amount
FROM tableA INNER JOIN tableB ON tableA.statement_id =
tableB.statement_id
WHERE tableA.statement_date=(select max(a.statement_date) from tableA
a, tableB b where a.statement_id = b.statement_id and b.statement_id =
tableB.statement_id)

You would have to use square brackets around table and column names if
you really do have spaces in your example.

Regards
Breadon
Hi Breadon,

Thanks for your advice. I am not skilled at SQL.
Would I put the above withing an Access Query ??

Regards,
Bill

Apr 10 '07 #4

P: n/a
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

Apr 13 '07 #5

P: n/a
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

Apr 17 '07 #6

P: n/a
Hello Bill

The sub query needs its own private instances of the tables referenced
hence the table aliases 'a' and 'b'. These are vital.

(select max(a.StatementDate) from tblBankStatements a,
tblMemberRepayments b
where a.StatementID = b.StatementID
and b.LoadId = tblMemberReplayments.LoanID)

The final tblMemberReplayments.LoanID is not using the alias and
references the main query's tables.

Regards
Breadon

Apr 20 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.