471,355 Members | 1,618 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Lookup Validation Table Help!

121 100+
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
Mar 1 '07 #1
3 2232
MMcCarthy
14,534 Expert Mod 8TB
This should give any records where Hotel_Exp is greater than the Max allowed:

Expand|Select|Wrap|Line Numbers
  1. SELECT Hotel_Exp FROM Input
  2. WHERE Hotel_Exp > DLookup("Max","Constraints","[Field]='Hotel_Exp');
  3.  
Then just substitute Airfare for Hotel_Exp and run again.

Mary
Mar 1 '07 #2
JHNielson
121 100+
This should give any records where Hotel_Exp is greater than the Max allowed:

Expand|Select|Wrap|Line Numbers
  1. SELECT Hotel_Exp FROM Input
  2. WHERE Hotel_Exp > DLookup("Max","Constraints","[Field]='Hotel_Exp');
  3.  
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'")));
Mar 1 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Staging Table] SET [Staging Table].Error = [Error] & "1001 ,"
  2. WHERE ((([Staging Table].Spk_Fee) > DLookUp("[Value_Max]","[Constraints Table]","[Field]= 'Spk_Fee'")));
  3.  
Mar 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by John Collyer | last post: by
3 posts views Thread by my-wings | last post: by
reply views Thread by =?Utf-8?B?RU1hbm5pbmc=?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.