Need Query Help

[jk1809]

Hi, I initially posted this in the database design forum and then realized it needs to go here. All help will be appreciated. I am an engineer that does not know Access very well, learning on the fly!

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

[dandoescode]

Select Distinct sounds like what you need brother. Try this page http://www.w3schools.com/sql/sql_distinct.asp

if it solves your problem mark thread as solved!

[jk1809]

Your saying I should use the SELECT DISTINCT after the INNER JOIN statement?

[dandoescode]

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

try that out.

[ssanfu]

Does it matter which "dash number" gets picked? If you have a "-AAAA" and a "-YYYY" and the "-YYYY" get inserted into the damage table, does it matter? If it does matter, you might want to add an "Order By" clause.

[jk1809]

Hey !!! That seems to work! I had simplified my query when I posted it. Below is what I just generated based on your recommended code structure and it seems to work. But SSANFU who replied after your suggestion may have a valid point. In a case where there multiple new records with same 9 digit number but different dash numbers, I would want the multiple records added to the damage table. He is suggesting adding an ORDER BY clause. What do you think? Will the code below add all multiple records with same 9 digit but different dash number to the damage table?

INSERT INTO [Damage Table] ([Control Number], Unit, [Part Number], [Damage Area], [Flying Hours], [Serial Number], [Tech Data], Deficiency, [Action Required], Comments, Repair, [Final Disposition], [Open Date] )

SELECT DISTINCT [Damage Table (Dirty Data)].[Control Number], [Damage Table (Dirty Data)].Unit, [Damage Table (Dirty Data)].[Part Number], [Damage Table (Dirty Data)].[Damage Area], [Damage Table (Dirty Data)].[Flying Hours], [Damage Table (Dirty Data)].[Serial Number], [Damage Table (Dirty Data)].[Tech Data], [Damage Table (Dirty Data)].Deficiency, [Damage Table (Dirty Data)].[Action Required], [Damage Table (Dirty Data)].Comments, [Damage Table (Dirty Data)].Repair, [Damage Table (Dirty Data)].[Final Disposition], [Damage Table (Dirty Data)].[Open Date]
FROM [Damage Table (Dirty Data)] INNER JOIN [part table] ON LEFT([Damage Table (Dirty Data)].[Part Number],9)=LEFT([Part Table].part_number,9);

[dandoescode]

Hey !!! That seems to work! I had simplified my query when I posted it. Below is what I just generated based on your recommended code structure and it seems to work. But SSANFU who replied after your suggestion may have a valid point. In a case where there multiple new records with same 9 digit number but different dash numbers, I would want the multiple records added to the damage table. He is suggesting adding an ORDER BY clause. What do you think? Will the code below add all multiple records with same 9 digit but different dash number to the damage table?

INSERT INTO [Damage Table] ([Control Number], Unit, [Part Number], [Damage Area], [Flying Hours], [Serial Number], [Tech Data], Deficiency, [Action Required], Comments, Repair, [Final Disposition], [Open Date] )

SELECT DISTINCT [Damage Table (Dirty Data)].[Control Number], [Damage Table (Dirty Data)].Unit, [Damage Table (Dirty Data)].[Part Number], [Damage Table (Dirty Data)].[Damage Area], [Damage Table (Dirty Data)].[Flying Hours], [Damage Table (Dirty Data)].[Serial Number], [Damage Table (Dirty Data)].[Tech Data], [Damage Table (Dirty Data)].Deficiency, [Damage Table (Dirty Data)].[Action Required], [Damage Table (Dirty Data)].Comments, [Damage Table (Dirty Data)].Repair, [Damage Table (Dirty Data)].[Final Disposition], [Damage Table (Dirty Data)].[Open Date]
FROM [Damage Table (Dirty Data)] INNER JOIN [part table] ON LEFT([Damage Table (Dirty Data)].[Part Number],9)=LEFT([Part Table].part_number,9);

I would trust whatever SSANFU says, he's much more expirienced than I am. I think that as long as the dash value is in the same field as the nine digit number it should include both XXXXXXXXX-AAAA and XXXXXXXXX -YYYY. I can't know for certain just by looking at the code but I think it should. Your best bet is just to try it out.

[jk1809]

Thanks dandoescode. I created a test database and the query correctly appended the damage table with multiple dash numbers of the same 9-digit base number.

Now I have one more tweak I need to make to prevent duplicates being added to the damage table (in case I forget and run the query on the same new records again). I need to have the query check the damage table and not add records if they are already present. Any suggestions?

[dandoescode]

Hmmm Yes, something seems apparent to me, I'm sure theres someone here with a more sophisticated way to do it. When you are done with all your queries what you can do is run a SELECT DISTINCT query, very similar to the one you've used, gathering all the data from the table. Then you can make this query a Make Table Query, this will create a table using only the values your interested in.

Making a query a make table query is very easy, Microsoft does a good job describing how here
http://msdn.microsoft.com/en-us/libr...=vs.71%29.aspx

[jk1809]

Thanks!

[jk1809]

How do I mark this thread as solved on the forum query page? Couldn't find a way.

[dandoescode]

Thread tools in the upper right green bar, right under the reply to thread button. Then click mark thread as solved. Happy to have helped.

[ssanfu]

There is a missing closing bracket

.... INNER JOIN [part table] ON LEFT([Damage Table (Dirty Data)].[Part Number],9)=LEFT([Part Table].part_number,9]);

The reason I ask about the sort order is because you said you wanted only one record to be appended to the damage table based on the first 9 characters. So, which record did you want to add?

But the query will add all records that match, not just one.
Lets say you have a table with 5 records:
68A320009-1009
68A320009-1010
77B320006-2010
77B320006-2020
45C320206-5010

Running the sub query with or without the keyword "Distinct" will return all 5 records.

You would need to run a query to return the left 9 characters of the part number, then use that query with the new records table in the main select query to get just one record per part number. That is when the last 4 characters might matter as to which part number record gets selected.

One of the SQL gurus could probably come up with one query to do the job.... I'm not there yet...

[dandoescode]

As far as I understood the problem I think returning all five of those records is exactly what he wants. The only thing he's worried about are exact copies, the distinct keyword takes care of that.

[jk1809]

I probably didn't clearly state my requirement initially. I want to append the damage table with all dash numbers applicable to the 9-digit number. The problem was that all dash numbers are not in the part table, however the basic 9-digit number is there. So your example is exactly what I want to happen.

I decided not to tweak the query any further to prevent duplicates........I'll just run a duplicate query on the damage table to catch any duplicates that might crop up.

This trend analysis is a minor part of my job but it was taking the major part of my time! I think now I can just use the database and not have to tweak any more!
Thanks for your help.