Query from two fields

[pdbaker]

I have a query setup from lets say a table called table A

In table A there are two colums one called Job A and Job B

I want to run a query so that if the job say window cleaner is found in either job a or job b it will show up the results but if i try putting in window cleaner in the critiria the query shows no results yet i can open up Table A and see that either under Job A or Job B there is window cleaner down.

What is it im doing wrong and is it possable to get to to search two colums and only have a result in either.

 

[Alansidman]

Show us your SQL statement so we can see your query set up.

Alan

 

[georgedwilkinson]

Not a normalized design. You'll have problems with it for all its life.

 

[sbenj69]

While it's not a normalized design, you could make expressions for both:

Expr1: iif([yourfieldA] = "cleaner", 1,0)
Expr2: iif([yourfieldB] = "cleaner", 1,0)

Save your query......

Expr3: [Expr1]+[Expr2]
Criteria for Expr3: 1 or 2

That should get you what you need.

If you need a parameter (a value you enter to search for) then:

In the "Fields" row put this: Expr1: [Enter a item to search for]

Save your query.....

Expr2: iif([yourfieldA] = [Expr1],1,0)
Expr3: iif([yourfieldB] = [Expr1],1,0)

Save your query......

Expr4: [Expr1]+[Expr2]
Criteria for Expr4: 1 or 2

 

[pdbaker]

Here is my SQL Code for the query that someone wanted:

SELECT Jobtrain.Situation, Jobtrain.Client, Jobtrain.[Start Date], Jobtrain.[End Date], Jobtrain.[Situation 2], Jobtrain.[Start Date 2], Jobtrain.[End Date 2], IIf([Situation]="Training",1,0) AS Expr1, IIf([Situation2]="Training",1,0) AS Expr2
FROM Jobtrain
GROUP BY Jobtrain.Situation, Jobtrain.Client, Jobtrain.[Start Date], Jobtrain.[End Date], Jobtrain.[Situation 2], Jobtrain.[Start Date 2], Jobtrain.[End Date 2], IIf([Situation]="Training",1,0), IIf([Situation2]="Training",1,0), [Expr1]+[Expr2]
HAVING (((Jobtrain.[Start Date]) Between [Forms]![Clients]![reports_subform]![cboStartDate] And [Forms]![Clients]![reports_subform]![cboEndDate]) AND ((Jobtrain.[End Date]) Between [Forms]![Clients]![reports_subform]![cboStartDate] And [Forms]![Clients]![reports_subform]![cboEndDate]) AND ((Jobtrain.[Start Date 2]) Between [Forms]![Clients]![reports_subform]![cboStartDate] And [Forms]![Clients]![reports_subform]![cboEndDate]) AND ((Jobtrain.[End Date 2]) Between [Forms]![Clients]![reports_subform]![cboStartDate] And [Forms]![Clients]![reports_subform]![cboEndDate]) AND (([Expr1]+[Expr2])=1 Or ([Expr1]+[Expr2])=2));

 

[namliam]

Here is my SQL Code for the query that someone wanted:

Nobody wanted that code It is friggin unreadable....

And please use [ code ] and [/ code ] without the spaces whenever you post (SQL) code...

SELECT Jobtrain.Situation
,      Jobtrain.Client
,      Jobtrain.[Start Date]
,      Jobtrain.[End Date]
,      Jobtrain.[Situation 2]
,      Jobtrain.[Start Date 2]
,      Jobtrain.[End Date 2]
,      IIf([Situation]="Training" ,1,0) AS Expr1
,      IIf([Situation2]="Training",1,0) AS Expr2
FROM Jobtrain
GROUP BY Jobtrain.Situation
,        Jobtrain.Client, Jobtrain.[Start Date]
,        Jobtrain.[End Date]
,        Jobtrain.[Situation 2]
,        Jobtrain.[Start Date 2]
,        Jobtrain.[End Date 2]
,        IIf([Situation]="Training",1,0)
,        IIf([Situation2]="Training",1,0)
,        [Expr1]+[Expr2]
HAVING   Jobtrain.[Start Date]   Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND  Jobtrain.[End Date]     Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND  Jobtrain.[Start Date 2] Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND Jobtrain.[End Date 2]    Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND ([Expr1]+[Expr2]=1 Or [Expr1]+[Expr2]=2);

1) I must agree with georgedwilkinson most any time you use things like 1 and 2 you have a problem with your design! Why have 2 columns of exactly the same information

2) DONT use spaces in your column names!

3) Why use a group by in this query?? You are not using anything with max,sum, avg, etc. So why group it?

4) AND ([Expr1]+[Expr2]=1 Or [Expr1]+[Expr2]=2
What is that all about?? Isnt that the same as ([Expr1]=1 or [Expr2]=1), KISS man, KISS*

* KISS = Keep It Simple Smartguy**

** Sometimes given another S...

5) Your question, yes offcourse it is possible! Anything and everything is possible. A simple or will do your trick.

Eventho this is a temporary fix, you really should think about redesigning this, this will keep bothering you and what about if a training happens in a third or fourth type? Then your DB cannot handle it anymore, redesigning it will solve this and a whole host of other potential issues in the future.

SELECT Jobtrain.Situation
,      Jobtrain.Client
,      Jobtrain.[Start Date]
,      Jobtrain.[End Date]
,      Jobtrain.[Situation 2]
,      Jobtrain.[Start Date 2]
,      Jobtrain.[End Date 2]
,      IIf([Situation]="Training" ,1,0) AS Expr1
,      IIf([Situation2]="Training",1,0) AS Expr2
FROM Jobtrain
Where    Jobtrain.[Start Date]   Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND  Jobtrain.[End Date]     Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND  Jobtrain.[Start Date 2] Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND Jobtrain.[End Date 2]    Between [Forms]![Clients]![reports_subform]![cboStartDate]
                                     And [Forms]![Clients]![reports_subform]![cboEndDate]
    AND ([Expr1]=1 Or [Expr2]=1)
    AND (Jobtrain.Situation = 'window cleaner' OR Jobtrain.Situation2 = 'window cleaner'
;

 

[pdbaker]

sorry for all the code but they asked for the SQL so i pasted it in. Because the form im using it is trying to record if they are getting training from a combo box and a start and end date but also doing a second time incase they are doing two courses.