Query Headache...

[dodgyone]

Could somebody please help me with this query before I go mad!

This is the first query example. It brings up a list of records and in there are records with the same Quest_ID so there are matching related records in there:

SELECT * FROM tbl_Questionnaire LEFT JOIN tbl_Question1 ON tbl_Questionnaire.Quest_ID = tbl_Question1.Quest_ID
WHERE (((tbl_Question1.Description)="Recruitment" Or (tbl_Question1.Description)="Staff Costs") AND ((tbl_Question1.ResultClosure)=True))
ORDER BY tbl_Questionnaire.Reference_ID;

I then use AND instead of OR for Description and no records are displayed. There are some records which match this criteria as the linking Quest_ID showed this on the previous query:

SELECT * FROM tbl_Questionnaire INNER JOIN tbl_Question1 ON tbl_Questionnaire.Quest_ID = tbl_Question1.Quest_ID
WHERE (((tbl_Question1.Description)="Recruitment" And (tbl_Question1.Description)="Staff Costs") AND ((tbl_Question1.ResultClosure)=True));


I hope this is a good enough explanation for you to be able to help me. Thanks in advance....



================================
Chaos, panic & disorder - my work here is done!
================================

 

[dodgyone]

Ah I think I might have it. Should it be?

SELECT * FROM tbl_Questionnaire INNER JOIN tbl_Question1 ON tbl_Questionnaire.Quest_ID = tbl_Question1.Quest_ID
WHERE (((tbl_Question1.Description)="Recruitment") AND ((tbl_Question1.ResultClosure)=True) AND ((tbl_Question1.InhibitDevelopment)=True)) OR (((tbl_Question1.Description)="Staff Costs") AND ((tbl_Question1.ResultClosure)=True));

================================
Chaos, panic & disorder - my work here is done!
================================

 

[PHV]

Something like this ?
SELECT * FROM tbl_Questionnaire A INNER JOIN tbl_Question1 B ON A.Quest_ID = B.Quest_ID
WHERE B.ResultClosure=True AND (
(B.Description="Recruitment" AND B.InhibitDevelopment=True)
OR B.Description="Staff Costs");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodgyone]

Thanks PHV but I'm still having no joy!

In basic descriptive terms this is what I want. Actually It has come to the point where I just don't think it is possible to search on the [Description] field many times in the same query!

Stage 1 = displays all records where either of the criteria set is true. It is not possible for both to be set as true in this situation:

([Description] = "Recruitment" and [ResultClosure] = True)
OR ([Description] = "Recruitment" and [InhibitDevelopment] = True).

Stage 2 = I then want to add this additional search criteria to catch these records after the first stage has been accomplished:

([Description] = "Staff costs" and [ResultClosure] = True)

I know this isn't correct but as a starting point I now have this:

SELECT tbl_Questionnaire.Quest_ID, tbl_Questionnaire.Reference_ID, tbl_Question1.Description,
tbl_Questionnaire.D13_AnnualTurnover, tbl_Question1.ResultClosure, tbl_Question1.InhibitDevelopment,
tbl_Question1.MinorProblem, tbl_Question1.NotProblem, tbl_Question1.Invalid

FROM tbl_Questionnaire INNER JOIN tbl_Question1 ON tbl_Questionnaire.Quest_ID = tbl_Question1.Quest_ID

WHERE (

(tbl_Question1.Description = "Recruitment" AND tbl_Question1.ResultClosure=True) OR (tbl_Question1.Description = "Recruitment" AND tbl_Question1.InhibitDevelopment=True)

)

AND

(tbl_Question1.Description = "Staff Costs" AND tbl_Question1.ResultClosure=True)

 

[PHV]

Something like this ?
SELECT * FROM tbl_Questionnaire A INNER JOIN tbl_Question1 B ON A.Quest_ID = B.Quest_ID
WHERE (B.Description="Recruitment" AND (B.ResultClosure=True OR B.InhibitDevelopment=True))
OR (B.Description="Staff Costs" AND B.ResultClosure=True);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodgyone]

That doesn't quite give the required results. The problem is that the part of the query relating to [Description]="Recruitment" should be grouped as either reslt is valid. Then [Description]="Staff Costs" should then be used to filter through again.

I thought about querying on a query but still I'm not getting the right results.

Sorry about this but the boss is shouting for this and time is running out!

 

[PHV]

Are you saying that Stage2 is a filter applied to Stage1's resultset ?
All Description in Stage1 are "Recruitment", so no one can be "Staff Costs" ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodgyone]

Sorry about this, it gets pretty confusing!

At the moment it is all in the one query and I would prefer it to be like this. I was only thinking along the lines that perhaps the answer was to do things in two stages. So, query the first stage results table with another query to filter out what is required.

 

[PHV]

What are the REAL requirements ?
Please post some input samples and expected result.