PROJECT ROV: If statements across tables

[jrubenol]

Hi All,

I'm pretty new to access and have joined on a project to make a pretty large database. My first question is how do I make an Iif statement across multiple tables? I have the following:

Table 1
PID
NUM

Table 2
NUM-TESTVALUE

Table 3 (needs to be)
If NUM = NUM-TESTVALUE = "Pass", Else "Fail"

Thanks in advance

[khalid]

Create a query based on your table2. create a calculated field Result: in the query grid

Result: IIF(Num=([Num]-[TestValue), "Pass", "Fail")

[jrubenol]

Thanks for the advice, going to try it out shortly. Just wanted to clarify that table 2 doesn't have a minus sign, it's just one value that I've named NUM-TESTVALUE where - is just a dash. I'll try out your suggestion and thanks again

[boblarson]

My suggestion is do NOT use the dash in object or field names. It only leads to pain and suffering (along with using any other special characters) and I would avoid using spaces in them as well.

[jrubenol]

Thanks for the advice bob, I'll switch it right away. This is going to be a long term process with a ton of questions so I'll be listing each one and the first part will be PROJECT ROV. Thanks to both of you and we'll see how this first step goes, I suspect there will be at least 25-50 tables by the end of this

[boblarson]

I suspect there will be at least 25-50 tables by the end of this

Then it would be good, if you haven't already done so, is to let us see the table structure as you have designed it, so that any normalization issues can be sorted out before you get too far along.

[jrubenol]

I'll see what I can get up. One of my main issues right now if the limitation of columns (number of columns). We're having to split up things that before were together because there aren't enough columns. Where should I post the structure??

[boblarson]

I would upload a copy of the database here with just the tables / relationships and maybe some sample (bogus) data in the tables so we can see how it all kind of relates.

[jrubenol]

One issue that I'm having is that the second table only has one row of numbers (these are all just numbers provided by the State of California for ADA accessibility). So I have something like the following:


Table 1
Site_Name Length Height
Record 1
Record 2
Record 3
Record 4
etc...


Table 2
Height Length
State Required Height (#) State Required Length (#)


So I want to compare Record 1,2,3,4 etc... length/height to the single corresponding numbers provided in Table 2. Not sure how to query these when they don't have unique keys that correspond. Thanks again

[boblarson]

I can answer you better with a sample of your database.

[jrubenol]

Unfortunately I can't really get the design down until I know if this is possible (essentially I don't want to spend the time until I know for certain the following is possible). I'll try to be a bit more clear about what I need to do:

Table 1 -- Poll Site Accessibility

Column 1 -- Poll Site ID (Key)
Column 2 -- Height of Door
Column 3 -- Width of Door
Column 4 -- Pass/Fail

Table 2 -- VAAC Standards
Column 1 -- Height of Door Requirement (a single record that has a number that reflects the minimum door height requirement)
Column 2 -- Width of Door Requirement (again, single record)


So I want to compare Door Height Record 1-10,000 from table 1 (column 2) all to the same value given in Table 2 under Column 1 (which again is just a single record)



It'd be equivalent to having this in excel:

= IF (A1 = $B$1,"True","False") -- this is what is in cell A1

= IF (A2 = $B$1,"True","False") -- this is in A2

Etc....all the way down to A10000


Thanks again in advance