I have a simple, three field database using Access 2000, SP3. I am a casual user of Access and don't know a lot about it. Yesterday I noticed that in a database of my CD collection, if I sorted by Artist Name, it showed one number of records. When I sorted by Album Title, it showed a different number. The third field is genre of music. I can't figure out whey there's a difference in the number of records. The difference is six. I'd appreciate any help or suggestions, again, consider that I am not an expert user of Access. Thanks. (Windows XP Pro operating system.)

One assumes that every Album Title has an Artist field filled in?

I think what David means is that the sum() Form function, and the count() SQL function will ignore Null fields in the count. Check that none of the fields (Artist Name, Album Title, and genre of music) are blank. I suspect that he is correct and you probably have some Blank entries. Filling them in will fix the count issues

Thanks. I checked the entire database and every field in all 393 records is filled in. That's the count when I sort by "Album". The count when I sort by "Artist" is 387. The third field is "Category" which gives me 393. The actual database reads:

ARTIST ALBUM CATEGORY

If it helps, I've actually posted the database on the link to it is:

www.folkusa.org/CD_DISKS.mdb

Hmmm. Unless I'm not using my copy of Access right (I just installed 2007), this db only has 1 object in it and it is a linked file, which was not at the link you posted. I am highly suspicious that the other program that serves up the linked table is giving you the incorrect responses.

What format is the file in? Can you post that, also?

Okay, you have me wondering now. I think that years ago, I made the original database in DBASEIII. Later on, I got Office 2000. Could I have converted the DBASEIII database to Access? Would Access have allowed me to do that? If so, that could be the link. I haven't used DBASEIII in years. Probably not since I got Office 2000. Any new CDs I got I entered directly into Access. If this is the case, and it probably is, can I fix it or do I have to start a whole new database in Access and type everything in again? Thanks.

What you have posted has the one table linked to C:\DBASE111 and it can't be opened because the DBASE111 is not there.

When you link you don't convert the source of data to Access. For example, you can link Access to Excel or a text file.

I am not 100% sure but I think if can link you could also import the same data to Access. So perhaps you can import from DBASE111 and the attach that to a post.

Edit. I just checked and DBASE111 is listed under file types for importing into Access

It's unlikely you'll have to start again. Just create a new table like the old table and move all the data from the old "linked" table. Verify the integrity of the data and then delete the old linked table.

Was I accurate in my assessment that the only object in the DB is the one linked table?

Thanks. I'll have to try and figure that one out. I did go in and create a new table with the same three fields, and called the file cd_disks2 (the original is cd_disks). Now I'm trying to find how to move the date from the original file to the new one. Is that what I'm supposed to be doing? Sorry I'm not an experienced user here.

Since the two tables each have the same three fields, the new one can be updated with the following query:

INSERT INTO cd_disks2 (SELECT * FROM cd_disks)

Once this is done you should have a local table with the correct information in it and you can work towards a solution.