Sub query?

[Les Isaacs]

Hello All

I have a table [x confirmed], and two of its fields are 'staff_name' (text)and 'fulltime' (Yes/No). There are many records in the table for each 'staff_name' value: generally a given 'staff_name' value will always have the same 'fulltime' value, but I need a query that will return ALL the records for any 'staff_name' value where this is NOT the case. So those 'staff_name' values that always have a 'fulltime' value of 'True' (or always 'False') should be EXCLUDED from the query, but any 'staff_name' values that have one or more records with a 'fulltime' value of 'True' AND one or more records with a 'fulltime' values of 'False' should be returned by the query.

I thought I could do this with a subquery, but can't seem to get it right

Hope someone can help.
Thanks in advance
Les

 

[vbaInet]

You only need one query. How did you write the conditions in your query?

 

[plog]

1 query with some complex conditions or a sub-query will do this. Here is the SQL to do it in one:

SELECT [x confirmed].staff_name
FROM [x confirmed]
GROUP BY [x confirmed].staff_name
HAVING (((IIf(Max([fulltime])=Min([fulltime]),0,1))=1));

 

[Les Isaacs]

Hello plog and vbaInet

Many thanks for your help: almost there but not quite!

The code you - plog - suggested does succeed in returning one record for each [staff_name] that has both 'fulltime' values, but what I really need is to return ALL the records for these employees. So perhaps it's a case of using your code as a subquery?

The other issue is that I needed to add a criteria, so have added this to your code and now have:

SELECT [x confirmed].staff_name, months.year
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x confirmed].[month name]
WHERE ((([x confirmed].practice)=[forms]![frm x main]![prac name]))
GROUP BY [x confirmed].staff_name, months.year
HAVING (((IIf(Max([fulltime])=Min([fulltime]),0,1))=1) AND ((months.year)=[forms]![frm x main]![year]));

Many thanks for any further help.
Les

 

[vbaInet]

We need to understand your table structure. Can you show us some sample records in a spreadsheet.