Discrepancy in field numbers

[Jen0dorf]

Hi

thought yesterday I had sorted my first Access project, learned a lot and was pleased everything worked as I want.

Ran some test searches and the results were to say the least not what I expected in every case.

Spent the day looking and could find nothing obvious as the search worked sometimes.

So I went back to the start and looked at the two tables I was given from a really old Database pre XP.

TWo tables Graves and Memorials both contain a field called Mlink which was used in some compiled way as a link.

memorials has 17662 records

Memorial has 17662 Mlink fields

Graves has 39201 records

Graves had 18093 Mlink fields

I obtained these figures by using the code:

SELECT Memorials.MLink, Memorials.Plot, Memorials.GraveNo
FROM Memorials
WHERE (((Memorials.MLink) Is Not Null));

similarly for Graves.

So this means that there are 431 Mlink entries in the table Graves that I would like to delete as they have no reference to the table memorials

Using the query wizard I created the query

SELECT Graves.MLink, Graves.Plot, Graves.GraveNo, 
FROM Graves LEFT JOIN Memorials ON Graves.[MLink] = Memorials.[MLink]
WHERE (((Memorials.MLink) Is Null));

Which shows only 278 mlinks in the table Graves that are not in the table memorials

So I have a discrepancy of 153



Is my logic at fault? Don't really want to start thinking about deleting until I understand why I have these two different figures.

Thanks

Ian

[orange]

Ian,

It may be easier to help you if readers had a copy of the database to work with. We could run some queries and try to make sense of the data. I hate to be negative but it seems to me, you are still trying to sort out what the original database was about. By that I mean some of these "apparent discrepancies" are still showing up as surprises.

[Jen0dorf]

Hi

thanks for the input, I thought about posting a copy of the database but it's just to big and if I delete the data then it would be impossible to see the issue.

As to what the original database was about I originally posted a copy and the answer cam back that it was not a true access database and that without the core programming code I was wasting my time. Hence the re write.

In terms of functionality the database is to search either by year or Surname and then display the results. In the great scope of databases not a particularly difficult job I suppose. However its importing the 39201 records that has proved to be the most difficult.

The original tables had no primary keys, No relationships the only thing that linked the two tables was the field Mlink which was not unique and formated as text.

What I missed was that both tables did not have at least one matching Mlink field.

Anyway I'll soldier on

thanks again

Ian

[orange]

Hopefully you have a copy of the original in a safe place.
And you have made a copy to work with.

Always make a backup ---it has saved many of us and is a proven practice.
Work with copies, and back those up as well.

I am willing and offering help, but we need some facts and some data to work with.