Query returns WRONG FIELD!!!

[DataMiner]

This is at least the 3rd time I've come across something that is, to me, an extremely serious bug in Access. Anyone else seen this, and anything I can do to avoid it??
In a query, I ask for the values from Field A.
The query returns the values from Field B, but still calls it Field A.

This is in a fairly complex query. If I delete one particular field from the query, the bug disappears. If I put that field back, the bug returns.

Here is my current query:

SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.FirstOfFeederSize, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST, [loadssavedperday]*[lostpersetup]*[std_cost]*250 AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));

HEre's the pertinent part of the record from BYPN_1:

PartNumber LostPerSetup FirstOfFeederSize
107573 3 12X4

But here's what the query returns:

PartNumber LostPerSetup FirstOfFeederSize
107573 12X4 12X4

Now, if I just delete the "parts_annual" field from the query:

SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.FirstOfFeederSize, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));

Now the query returns what I expect:

PartNumber LostPerSetup FirstOfFeederSize
107573 3 12X4

 

[Pat Hartman]

Do you have a table level lookup field defined?

 

[DataMiner]

Well, yes, I have several. ByPN_1 is, itself, a query based on 5 tables. 3 of them have one field each with a combo-box lookup defined. So what is your question driving at? Are lookup fields a known problem?

 

[boblarson]

Are lookup fields a known problem?

Yes, they are - see here:
http://www.mvps.org/access/lookupfields.htm

 

[RuralGuy]

...Are lookup fields a known problem?

All they basically do is confuse the programmer!

 

[DataMiner]

OK, I have removed all traces of any lookup fields from the tables involved with this query. No change. Furthermore, I can change what shows up as the erroneous data in the LostPerSetup field by changing the order of the fields in ByPN_1. Seems that whatever I put as the 3rd field in ByPN_1 always gets returned in place of the LostPerSetup field when I run my query.

I have tried repair/compact. Have also created a new db and imported everything into it. No help. Any other ideas?

 

[neileg]

Could this be an Access 2003 service pack 3 issue?

 

[DataMiner]

No, I am using Access 2002. I have to say, this really scares me. If the "wrong" field that Access happens to pick, happens to be an incompatible data type with the correct field (text vs. number, for example) then I'll notice the error. But if the data types are compatible, I may not notice it at all. This is a MAJOR problem, major enough for me to question any future use of Access if I can't resolve.

Right now I am in the process of having to correct an application Access told me my annual cost was $4,692. Actually it's $245,038! The mistake is that instead of using the "parts per setup" field that I requested, Access used a different field that is almost always 0 or 1.

 

[RuralGuy]

Have you tried adding parens around the calculated fields to see if it makes any difference?

 

[DataMiner]

Tried it, no change. BTW, I have simplified my offending query down to:

SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, (IIf([nonperm]*[setupsperday]<IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups],[nonperm]*[setupsperday],IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[lostpersetup]*[std_cost]*250) AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM;

"lostpersetup" should show an integer value but instead is returning the results from a nearby text field in BYPN_1. I can control what "lostpersetup" returns by changing the order of the fields in ByPn_1.

 

[DataMiner]

I have now split my original query, which was very complex, into 8 separate, sequential queries -- by sequential I mean that query #8 runs from the results of #7, #7 from #6, etc.

In my original query I had many calculated fields where calculated field B depended on the results of calculated field A. I decided to err on the side of caution and totally get rid of this design; hence the need for 8 sequential queries.

At any rate, it *seems* to be working now. I say *seems* because... how would I know for sure???? When will it decide to use the wrong field again???? This really has me worried.... This behavior to me is equivalent to Access deciding that, once in a while and at random, 2+2=73.

 

[DataMiner]

It's been suggested that I report this problem to microsoft. How do I do that?

 

[RuralGuy]

I've sent a ping to Microsoft with this thread as a link. I'll check back to see what happens.

 

[boblarson]

I would say that it is highly likely that there is something else at work here and you, and we, aren't catching it. If you can post your database then we could possibly see what is up. If it were reproduceable by us then we can approach MS with this.

 

