By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Lookup Validation Table Help!

100+
P: 121
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
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

100+
P: 121
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
Expert Mod 10K+
P: 14,534
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.