Smart_Systems
asked on
Access 2007 null values in query appear as #Deleted#
In an Access 2007 application that links SQL Server 2000 tables through ODBC I have created the following query:
SELECT OrderItems.ItemQuantity, OrderItems.ItemID, ItemComponents.ComponentQt y, ItemComponents.ComponentID
FROM ((Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID) INNER JOIN BatchControl ON (Orders.ShipDate = BatchControl.ProcDate) AND (Orders.DateBatchID = BatchControl.ProcBatchID)) LEFT JOIN ItemComponents ON OrderItems.ItemID = ItemComponents.ItemID;
Some OrderItems do not have components, and one would expect that in rows where that is the case that the values for ComponentID and ComponentQty would be null. However, they show as #Deleted#. The foreign key ItemComponents.ItemID is indexed, but not unique.
As a check I created the following simple query to see just Items and their components, and it displayed null values as expected.
SELECT ItemMaster.ItemID, ItemComponents.ComponentQt y, ItemComponents.ComponentID
FROM ItemMaster LEFT JOIN ItemComponents ON ItemMaster.ItemID = ItemComponents.ItemID;
It should be mentioned that I'm using Windows 7. I don't remember ever having this type of problem with XP or Vista, but I also don't know that I ever attempted to do this exact sort of thing. It seems like I must have at one time or another, but I can't be sure.
Any help would be appreciated.
SELECT OrderItems.ItemQuantity, OrderItems.ItemID, ItemComponents.ComponentQt
FROM ((Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID) INNER JOIN BatchControl ON (Orders.ShipDate = BatchControl.ProcDate) AND (Orders.DateBatchID = BatchControl.ProcBatchID))
Some OrderItems do not have components, and one would expect that in rows where that is the case that the values for ComponentID and ComponentQty would be null. However, they show as #Deleted#. The foreign key ItemComponents.ItemID is indexed, but not unique.
As a check I created the following simple query to see just Items and their components, and it displayed null values as expected.
SELECT ItemMaster.ItemID, ItemComponents.ComponentQt
FROM ItemMaster LEFT JOIN ItemComponents ON ItemMaster.ItemID = ItemComponents.ItemID;
It should be mentioned that I'm using Windows 7. I don't remember ever having this type of problem with XP or Vista, but I also don't know that I ever attempted to do this exact sort of thing. It seems like I must have at one time or another, but I can't be sure.
Any help would be appreciated.
ASKER
derekkromm,
Thank you for your answer, but none of the above apply.
It is a select query (i.e. a read operation).
I'm using Access 2007.
There are no bigint or float fields.
Alll tables involved have primary keys.
Thank you for your answer, but none of the above apply.
It is a select query (i.e. a read operation).
I'm using Access 2007.
There are no bigint or float fields.
Alll tables involved have primary keys.
The #Deleted# (most of the time) means that the underlying dataset need to be refreshed.
First simply try refreshing the dataset.
Press Shift+F9
See this MS link as well:
http://support.microsoft.com/kb/128809
First simply try refreshing the dataset.
Press Shift+F9
See this MS link as well:
http://support.microsoft.com/kb/128809
ASKER
boag2000,
Thanks for the response, but that's not it either. The data involved is all static. The issue appears to be with the combination of joins, but I know I've done such things before without this problem. The only thing that is different now is Windows 7, but I can't imagine what that has to do with it.
Thanks for the response, but that's not it either. The data involved is all static. The issue appears to be with the combination of joins, but I know I've done such things before without this problem. The only thing that is different now is Windows 7, but I can't imagine what that has to do with it.
Is this the case for all computers on Win7?
Is the Database "Split"?
All the Updates installed for Windows and Office?
Is the Database "Split"?
All the Updates installed for Windows and Office?
ASKER
The only computer running on Win 7 is mine. I did get a chance to run the query on a couple of XP computers and got the same result (i.e. #Deleted# where nulls should appear).
The application is Access 2007 linking SQL Server 2000 tables using ODBC, so yes, it is split.
Windows and Office are current on updates.
Thanks again for your ideas.
The application is Access 2007 linking SQL Server 2000 tables using ODBC, so yes, it is split.
Windows and Office are current on updates.
Thanks again for your ideas.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry about the absence. I was dealing with an urgent situation.
boag2000,
I have pretty much come to the same conclusion - move the data to a working table in Access, or I can change the structure (require that there be at least one component per item) so the need for the left join is eliminated. As I mentioned in my initial question, that does work. I will be at the db's site tomorrow and will test both ideas. I'll report back.
boag2000,
I have pretty much come to the same conclusion - move the data to a working table in Access, or I can change the structure (require that there be at least one component per item) so the need for the left join is eliminated. As I mentioned in my initial question, that does work. I will be at the db's site tomorrow and will test both ideas. I'll report back.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Click the "request Attention" link and explain what you would like to do.
;-)
Jeff
;-)
Jeff
OK, glad I could be of some assistance...
Congratulations on your solution as well...
;-)
Jeff
Congratulations on your solution as well...
;-)
Jeff
ASKER
I accepted my comment as part of the solution because it is the method I actually used. The Expert (Boag2000) proposed a method that would work, but I didn't use that method.
I gave the solution(s) a B grade because it was more of a workaround than a direct solution for the original issue. If there was a direct solution, I feel confident that one of the Experts would have known about it.
I gave the solution(s) a B grade because it was more of a workaround than a direct solution for the original issue. If there was a direct solution, I feel confident that one of the Experts would have known about it.