If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Check if a value is legal
I have some fields I need to check against another table.
The scenario: My users have been inputting data in my tables through queries up until now. i've realized I'm going to need a few lookuptables to verify their input, but there's some 10.000+ records that needs to be checked. So I've made the lookup tables but I thought I'd make it a little easier for my users to verify their data with a query that returns records with faults. I've made a query that returns al the NULL values and a continuous form that mark the empty but required fields with yellow background through autoformat. I'd love to have the invalid entries marked with red text. I think i can handle that if i get a query that returns the invalid records. |
#2
|
|||
|
|||
Check if a value is legal
On Tue, 4 May 2010 01:33:12 -0700 (PDT), atledreier
wrote: The basic idea for such query is: select * from myTable where myField not in (select myField from myLookupTable) -Tom. Microsoft Access MVP I have some fields I need to check against another table. The scenario: My users have been inputting data in my tables through queries up until now. i've realized I'm going to need a few lookuptables to verify their input, but there's some 10.000+ records that needs to be checked. So I've made the lookup tables but I thought I'd make it a little easier for my users to verify their data with a query that returns records with faults. I've made a query that returns al the NULL values and a continuous form that mark the empty but required fields with yellow background through autoformat. I'd love to have the invalid entries marked with red text. I think i can handle that if i get a query that returns the invalid records. |
#3
|
|||
|
|||
Check if a value is legal
atledreier wrote:
I have some fields I need to check against another table. The scenario: My users have been inputting data in my tables through queries up until now. i've realized I'm going to need a few lookuptables to verify their input, but there's some 10.000+ records that needs to be checked. So I've made the lookup tables but I thought I'd make it a little easier for my users to verify their data with a query that returns records with faults. I've made a query that returns al the NULL values and a continuous form that mark the empty but required fields with yellow background through autoformat. I'd love to have the invalid entries marked with red text. I think i can handle that if i get a query that returns the invalid records. Did you not see my answer to this yesterday? -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Check if a value is legal
This will get you the invalid records. You'll need extra to figure out which
fields to turn red. Table1 table you are looking for invalid records Field1 Field2 Field3 lookup1 table with valid values for Table1.Field1 lookup2 table with valid values for Table1.Field2 lookup3 table with valid values for Table1.Field3 SELECT Table1.Field1, Table1.Field2, Table1.Field3 FROM ((Table1 LEFT JOIN lookup1 ON Table1.Field1 = lookup1.Field1) LEFT JOIN lookup2 ON Table1.Field2 = lookup2.Field1) LEFT JOIN lookup3 ON Table1.Field3 = lookup3.Field1 WHERE (([lookup1]![Field1] Is Null) OR ([lookup2]![Field1] Is Null) OR ([lookup3]![Field1] Is Null)); "atledreier" wrote: I have some fields I need to check against another table. The scenario: My users have been inputting data in my tables through queries up until now. i've realized I'm going to need a few lookuptables to verify their input, but there's some 10.000+ records that needs to be checked. So I've made the lookup tables but I thought I'd make it a little easier for my users to verify their data with a query that returns records with faults. I've made a query that returns al the NULL values and a continuous form that mark the empty but required fields with yellow background through autoformat. I'd love to have the invalid entries marked with red text. I think i can handle that if i get a query that returns the invalid records. . |
Thread Tools | |
Display Modes | |
|
|