DLookup problem

[stu999]

Working SQL with the DLookup

SELECT QueryNPSumField.Field, QueryNPSumField.SoilType, QueryNPSumField.TableCropN, QueryNPSumField.TableSNSRain, DLookUp([SoilType],'SNSRainfall',[Crop]=[TableSNSRain]) AS SNSNumber, [TableCropN] & [SoilType] AS Ntype
FROM QueryNPSumField INNER JOIN SNSRainfall ON QueryNPSumField.TableSNSRain = SNSRainfall.Crop;

This Dlookup is returning 4 which is correct as it is looking at 'Cereals Moderate' in 'Organic' see table below

SQL with DLookup set out the same as above but returns the incorrect value

SELECT QuerySNSField.Field, QuerySNSField.SNS, QuerySNSField.Ntype, DLookUp([SNSNumber],'NTable',[TheSoil]=[Ntype]) AS Expr1
FROM QuerySNSField INNER JOIN NTable ON QuerySNSField.Ntype = NTable.TheSoil;

The Dlookup should be return 30 from the NTable which is shown below, but always returns 4, which is the SNSNumber The Dlookup SNSNumber value = 4 and TheSoil value = Winter OatsNOrganic which are displayed correctly in the query result, so I don't know why it is still returning 4 when it should be returning 30. See table below

Any help would be appreciated as I am now confused as to why the Dlookup is not working!

See my Mr Excel post for images as i need to have 10 posts to include images and links! mrexcel.com/forum/showthread.php?t=516713

 

[Galaxiom]

Avoid using Domain functions in a query.

Reconstruct it using joins.

 

[stu999]

Hi
Are you saying not to use the Dlookup in the Query?
The query is only 2 tables with the relevant join which should make the Dlookup work.

Thanks

 

[Galaxiom]

Maybe I am wrong (it is late here) but I don't think you even need the DLookup because it is already done by the join.

(I have also aliased one of the table to make it easier to read.)

SELECT A.Field, A.SNS, A.Ntype, NTable.[SNSNumber]
FROM QuerySNSField As A
INNER JOIN NTable ON A.Ntype = NTable.TheSoil;

BTW. A DLookUp is essentially a query in itself. Using Domain functions in a query invites very slow processing.

 

[stu999]

Hi
That would work apart from NTable does not have the fieldname SNSNumber.
It is set out with fields 'TheSoil' '0' '1' '2' '3' '4' '5' '6' which is why I was using the Dlookup.
In theory the NTable.[SNSNumber] needs to be a variable.

 

[jdraw]

Perhaps you could show us the fields in your Ntable.

 

[stu999]

It won't let me post the images because I have less than 10 posts.

 

[Galaxiom]

Your Dlookup is not valid syntax. All arguments must be text strings. Too tired to notice last night.

The real problem is your data is not properly normalized. Those numbered fields should be expressed as a value in another field of the record, like this:

TheSoil | TheNumber | TheValue

Then you can use a join in the query.

 

[stu999]

Hi

Thanks for suggestions, I have sorted out the problem by rejigging the NTable and then using joins to get out the required value, as per your suggestion, as the Dlookup was the problem.

I am also now going to look at the other tables for rejigging too.

Thanks

 

[stu999]

Here is SQL of working query.

SELECT QuerySNSField.Field, QuerySNSField.Ntype, NTable.SNSValue
FROM QuerySNSField INNER JOIN NTable ON (QuerySNSField.Ntype = NTable.TheSoil) AND (QuerySNSField.SNSNumber = NTable.SNSField);