Lookup Validation Table Help!

[JHNielson]

I am not new to Access, but am new to the level of complexity I am now having to get into, so this question may be easy, but I don't even know what to look for to see if it has been posted here before:

I have one table called "Constraints"
and one table called "Input"


Constraints has two columns: Field and Max. Field a list of records with the column names from Input. Max is a constraint value in the second column that the records in Input can't be greater than.

For INstance:

In Input there is a column Hotel_Exp and Airfare. In Constraints there is a Record of Hotel_Exp and Max=1000, Airfare and Max=2000.

I want to run a query to check that Hotel_Exp and Airfare aren't greater than the associated Max values in the Constraints table.


Thanks in advance

[MMcCarthy]

This should give any records where Hotel_Exp is greater than the Max allowed:

SELECT Hotel_Exp FROM Input
WHERE Hotel_Exp > DLookup("Max","Constraints","[Field]='Hotel_Exp');
 

Then just substitute Airfare for Hotel_Exp and run again.

Mary

[JHNielson]

This should give any records where Hotel_Exp is greater than the Max allowed:

SELECT Hotel_Exp FROM Input
WHERE Hotel_Exp > DLookup("Max","Constraints","[Field]='Hotel_Exp');
 

Then just substitute Airfare for Hotel_Exp and run again.

Mary

Thanks - It ran fine, except that it ran multiple times for each record in the "Constraints Table". So it is supposed to add an Error Code to an Error Field when Spk_Fee is larger than the Max_value in the "Constraints Table". When I ran it I got the same error code over and over again.

Here's the actual SQL:

UPDATE [Staging Table], [Constraints Table] SET [Staging Table].Error = [Error] & "1001 ,"
WHERE ((([Staging Table].Spk_Fee)>DLookUp("[Value_Max]","[Constraints Table]","[Field]='Spk_Fee'")));

[MMcCarthy]

Thanks - It ran fine, except that it ran multiple times for each record in the "Constraints Table". So it is supposed to add an Error Code to an Error Field when Spk_Fee is larger than the Max_value in the "Constraints Table". When I ran it I got the same error code over and over again.

Here's the actual SQL:

UPDATE [Staging Table], [Constraints Table] SET [Staging Table].Error = [Error] & "1001 ,"
WHERE ((([Staging Table].Spk_Fee)>DLookUp("[Value_Max]","[Constraints Table]","[Field]='Spk_Fee'")));

You've got a full outer join between [Staging Table] and [Constraints Table]. Remove [Constraints Table] completely from the query as you don't need it.

UPDATE [Staging Table] SET [Staging Table].Error = [Error] & "1001 ,"
WHERE ((([Staging Table].Spk_Fee) > DLookUp("[Value_Max]","[Constraints Table]","[Field]= 'Spk_Fee'")));