Update a table inside the dataview of a query

[John T Ingato]

Geeze, I wonder if that makes any sense.

I have a table with a list of 1800 stores and store information. Some of
the records have missing information. So I left joined the table on itself
with the criteria or'ed for all fields I would like to test. SQL is below.
I would like to be able to use the query result to type in the updated
information. Is this possible? Or do I need to do something else?

SELECT tblHDStoreList.StoreNumber, tblHDStoreList_1.StoreName,
tblHDStoreList_1.MKT, tblHDStoreList_1.CloseDate, tblHDStoreList_1.Address1,
tblHDStoreList_1.City, tblHDStoreList_1.State, tblHDStoreList_1.Zip,
tblHDStoreList_1.Phone
FROM tblHDStoreList LEFT JOIN tblHDStoreList AS tblHDStoreList_1 ON
tblHDStoreList.StoreNumber = tblHDStoreList_1.StoreNumber
WHERE (((tblHDStoreList_1.StoreName) Is Null)) OR (((tblHDStoreList_1.MKT)
Is Null)) OR (((tblHDStoreList_1.CloseDate) Is Null)) OR
(((tblHDStoreList_1.Address1) Is Null)) OR (((tblHDStoreList_1.City) Is
Null)) OR (((tblHDStoreList_1.State) Is Null)) OR (((tblHDStoreList_1.Zip)
Is Null)) OR (((tblHDStoreList_1.Phone) Is Null))
ORDER BY tblHDStoreList.StoreNumber;

 

[Marshall Barton]

Geeze, I wonder if that makes any sense.

I have a table with a list of 1800 stores and store information. Some of
the records have missing information. So I left joined the table on itself
with the criteria or'ed for all fields I would like to test. SQL is below.
I would like to be able to use the query result to type in the updated
information. Is this possible? Or do I need to do something else?

SELECT tblHDStoreList.StoreNumber, tblHDStoreList_1.StoreName,
tblHDStoreList_1.MKT, tblHDStoreList_1.CloseDate, tblHDStoreList_1.Address1,
tblHDStoreList_1.City, tblHDStoreList_1.State, tblHDStoreList_1.Zip,
tblHDStoreList_1.Phone
FROM tblHDStoreList LEFT JOIN tblHDStoreList AS tblHDStoreList_1 ON
tblHDStoreList.StoreNumber = tblHDStoreList_1.StoreNumber
WHERE (((tblHDStoreList_1.StoreName) Is Null)) OR (((tblHDStoreList_1.MKT)
Is Null)) OR (((tblHDStoreList_1.CloseDate) Is Null)) OR
(((tblHDStoreList_1.Address1) Is Null)) OR (((tblHDStoreList_1.City) Is
Null)) OR (((tblHDStoreList_1.State) Is Null)) OR (((tblHDStoreList_1.Zip)
Is Null)) OR (((tblHDStoreList_1.Phone) Is Null))
ORDER BY tblHDStoreList.StoreNumber;

I can't see where the Left Join make any sense. Unless I'm
missing something tricky, just check the fields in the
table.

SELECT T.StoreNumber, T.StoreName, T.MKT, T.CloseDate,
T.Address1, T.City, T.State,T.Zip, T.Phone
FROM tblHDStoreList As T
WHERE T.StoreName Is Null OR T.MKT Is Null
OR T.CloseDate Is Null OR T.Address1 Is Null
OR T.City Is Null OR .State Is Null OR T.Zip Is Null
OR T.Phone Is Null
ORDER BY tblHDStoreList.StoreNumber

 

[John T Ingato]

You are absolutely right. I don't know what I was thinking. No join
necessary. And the fields are now updatable.