Number of records is diffirent in Table and Query

[hmsahir4u]

Dear Fourm members.
I am facing a very strange problem. I have table when I open it I found total number of records 1000 while I get result through query I found that 2 records or more than present in the Table mean in query number of record is 1002. I donot understand why these tow records are not seen in the actual table. and these are not deleted by delete record qurey. While I can see it in select command.
Waiting for positive reply as earl as possible

[John_G]

Does your query contain more than one table? If it does, please post the SQL of the query.

John

[hmsahir4u]

Dear Sir
I could send you the data base file if you will ask. how ever SQL of Query is as under

SELECT Transaction_Detail.Debit, Transaction_Detail.Credit, Transaction_Detail.Price, Transaction_Detail.Qty, Transaction_Detail.Discription, accountmaster.accuname, achead.accatname, Transaction_Detail.VANumber, Transaction_Detail.Vnumber, Transaction_Detail.AccountID
FROM (Transaction_Detail INNER JOIN (accountmaster INNER JOIN achead ON accountmaster.accatname = achead.accatname) ON Transaction_Detail.AccountID = accountmaster.acid) INNER JOIN Transaction_Main ON Transaction_Detail.Vnumber = Transaction_Main.TransId
ORDER BY Transaction_Detail.VANumber;

It contain multiple tables

[John_G]

Hi -

Which is the table that looks like it has too many records?

The issue is with your data, not MS Access. Your problem is happening because one of your joins, which you expect to be referring to only one record, actually refers to more than one, and your query wil contain one line for each of them.

It's only a guess, but check accatname in table achead, and acid in table accountmaster. One of those may be duplicated by mistake (I would guess it might be accatname)

HTH

John

[hmsahir4u]

Dear John
I would like to tell you the background for this error. The Software which I have designe for a small pesticide shop was working accuratly from last 1 year. This problem was due the electricity failure during the saving process of the entry. So when the operator start the computer he saw these transaction in the transaction detail table. He has tried to delet these records but records are not deleted from the table so this problem has been seen. I wonder that why this database is not deleting that entries. while it was working good in last year.
Hope the you have got my point.
Regards
Hamid!

[John_G]

Hi -

A power failure during a save - you may have a corrupted database. Have you tried a compact and repair? That's always the first step. In this case (and in any case really), make a backup copy before doing the compact and repair.

Just a question - when your user tries to delete the records, but can't - is there any error message shown?

John

[hmsahir4u]

Hi
I have not tried compact and repair yet because everything except these two records are in good condition and the program is running well. There is no error when we use delete command and as I have told you that the record is not visible in Transaction Detail table but when I use query in Access it become visible in query. and the same record is visible in my VB.net base software.
I will try to repair it.
Thanks
Hamid

[hmsahir4u]

Dear John,
Thank you for your great contribution and cooperation to resolve the problem. The problem was solved using "Compact and Repairing" Utility
Regards

Hamid

[John_G]

Hi -

Glad to hear your problem has been resolved. If you are in a part of the world where power failures are common, you should consider making it a part of your daily procedure to make a backup copy of the database.

Cheers!

John

[hmsahir4u]

Dear John, It was wonderful information for me. Although I am living in Pakistan which has worst power failure problem but I always advise to my customers to use UPS with the system at which software is running. I have made an Inventory and finance software for my local language on self study bases. Thanks once again for your suggestions. Regards! Hamid