Results 1 to 4 of 4
  1. #1
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14

    Design Help for Novice Access User

    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]);

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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?

  3. #3
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14
    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.

  4. #4
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14
    I realize now that I should have posted my problem in the Query section fo the forum. My bad.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-12-2011, 07:24 AM
  2. Novice User Needing Help Building a Database
    By softspoken in forum Database Design
    Replies: 1
    Last Post: 10-11-2011, 09:29 AM
  3. Novice User Help Please
    By bmschaeffer in forum Access
    Replies: 18
    Last Post: 08-26-2011, 09:12 AM
  4. Access novice help with refresh/requery
    By cvacgreg in forum Access
    Replies: 3
    Last Post: 02-04-2011, 08:51 PM
  5. Very Novice User needs HELP with Form
    By Insanity Queen in forum Forms
    Replies: 1
    Last Post: 12-02-2010, 01:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums