[Access SQL] Missing Records with SubQuery

[rack]

I created this subquery, which I thought would pull the Account Number, CustomerName, Status, OriginalBalanceDue, CurrentBalanceDue. However there are 4 accounts missing.


Could you look at my sql, and see if I am just missing something?

SELECT T1.AccountNumber, 
    (SELECT TOP 1 T2.FULLNAME FROM tblAKDCustomer AS T2 WHERE T2.AccountNumber=T1.AccountNumber ORDER BY T2.ImportDate DESC, T2.FullName) AS CustomerName, 
   tblAKDStatus.Name AS Status, 
   (SELECT TOP 1 T3.BalanceDue FROM tblAKDBalanceDue AS T3 WHERE T3.AccountNumber=T1.AccountNumber ORDER BY T3.BalanceDueDate, T3.ImportDate, T3.BalanceDue) AS OriginalBalanceDue, 
   (SELECT TOP 1 T3.BalanceDue FROM tblAKDBalanceDue AS T3 WHERE T3.AccountNumber=T1.AccountNumber ORDER BY T3.BalanceDueDate, T3.ImportDate DESC, T3.BalanceDue) AS BalDueToDate
FROM tblAKDStatus INNER JOIN tblAKDAccount AS T1 ON tblAKDStatus.StatusID=T1.StatusID
ORDER BY tblAKDStatus.Name, T1.AccountNumber;

Even if I remove most of the other information, I still end up missing 1-2 records. Its odd, I can run the same query over and over. And in the span of 15 seconds get 3 different totals back. Sometimes it returns all the accounts, sometimes it only returns some of them.

SELECT T1.AccountNumber, 
   (SELECT TOP 1 T2.FULLNAME FROM tblAKDCustomer AS T2 WHERE T2.AccountNumber=T1.AccountNumber ORDER BY T2.ImportDate DESC, T2.FullName) AS CustomerName, 
   tblAKDStatus.Name AS Status
FROM tblAKDStatus INNER JOIN tblAKDAccount AS T1 ON tblAKDStatus.StatusID=T1.StatusID

How can I better write my SubQuery so that it is not randomly getting less records?

[rack]

I tried adding the primary key to the sort order, to help make sure that the subquery TOP 1 wasn't combining items of similar values, however I was still short records.

If I do a simple combine of the data, I get the correct amount of records.
How do I grab the BalanceDue for the Oldest BalanceDueDate?
How do I grab the BalanceDue for the Newest BalanceDueDate?
How do I grab the FullName for the Newest ImportDate?

SELECT tblAKDAccount.AccountNumber, tblAKDCustomer.FullName, tblAKDBalanceDue.BalanceDue, tblAKDStatus.Name
FROM tblAKDStatus INNER JOIN ((tblAKDAccount INNER JOIN tblAKDCustomer ON tblAKDAccount.AccountNumber = tblAKDCustomer.AccountNumber) INNER JOIN tblAKDBalanceDue ON tblAKDAccount.AccountNumber = tblAKDBalanceDue.AccountNumber) ON tblAKDStatus.StatusID = tblAKDAccount.StatusID;

Table Structure

tblAKDAccount
AccountNumber - Primary Key Text Chr(13)
StatusID - Number

tblAKDCustomer
CustomerID - Primary Key - AutoNumber
AccountNumber - Text Chr(13)
FullName - Text
ImportDate - Date/Time

tblAKDBalanceDue
BalanceDueID - Primary Key - AutoNumber
AccountNumber - Text Chr(13)
BalanceDue - Currency
BalanceDueDate - Date/Time

tblAKDStatus
StatusID - Primary Key - AutoNumber
Name - Text Chr(20)
Description - Text Chr(100)

[rack]

Is my SQL wrong?

Is there a different, or better way to get what I need?

SELECT Field1, (SELECT TOP 1 SelectedField 
                      FROM table2 
                      WHERE Conditions 
                      ORDER BY Fields) AS Field2
FROM table1
WHERE conditions
ORDER BY fields

[FunkyDexter]

What do you mean the records are 'missing'? Are they just not showing at all? or does the account get returned but you get a null returned from the sub-queries?

If the record doesn't get returned at all then the problem is probably not with you sub query, it's with you main query and I'm willing to bet it's a problem with the join. Do all you accounts have a status? If not they won't get returned from that join.

[rack]

Each account does have a status. I just did a simple Distinct Query on the StatusID field, and it did not return any extra, or unwanted values.

The data returned does not have NULL's, but rather, full rows are missing, meaning that an account number wasn't returned with associated data.

Editing the items in the Order By Clauses of the SubQueries restricts the returned results, even if I add more items to hopefully eliminate the possability it is removing similar entries.

If I remove the Status Lookup table, and simply display the Status ID, I get more records, but am still missing 6.

SELECT T1.AccountNumber, (SELECT TOP 1 T2.FULLNAME FROM tblAKDCustomer AS T2 WHERE T2.AccountNumber=T1.AccountNumber ORDER BY T2.ImportDate DESC, T2.FullName) AS CustomerName, T1.StatusID, (SELECT TOP 1 T3.BalanceDue FROM tblAKDBalanceDue AS T3 WHERE T3.AccountNumber=T1.AccountNumber ORDER BY T3.BalanceDueDate, T3.ImportDate, T3.BalanceDue) AS OriginalBalanceDue, (SELECT TOP 1 T3.BalanceDue FROM tblAKDBalanceDue AS T3 WHERE T3.AccountNumber=T1.AccountNumber ORDER BY T3.BalanceDueDate, T3.ImportDate DESC, T3.BalanceDue) AS BalDueToDate
FROM tblAKDAccount AS T1
ORDER BY T1.StatusID, T1.AccountNumber;

[rack]

Also, if I right click the query, and go to Datasheet View, wait for it to show the max number of records (26357), then go back to SQL view, then back to Datasheet view, it will be different (26354).

Its like its randomly dropped records?

Should a query always return the same result set if the tables have not be altered?

[FunkyDexter]

If whole records are missing then your sub queries aren't the problem. How many records are there in tblAKDAccount? That is the number you're latest query should return.

[rack]

There are 26,357 Records.

But that last query will pull anywhere from 26,251 to 26,357 records. If I run it over and over and over each time it comes back with total records somewhere inbetween those 2 numbers. Its werid.

[brucevde]

I would run a "Compact and Repair" on the database.

In my experience with Access, "weird" results returned by a query were caused by some sort of corruption.