Design Help for Novice Access User

[jk1809]

Hi,


I am having to use an inherited Access database in my new job and I need to revise it. I don't know Access, I am learning Access on my own time. The database consists of 3 main tables: Part table, damage table, new records table. Part table has a column PartNumber where records are of the format XXXXXXXXX-YYYY, XXXXXXXXX-ZZZZ. The basic 9 digit number (XXXXXXXXX) can have multiple dash numbers (YYYY, ZZZZ, AAAA, etc.). New records table has column PartNumber with same record format, XXXXXXXXX-YYYY. I need to check New records table basic 9 digit number against Part table basic 9 digit number and if found, add New record to damage table. I generated the query below but it adds duplicate new records to the damage table. Can anyone help me out?

The append query is comparing the new records table with the part table and restricting the comparison to only the first 9 digits. The problem is that the part table contains part numbers such as fictitious PN 68A320009-1009, 68A320009-1010, etc.. So there almost always be more than one match in the part table. What needs to happen is to stop the comparison as soon as 1 match is found, append the new record to the damage table, and then go to the next record to compare.


INSERT INTO [damage table] (PartNumber)
SELECT [new records] (PartNumnber)
FROM [new records] INNER JOIN [part table] ON LEFT([new record].[PartNumber],9)=LEFT[part table].[PartNumber,9]);

[orange]

What documentation for the database do you have?

to use an inherited Access database in my new job and I need to revise it

Are you a user, dba,or???
Why do you have to revise it? Perhaps it does everything it id suppose to do. Who are the other users? What do they think?
Is there a data base group/person? What sort of organization/business is this?

I think there are many questions that evolve from your post that need some answers and/or context before you start dealing with table structures, logic or SQL. You say you have little experience with Access, but what about data base generally.

Who wrote the append query? Why is it only checking left 9 characters/digits?

[jk1809]

I am an electrical engineer doing damage trend analysis looking at the previous 5-year period. I am the only person using this database. It was created by a person holding this position several years ago and there is no documentation for the database.

I wrote the append query for the following reason. The old query written by the database originator checked for the whole part number (9 digit plus dash number) matches between the new record table and the part table. Including the dash number resulted in too many new records that did not get appended to the damage table because part number dash numbers frequently change and there is no provision in the database to add new dash numbers in the part table. By restricting the comparison to just the 9 digit portion of the part number, I can quickly determine the high count damage areas and then look at dash number counts as a next step in the investigation.

Possibly there is a better solution than trying to use a JOIN funtion, but if I can make it stop after 1 match is found and then go to the next record, it should work. Maybe a COUNT of the matches and if COUNT >1, only add the record to the damage table 1 time.

Thanks for responding.

[jk1809]

I realize now that I should have posted my problem in the Query section fo the forum. My bad.