ChampionDuy
05-07-2002, 04:34 AM
I have the following SQl statement that returns a number of aspects about a certain record. However each record may have more than one date associated with it that is stored in another table. I receive all teh dates that are associated with it but I also get hundreds of other lines of the same dates just with different combinations of them. How can I get just the dates I want and not all the extras.
PARAMETERS [Enter CCN of case you wish to view] Value, [Enter starting date: (MM/DD/YYYY)] Value, [Enter ending date: (MM/DD/YYYY)] Value;
SELECT DISTINCT [CASEFILE].[TYPE], [CASEFILE].[CRIME], [CASEFILE].[CCN], [CASEFILE].[COMMAND], [CASEFILE].[LOCATE], [CASEFILE].[PERUNIT], [CASEFILE].[APPROVE], [CASEFILE].[AUTH], [RENEW].[RENEW_DATE], [TARDATE].[INT_DATE], [CASEFILE].[EXPDATE], [CASEFILE].[STATUS]
FROM (CASEFILE INNER JOIN RENEW ON [CASEFILE].[CCN]=[RENEW].[CCN]) INNER JOIN TARDATE ON [CASEFILE].[CCN]=[TARDATE].[CCN]
WHERE ([casefile].[auth]>=[Enter starting date: (MM/DD/YYYY)] And [casefile].[auth]<=[Enter ending date: (MM/DD/YYYY)]) Or ([casefile].[expdate]>=[Enter starting date: (MM/DD/YYYY)] And [casefile].[expdate]<=[Enter ending date: (MM/DD/YYYY)]) And [casefile].[ccn]=[Enter CCN of case you wish to view];
PARAMETERS [Enter CCN of case you wish to view] Value, [Enter starting date: (MM/DD/YYYY)] Value, [Enter ending date: (MM/DD/YYYY)] Value;
SELECT DISTINCT [CASEFILE].[TYPE], [CASEFILE].[CRIME], [CASEFILE].[CCN], [CASEFILE].[COMMAND], [CASEFILE].[LOCATE], [CASEFILE].[PERUNIT], [CASEFILE].[APPROVE], [CASEFILE].[AUTH], [RENEW].[RENEW_DATE], [TARDATE].[INT_DATE], [CASEFILE].[EXPDATE], [CASEFILE].[STATUS]
FROM (CASEFILE INNER JOIN RENEW ON [CASEFILE].[CCN]=[RENEW].[CCN]) INNER JOIN TARDATE ON [CASEFILE].[CCN]=[TARDATE].[CCN]
WHERE ([casefile].[auth]>=[Enter starting date: (MM/DD/YYYY)] And [casefile].[auth]<=[Enter ending date: (MM/DD/YYYY)]) Or ([casefile].[expdate]>=[Enter starting date: (MM/DD/YYYY)] And [casefile].[expdate]<=[Enter ending date: (MM/DD/YYYY)]) And [casefile].[ccn]=[Enter CCN of case you wish to view];