[DataMiner]

Here's the database. I've named the offending query "BAD". I really appreciate any help you can offer!

 

[boblarson]

I thought you said you removed all lookups. I found two more:

1 in Perm Setups table and 1 in Customized Feeder List.

Plus your IIf statement for Parts Annual is missing arguments. You are missing the FALSE part for the beginning IIf.

Also, the database is not normalized AND you are using text for primary keys where text is not the appropriate place to use them. You should not need to use so many composite keys.

So, before you go saying that Microsoft Access has a bug, I think you need to read up on normalization, and get a handle on the primary key situation because I think you are your own worst enemy at the moment with the data.

Here's a good link on normalization:

http://support.microsoft.com/kb/283878

 

[DataMiner]

Thanks for taking a look.

In response to your points:

I have attached a new version the database which addresses most of your points, but still has the problem query.
1. I had removed the lookups but it made no difference so I re-added them, as I find them very useful. However, I have re-removed them in the new attachment.
2. I don't see that I'm missing the false part if the Iff statement. It's a complex statement, but if you look at it carefully you'll see the true part is:

[nonperm]*[setupsperday]

and the false part is:

IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[lostpersetup]*[std_cost]*250)

3. The file I have posted is a tiny subset of the large database that I'm working with. In constructing this subset I had inadvertantly duplicated some records in some of the tables. I've gotten rid of the duplicates so that each table now has a primary key.
4. I understand that there is disagreement among database folks as to whether composite "natural" keys are the way to go, or whether to always use numerical "ID" type keys. Personally I prefer natural keys. At any rate, it's hard for me to take that as a justification for Access to return the wrong field. If the rule in Access is "always use single field numerical keys, or you're liable to get the wrong field returned", then I guess I, and the rest of the world, need to know that.

I appreciate any further help you can offer.

 

[DALeffler]

I don't know know if this will help or not, but if I remove the [lostpersetup] reference from the false part of the Iif construction, the BAD query in the latest zip file works fine. Change

IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[lostpersetup]*[std_cost]*250)

to

IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[std_cost]*250)

Its kinna weird tho - I get errors in the BAD query result running the ZIP file as is, but removing ByPN_1.LostPerSetup from from the Select statement solves that.

SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, (IIf([nonperm]*[setupsperday]<IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups],[nonperm]*[setupsperday],IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[lostpersetup]*[std_cost]*250) AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.FirstOfType=FeederCost1.Type) AND (ByPN_1.FirstOfFeederSize=FeederCost1.Size) AND (ByPN_1.ActualMachine=FeederCost1.Machine)) LEFT JOIN master_attr ON ByPN_1.PartNumber=master_attr.ITEM;

Gives errors but

SELECT ByPN_1.PartNumber, (IIf([nonperm]*[setupsperday]<IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups],[nonperm]*[setupsperday],IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups]))*[setupsperday]/[concurrentsetups])*[lostpersetup]*[std_cost]*250) AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.FirstOfType=FeederCost1.Type) AND (ByPN_1.FirstOfFeederSize=FeederCost1.Size) AND (ByPN_1.ActualMachine=FeederCost1.Machine)) LEFT JOIN master_attr ON ByPN_1.PartNumber=master_attr.ITEM;

doesn't generate errors.


Seems you can have lostpersetup in the query results or in the caluculated field of the query but not both.

hth,

 

[Pat Hartman]

I wonder if the error is caused by some corruption of the database. Perhaps there is some residual memory of the lookups. I would rebuild the database from scratch. Export the tables to .csv files. Open a new empty database and import all the objects except the tables. Import the tables from the .csv files. Reapply the primary keys and relationships.

 

[DataMiner]

Pat, I tried totally rebuilding the db using the method you suggested. No change.

Doug, thanks for your observation. I already know that I can make this problem go away, in this instance, by breaking the query down into multiple queries. What I need to figure out is exactly WHY this happened so I can keep it from happening again in the future, totally blindsiding me with spurious results... eg the $4,692 vs. $245,038 I mentioned several posts ago.