Query doing nothing

[Guest]

I am using an update query to clean up really messed up data I receive. I
receive large chunks of sloppy, unformatted, date, where years of dates are
not always correct, among other problems. In table TnewData I start all data
in text fields, along with a duplicate set of empty fields with proper
datatypes in the same table.

Then, using subqueries to fix certain data problems, like to calculate the
proper year a date should have, I update the results to the corresponding
empty field of the proper field type . But, I am having trouble with the
Customer Name expression. In column CustomerRaw, a single Customer name can
be entered several different ways.

For instance, "Chachi's Hog Hut" is sometimes entered as
"CHH" or
"Chachis Hog Hut" or
"Chachi's" or
"Chachis" or
"Chachis HH"

So, I created a separate table, TCustomersLookup, where there would be 5
records for this customer:

GoodName, BadName
Chachi's Hog Hut, CHH
Chachi's Hog Hut, Chachis Hog Hut
Chachi's Hog Hut, Chachi's
Chachi's Hog Hut, Chachis
Chachi's Hog Hut, Chachis HH

I've identified all the customer names that are problematic and entered them
in this table.

So, I left join TnewData with TcustomersLookup and use an expression in the
update field. However, Customer always comes out with the same name as
CustomerRaw. So far, I have tried:

DLookUp("[goodName]","TcustomersLookup","badName=customerRaw") WHERE
[customerRaw] = [Tcustomers.Lookup].[badName]

IIf([customerRaw]=[TcustomersLookup].[badName],[TcustomersLookup].[goodName],[customerRaw])


Sometimes the VERY FIRST bad name in my table is converted to the good name
properly, and all the rest of the names are not converted, but most of the
time nothing is converted propertly.

What am I doing wrong?

Thanx
Jeny

 

[Allen Browne]

Hi Jeny

Importing poor data into a well normalized structure can sure be
frustrating!

Try concatenating the bad name into the 3rd argument of your DLookup(). This
kind of thing:
DLookUp("[goodName]","TcustomersLookup",
"badName = """ & customerRaw & """")

The general approach I usually take is to use a temporary table that uses
all text fields, and identify the problems before appending the data to the
real tables. It takes a bit of code to identify all the problems (e.g. names
that don't match either the main Customer table nor the alternative names,
bad dates, required fields with no value, bad lookup values, ...), and show
these records in a form with a description of the problem so the user can
sort out the issues. Once they have all issues sorted out, the command
button for Import is enabled, and the append process for the real tables
takes place - typically inside a transaction in case I missed something that
won't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using an update query to clean up really messed up data I receive. I
receive large chunks of sloppy, unformatted, date, where years of dates
are
not always correct, among other problems. In table TnewData I start all
data
in text fields, along with a duplicate set of empty fields with proper
datatypes in the same table.

Then, using subqueries to fix certain data problems, like to calculate the
proper year a date should have, I update the results to the corresponding
empty field of the proper field type . But, I am having trouble with the
Customer Name expression. In column CustomerRaw, a single Customer name
can
be entered several different ways.

For instance, "Chachi's Hog Hut" is sometimes entered as
"CHH" or
"Chachis Hog Hut" or
"Chachi's" or
"Chachis" or
"Chachis HH"

So, I created a separate table, TCustomersLookup, where there would be 5
records for this customer:

GoodName, BadName
Chachi's Hog Hut, CHH
Chachi's Hog Hut, Chachis Hog Hut
Chachi's Hog Hut, Chachi's
Chachi's Hog Hut, Chachis
Chachi's Hog Hut, Chachis HH

I've identified all the customer names that are problematic and entered
them
in this table.

So, I left join TnewData with TcustomersLookup and use an expression in
the
update field. However, Customer always comes out with the same name as
CustomerRaw. So far, I have tried:

DLookUp("[goodName]","TcustomersLookup","badName=customerRaw") WHERE
[customerRaw] = [Tcustomers.Lookup].[badName]

IIf([customerRaw]=[TcustomersLookup].[badName],[TcustomersLookup].[goodName],[customerRaw])


Sometimes the VERY FIRST bad name in my table is converted to the good
name
properly, and all the rest of the names are not converted, but most of the
time nothing is converted propertly.

What am I doing wrong?

Thanx
Jeny

 

[Guest]

Hi, thanks so much for answering.

I'm not quite sure what you are suggesting regarding th DLookup. You said
to use something "like" the statement you gave me.
Do you mean I should tailor the statement with actual bad names in it? If
this is so, I have too many bad names to fit into the update statement.

DLookUp("[goodName]","TcustomersLookup","badName = """ & customerRaw & """")

I tried pasting your statement into my update query field, but it did not do
anything. do you think you could possibly explain a little bit more?

Thank you!

 

[Guest]

Also, considering the fact that my lookup table only includes exceptions,
does there need to be a condition put into the statement if the customer is
not in that table?

 

[Allen Browne]

Please bear in mind that I cannot see your query, and don't even know your
table and field names, so all I can give is an example.

The suggestion was to concatenate the name of the field (customerRaw?) that
could contain the bad data into the 3rd argument for DLookup(). If you need
further help with how to form the 3rd argument for DLookup(), see:
http://allenbrowne.com/casu-07.html

You will be trying to match the name directly first, and if that doesn't
work then match the lookup of alternative names. This will probably involve
an outer join between the import table and your actual customer table (all
records from the import, and any matches from customers.) If there is a
match, you can just use the matching name. If there is no match, you can use
the DLookup() or possibly a subquery. You may be able to combine these
expressions using Nz().

If outer joins are new, see:
http://allenbrowne.com/casu-02.html

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Hope that helps you get on the road.

 

[John Spencer]

A standalone query to update just the customer name would look like the following.
Assumption: Your field name is CustomerRaw in your table


UPDATE tNewData LEFT JOIN tCustomersLookup
ON tNewData.CustomerRaw = tCustomersLookup.BadName
SET tNewData.FixedName = Nz([tCustomersLookup].[GoodName],[tNewData].[CustomerRaw])

If you use the DLookup Method and don't have the CustomerRaw in tCustomersLookup
then DLookup will return a null. Use the NZ function to return CustomerRaw in
those cases.

NZ(DLookUp("[goodName]","TcustomersLookup","badName = """ &
[tNewData].[CustomerRaw] & """"), CustomerRaw)

 

[Guest]

Thank you for your reply!

I have just been thrown into using access at work, and have just been having
problem after problem to research and solve. I have been working on this one
for days now. I am truly just a hack!

I had such a time with the DLookup function not doing anything, especially
with the quotation issues, as my fields are text, AND may contain an
apostrophe. I was truly confused!




JOHN, your answers did work, but I actually found that, with a left join, i
can simply use an iif statement in my already established update query!

tNewData.customerRaw =
UCase(Trim(IIf([customerRaw]=[badname],[goodname],[customerRaw])))

So, now I have 3 statements that work. If you can think of any reasons one
statement would be better than another, please tell me. Thanks!

Thanks, both of you, from the bottom of my heart!