Query Help

[Reggie2004]

This is the code. I was in the wrong forum earlier.

SELECT [Why not workable].[SSN P ], [Why not workable].TXPD, [Why not workable].[LFRZ-RFRZ], [Why not workable].[TC-150], [Why not workable].[TC-290], [Why not workable].[TC-291], [Why not workable].[TC-300], [Why not workable].[TC-301], [Why not workable].[TC-420], [Why not workable].[TC-421], [Why not workable].[TC-424], [Why not workable].[TC-530], [Why not workable].[TC-540], [Why not workable].[TC-590], [Why not workable].[TC-591], [Why not workable].[TC-594], [Why not workable].[TC-599], [Why not workable].[TC-976], [Why not workable].[TC-977], [Why not workable].[ DOB ], [Why not workable].[ DOD ]
FROM [Why not workable]
WHERE [TXPD] > (SELECT MIN(TXPD) FROM [Why not workable])
AND [SSN P] > (SELECT MIN([SSN P]) FROM [Why not workable])
GROUP BY [SSN P]
HAVING COUNT (DISTINCT([SSN P])) > 1
My SSN P field has multiple years [TXPD]. I am trying to query all years except the first. I got the error message
Undefined function 'distinct' in expression. Please help...

 

[cjowsey]

I get the feeling that I am coming in half way through a conversation, but let's try.

DISTINCT is an SQL keyword normally used after SELECT. I don't believe it is a function unless you have written your own.

You could use more meaningful names to aid debugging. There is no way I could know that TXPD is a year.

I don't understand the statement "My SSN P field has multiple years [TXPD]." It looks to me like they are both columns in the [Why not workable] table.

It is not clear from the SQL or your description if you are trying to return all rows where TXPD and SSN P are greater than the minimum value in the table or a single row. I don't think you need the GROUP BY or HAVING clauses as the WHERE clause will exclude the lowest value of TXPD and SSN P.

Have a good look at the SELECT statement in Books Online. You should be able to find some examples there of the syntax you are looking for.

 

[Reggie2004]

O.k. This is the complete process. I have one query.... This is the code

SELECT final.[SSN P ], final.TXPD, final.[LFRZ-RFRZ], final.[TC-150], final.[TC-290], final.[TC-291], final.[TC-300], final.[TC-301], final.[TC-420], final.[TC-421], final.[TC-424], final.[TC-530], final.[TC-540], final.[TC-590], final.[TC-591], final.[TC-594], final.[TC-599], final.[TC-976], final.[TC-977], final.[ DOB ], final.[ DOD ]
FROM final
WHERE (((final.[TC-290])="TC-290")) OR (((final.[TC-291])="TC-291") AND ((final.[ DOD ])="dead")) OR (((final.[TC-300])="TC-300")) OR (((final.[TC-301])="TC-301")) OR (((final.[TC-976])="TC-976")) OR (((final.[TC-977])="TC-977")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-150])="TC-150")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-530])="TC-530")) OR (((final.[TC-540])="TC-540")) OR (((final.[TC-590])="TC-590")) OR (((final.[TC-591])="TC-591")) OR (((final.[TC-594])="TC-594")) OR (((final.[TC-599])="TC-599"));

It pull out what I am filtering for.

Next I am quering off this query to eliminate the first year with this code..

SELECT [Why not workable].[SSN P ], [Why not workable].TXPD, [Why not workable].[LFRZ-RFRZ], [Why not workable].[TC-150], [Why not workable].[TC-290], [Why not workable].[TC-291], [Why not workable].[TC-300], [Why not workable].[TC-301], [Why not workable].[TC-420], [Why not workable].[TC-421], [Why not workable].[TC-424], [Why not workable].[TC-530], [Why not workable].[TC-540], [Why not workable].[TC-590], [Why not workable].[TC-591], [Why not workable].[TC-594], [Why not workable].[TC-599], [Why not workable].[TC-976], [Why not workable].[TC-977], [Why not workable].[ DOB ], [Why not workable].[ DOD ]
FROM [Why not workable]
WHERE ((([Why not workable].TXPD)<>(SELECT MIN(TXPD) FROM [Why not workable])))
GROUP BY [Why not workable].[SSN P ], [Why not workable].TXPD, [Why not workable].[LFRZ-RFRZ], [Why not workable].[TC-150], [Why not workable].[TC-290], [Why not workable].[TC-291], [Why not workable].[TC-300], [Why not workable].[TC-301], [Why not workable].[TC-420], [Why not workable].[TC-421], [Why not workable].[TC-424], [Why not workable].[TC-530], [Why not workable].[TC-540], [Why not workable].[TC-590], [Why not workable].[TC-591], [Why not workable].[TC-594], [Why not workable].[TC-599], [Why not workable].[TC-976], [Why not workable].[TC-977], [Why not workable].[ DOB ], [Why not workable].[ DOD ], [Why not workable].[ DOD ]
HAVING (((Count([Why not workable].[SSN P ]))>1));

Now the code works until I add the SSN P field. When the SSN P field is added I no data. But without the SSN P field, I get the correct data. Anyone, EVERYONE please help. I am at my wits end on this query.

 

[cjowsey]

Why not simply take your first query, add a set of brackets round the WHERE clause and put AND (final.TXPD <>(SELECT MIN(TXPD) FROM final)) on the end of it. Like this:

SELECT final.[SSN P], final.TXPD, final.[LFRZ-RFRZ], final.[TC-150], final.[TC-290], final.[TC-291], final.[TC-300], final.[TC-301], final.[TC-420], final.[TC-421], final.[TC-424], final.[TC-530], final.[TC-540], final.[TC-590], final.[TC-591], final.[TC-594], final.[TC-599], final.[TC-976], final.[TC-977], final.[DOB], final.[DOD]
FROM final
WHERE ((((final.[TC-290])="TC-290")) OR (((final.[TC-291])="TC-291") AND ((final.[ DOD ])="dead")) OR (((final.[TC-300])="TC-300")) OR (((final.[TC-301])="TC-301")) OR (((final.[TC-976])="TC-976")) OR (((final.[TC-977])="TC-977")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-150])="TC-150")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-530])="TC-530")) OR (((final.[TC-540])="TC-540")) OR (((final.[TC-590])="TC-590")) OR (((final.[TC-591])="TC-591")) OR (((final.[TC-594])="TC-594")) OR (((final.[TC-599])="TC-599"))) AND (final.TXPD <>(SELECT MIN(TXPD) FROM final))

Then you won't need your second query.

 

[Reggie2004]

You are good. I am a novice. I have earlier programming skill from the dark ages. I have a new job but things are getting rough. Thanks so much.

Reg