Cleaning Up a Huge Table - Need to populate blanks..

[TJones76]

Hi All..
I have a huge table that came from several tables with some of the same information. Now I need to clean it all up so it's nice and pretty and remove all duplicates. To do that I first have to populate the "Serial Number" column completely... some of the tables were missing the # but other fields will help populate that number.

Let's say this is my table:

Serial# Test# Test Date

(BLANK) 12345 4/13/2005
(BLANK) 12345 1/18/2005
K777475 12345 4/13/2005

How do I populate the BLANK field with the "Serial#" that has the same "Test#" and "Test Date"?

Thank you for your help.. I have a lot of cleaning up to do :\

-Tiffany

-TJones

 

[Remou]

Hi
Would two queries work for you? Query one to select all records with a Serial#. Query two relating MyTable to query one on test and test date? Or am I missing something?

 

[TJones76]

Remou,
Forgive me, I'm sort of a visual learner, can you show me what you mean or explain it more thoroughly?
How exactly do I populate the null fields in my table with the two queries?

Thanks,
-T

-TJones

 

[TomHW]

If the name of your table is MyTable then you could use a query such as:

UPDATE MyTable INNER JOIN MyTable AS Temp ON (MyTable.[Test#]=Temp.[Test#]) AND (MyTable.[Test Date]=Temp.[Test Date])
SET MyTable.[Serial#] = Temp.[Serial#]
WHERE IsNull(MyTable.[Serial#] AND (NOT IsNull(Temp.[Serial#]));

What this does is join the table MyTable with itself where the Test#'s are equal and the Test Dates are equal. Because of the WHERE clause, the only rows in the query have a blank serial number for MyTable and an actual serial number for Temp. It then just sets the blank equal to the serial number for Temp.

If this were to run on your example rows, your first row would get the serial number value: K777475 but the second row would remain blank because there is no row with the same test# and test date that has a serial number. So it will only put a serial number in a blank if there is a serial number in another row that has the exact same test# and test date.

I think this is somewhat similar to what Remou was talking about.

Hope this helps,
Tom

 

[TomHW]

Sorry, there should be a closing parenthesis for the first IsNull() in the WHERE clause:

Change:

WHERE IsNull(MyTable.[Serial#] AND (NOT IsNull(Temp.[Serial#]));

To:

WHERE IsNull(MyTable.[Serial#]) AND (NOT IsNull(Temp.[Serial#]));

 

[TJones76]

Perfect, that is exactly what I'm wanting to do. I'll give this a try.

Thank you Both!

-Tiffany

-TJones

 

[TJones76]

Hi TomHW,
I'm getting a syntax error on UPDATE with this code. I put it in exactly as you have but replaced "MyTable" with the actual table name.

-T


-TJones

 

[lespaul]

what's the actual SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[TomHW]

First of all, what is the error? Next, check the following:

- Make sure that the values for the field names are exactly those of your field names. I used [Serial#], [Test#] and [Test Date] because that is what you had listed above your example table.

- If your table name has any spaces in it then you have to put [] around it whenever it is used. For instance, if your actual tabel name is "My Table" then you need to replace things such as "MyTable.[Serial#]" with "[My Table].[Serial#]"

- Make sure that [Test Date] is actually supposed to have a space.

- Make sure that you added the parenthesis that I forgot in the WHERE clause.

I created a table and fields with exactly the names that you gave me and it runs fine on my end, so just check over some of these issues and let me know. If this doesn't help, let me know the error and the Exact names of your tbale and fields.

Hope this helps,
Tom

 

[TJones76]

Ok, the spaces in my table name caused the errors, so with all that fixed, the query runs but it asks for a parameter value of the test date, is this the best way? Or is there a way to do the updating automatically without having to enter in the test date and test number for each (there are about 5000 blanks)

Here's my code:
UPDATE [PCB DATA TEST] INNER JOIN [PCB DATA TEST] AS Temp ON ([PCB DATA TEST].[Test date]=Temp.[Test date]) AND ([PCB DATA TEST].[Test number]=Temp.[Test Test number]) SET [PCB DATA TEST].[Serial number] = Temp.[Serial number]
WHERE IsNull([PCB DATA TEST].[Serial number]) AND (NOT IsNull(Temp.[Serial number]));

-T

-TJones

 

[TomHW]

The query shouldn't ask you to enter a parameter for any value. Seeing as how it does, this means that something in your query is mistyped.

Here is at least one problem in your query:

UPDATE [PCB DATA TEST] INNER JOIN [PCB DATA TEST] AS Temp ON ([PCB DATA TEST].[Test date]=Temp.[Test date]) AND ([PCB DATA TEST].[Test number]=Temp.[red][Test Test number][/red]) SET [PCB DATA TEST].[Serial number] = Temp.[Serial number]
WHERE IsNull([PCB DATA TEST].[Serial number]) AND (NOT IsNull(Temp.[Serial number]));

Since it is asking you for the test date, it means that you probably have [Test date] typed incorrectly. Check to make sure that the field name is actually [Test date] and that maybe the space shouldn't be there or something along those lines. When everything is correct you won't have to enter any values, everything will be done automatically.

Hope this helps,
Tom