Conflicting Query

[amerifax]

I'm trying to select several different values and it's not working.

In one field "TOT_5" the value is greater than five.
In several other fields, all similar, the value has to be greater than two. It must meet all these conditions.

SELECT BUILDER.*
FROM BUILDER
WHERE (((BUILDER.TOT_B5)>5) And ((BUILDER.YR_B)>2)) Or (((BUILDER.YR_C)>2)) Or (((BUILDER.YR_D)>2)) Or (((BUILDER.YR_E)>2)) Or (((BUILDER.YR_F)>2)) Or (((BUILDER.YR_G)>2)) Or (((BUILDER.YR_H)>2)) Or (((BUILDER.YR_I)>2)) Or (((BUILDER.YR_J)>2)) Or (((BUILDER.YR_K)>2)) Or (((BUILDER.YR_L)>2)) Or (((BUILDER.YR_M)>2)) Or (((BUILDER.YR_n)>2)) Or (((BUILDER.YR_o)>2)) Or (((BUILDER.YR_p)>2)) Or (((BUILDER.YR_q)>2)) Or (((BUILDER.YR_r)>2)) Or (((BUILDER.YR_s)>2));

I have also tried "and" with not much luck.

Bob

 

[pbaldy]

I'm not clear on the desired outcome, but almost certainly you have a parentheses problem. Get rid of all the extraneous parentheses that Access adds and reduce it to the appropriate logic:

A And (B Or C)

(A And B) Or C

 

[amerifax]

Okay. Parentheses is something I had not considered. I will get on that right now.

Here is what I'm trying to do:

Actually any of "YR_" greater then 2 with "TOT_B5>5" should be a positive response.

If TOT_B5 = 7 & YR_M = 3 this would be a positive condition. Or should I say part of the resulting query. I hope this clears it up. What looks good in my mind doesn't always convey properly.


Bob

 

[pbaldy]

It sounds like you want my first example, with all of the yr fields inside the parentheses. Dare I ask what those fields are? I'm wondering if it's normalized.

 

[amerifax]

>>pbaldy<<
I do a mass of amount of calculations for homes being built in Wisconsin. With my limited skills in dBase, now Access, I had to come up with a way to retain the yearly totals in the database. Rather than every year change the field name two 2013, 2014 etc we came up with this solution.
YR_A = current year
YR_B = previous year
and so forth.

1. (((BUILDER.YR_D)>2)) An example I was asking the builder database to only consider YR_? That had three or more home starts.
2. WHERE (((BUILDER.TOT_B5)>5) . This field retains total amount of home starts, for the builder, going back to 1989.

1. So if the builder only has a total of four homes since 1989 he is not strong enough for the research project.
2. A second consideration is how many home starts he had in the immediate resent past.
a. I change this requirement at times, based on how many starts each year.

Example:
YR_B (2011) 0
YR_C (2010) 0
YR_D (2009) 1
YR_E (2008) 0

In this case he would be considered too weak for the survey and excluded no matter what his overall total was.

I'm not the best at explaining myself. So usually have my assistant who went to detail. Since you showed an interest I wanted to try and explain oneself. I hope I have.

I might further say we are in the process of converting dBase to Access. We have a very exquisite program that is executed with a hit of a Botton. It runs for about an hour, code written in 1992, resulting in several hundred calculations that are updated every month. Our base starts from the actual Wisconsin Uniform Building Permit applications which we have been contracted process since 1992. What you see in the national media or local papers, when it comes to home starts, is most often off the mark by as much as 30%. Since U.S. Census uses aggregate reports generated and most often supplied as totals only you can develop discrepancies.

Talk about overkill... Oh well, just in case.

Bob

 

[Pat Hartman]

The way your data is organized now it is more like a spreadsheet than a relational database. Since you are undergoing a conversion anyway, perhaps you should consider revamping your schema to be more normalized. It will pay back in dramatically reduced calculation conplexity. You also won't have to keep changing the calculations every year as you add new columns.

Data should be stored with each year in a separate row rather than a separate column. Queries will select the rows you want and you can use simple aggregate functions to summarize the data. So your data will look something like:

BuilderA, 2009, 4
BuilderA, 2011, 2
BuilderB, 2010, 3
BuilderB, 2011, 4
BuilderB, 2012, 3
BuilderC, 2006, 5
BuilderC, 2007, 6

Years when a builder constructed no houses will not exist. Absence of a row means 0. This reduces the number or rows slightly.