Crosstab Conditional Query

[Wiz0fBaud]

Hey guys, I have a database that we are using to keep track of testing on a house and its electrical wiring.

Basically I have the following Fields

Location - Location or Zone.
UNSAT - Unsat condition (no power)
SAT - Sat condition
Tester - Person who retested
Date - Date

and then I have a set of data in case it tested unsat, for it to be tested again...

RT-UNSAT - ReTest UNSAT
RT-SAT - ReTest SAT
RT-Tester - Retester
RT-Date - Date Retested

So what I did was build a crosstab query. The Zone is the Column Heading, the Row Headings are the SAT and UNSAT and then there is a value, that counts the number of UNSAT/SAT signals in the zone.

My problem is, I want to be able to report on the retest.
For example, if I have a location that was tested unsat, and then retested sat. How do I tell the crosstab query that, hey, it was tested unsat, lets look at the retests and see if the retest is unsat or sat? Then use those values in my crosstab query so I can tell how many zones are unsat, or sat, or utested?

Any ideas folks?

Thanks!

 

[hneal98]

You would be better off doing that in a form or report. A query can not easily be changed like that, except through programming.

 

[Wiz0fBaud]

I don't quite understand what you mean.....I can't just say if UNSAT in this column, check this column and if thats SAT then count this column instead of the first?

 

[hneal98]

Do you have the SAT, UNSAT, and UNTESTED in the same field? If so, just put int he criteria of that field in the query something liket this:

[Enter Status]

Since the query will not understand that statement in the criteria row, it will prompt the user to enter status. Then you just enter SAT or UNSAT or UNTESTED accordingly.

 

[Wiz0fBaud]

And if they're not in the same field, but rather seperate fields with check boxes....
SAT
UNSAT
RetestSAT
RetestUNSAT
Thanks

 

[hneal98]

That is where you run into the problem I was talking about above. You would need to create a query that would put those values in the same field, then it would be easier to do your crosstab against that query instead of the table.