473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Select Query Returns Incorrect Data

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
6 4388
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dave | last post by:
Hi, I was wondering if anyone can help me with this query. I have two tables. table_1 is a master table that contains all records. table_2 contains IDs of some records from table_1 and flags...
0
by: Tanamon | last post by:
Hello All, I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries...
5
by: Pat L | last post by:
I have a function that is designed to return a variable that contains concatenated values from a partinular field in the returned rows: DECLARE @output varchar(8000) SELECT @output = CASE...
3
by: Hendry Taylor | last post by:
I have a problem where if I issue a select * from against a database it returns no data, but if I select column from it returns the data. Why would the * not be working as a wildcard?
4
by: Elroyskimms | last post by:
Using SQL 2000... tblCustomer: CustomerID int CompanyName varchar(20) HasRetailStores bit HasWholesaleStores bit HasOtherStores bit tblInvoiceMessages:
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
4
by: Anna | last post by:
Hi all, I have a query in ASP.NET that selects text from a memo field in Access (among other things). The query appears to be truncating the text at 255 characters. I've done a little research...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: johnhanis | last post by:
I'm using a Visual Basic front end with an SQL query to select some data from a MS Access database. I have a table named Tithes with Columns of TitheDate Tither No Total Tithes Faith Promise...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.