help hiding query duplicate

  • Thread starter Thread starter Rick Stahl
  • Start date Start date
R

Rick Stahl

For some reason I have a duplicate in my query that shouldn't be there. One
employee has been promoted 2 times and only the most recent time should be
displayed. I selected this by chosing Max PromoDate. Thanks !

SELECT tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, Max(tblJobPromotions.dtmPromoDate)
AS MaxOfdtmPromoDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle])
AS strCurJobTitle,
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate])
AS dtmBasePromoDate
FROM (tblPersonalInfo
LEFT JOIN tblJobPromotions
ON (tblPersonalInfo.strLastName = tblJobPromotions.strLastName)
AND (tblPersonalInfo.strFirstName = tblJobPromotions.strFirstName))
INNER JOIN [tblJobTitle&PayRates]
ON tblPersonalInfo.strHiredTitle = [tblJobTitle&PayRates].strJobTitle
GROUP BY tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle]),
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate]),
[tblJobTitle&PayRates].ysnSalary, tblPersonalInfo.strHiredTitle
HAVING ((([tblJobTitle&PayRates].ysnSalary)=No));
 
Sorry, but I believe I need help. Where do I choose Distinct ?
I have already set the query to Unique Values or Unique Records and still no
dice.
The problem may be in the original table of promotions which list the same
employee with 2 different promotion on different dates. For some reason
though both are carried onto the query eventhough I selected Last and/or
Unique. Thanks


Maurice said:
I think adding the distinct word should do the trick so:

Select distinct .......

hth
--
Maurice Ausum


Rick Stahl said:
For some reason I have a duplicate in my query that shouldn't be there.
One
employee has been promoted 2 times and only the most recent time should
be
displayed. I selected this by chosing Max PromoDate. Thanks !

SELECT tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, Max(tblJobPromotions.dtmPromoDate)
AS MaxOfdtmPromoDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle])
AS strCurJobTitle,
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate])
AS dtmBasePromoDate
FROM (tblPersonalInfo
LEFT JOIN tblJobPromotions
ON (tblPersonalInfo.strLastName = tblJobPromotions.strLastName)
AND (tblPersonalInfo.strFirstName = tblJobPromotions.strFirstName))
INNER JOIN [tblJobTitle&PayRates]
ON tblPersonalInfo.strHiredTitle = [tblJobTitle&PayRates].strJobTitle
GROUP BY tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle]),
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate]),
[tblJobTitle&PayRates].ysnSalary, tblPersonalInfo.strHiredTitle
HAVING ((([tblJobTitle&PayRates].ysnSalary)=No));
Click to expand...
Click to expand...
 
Ok,

I assume you've created the query in the query designer from Access right?
If you open your query in designview you should see a button in the upper
left corner which shows a triangel, pencil etc. Click on the little black
arrow beside the button. You can see tha various optiosn to choose from.
Select SQL-view.

You should now see your SQL-statment there. Right after the word Select type
the word Distinct. After the word distinct type a space and leave the rest as
it is.

try running the query to see if that made any change in regards to the
double record.

hth
--
Maurice Ausum


Rick Stahl said:
Sorry, but I believe I need help. Where do I choose Distinct ?
I have already set the query to Unique Values or Unique Records and still no
dice.
The problem may be in the original table of promotions which list the same
employee with 2 different promotion on different dates. For some reason
though both are carried onto the query eventhough I selected Last and/or
Unique. Thanks


Maurice said:
I think adding the distinct word should do the trick so:

Select distinct .......

hth
--
Maurice Ausum


Rick Stahl said:
For some reason I have a duplicate in my query that shouldn't be there.
One
employee has been promoted 2 times and only the most recent time should
be
displayed. I selected this by chosing Max PromoDate. Thanks !

SELECT tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, Max(tblJobPromotions.dtmPromoDate)
AS MaxOfdtmPromoDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle])
AS strCurJobTitle,
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate])
AS dtmBasePromoDate
FROM (tblPersonalInfo
LEFT JOIN tblJobPromotions
ON (tblPersonalInfo.strLastName = tblJobPromotions.strLastName)
AND (tblPersonalInfo.strFirstName = tblJobPromotions.strFirstName))
INNER JOIN [tblJobTitle&PayRates]
ON tblPersonalInfo.strHiredTitle = [tblJobTitle&PayRates].strJobTitle
GROUP BY tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle]),
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate]),
[tblJobTitle&PayRates].ysnSalary, tblPersonalInfo.strHiredTitle
HAVING ((([tblJobTitle&PayRates].ysnSalary)=No));
Click to expand...
Click to expand...
Click to expand...
 
Thank you. I tried this and no dice. I really appreciate your help. Any
other suggestions are greatly appreciated.


Maurice said:
Ok,

I assume you've created the query in the query designer from Access right?
If you open your query in designview you should see a button in the upper
left corner which shows a triangel, pencil etc. Click on the little black
arrow beside the button. You can see tha various optiosn to choose from.
Select SQL-view.

You should now see your SQL-statment there. Right after the word Select
type
the word Distinct. After the word distinct type a space and leave the rest
as
it is.

try running the query to see if that made any change in regards to the
double record.

hth
--
Maurice Ausum


Rick Stahl said:
Sorry, but I believe I need help. Where do I choose Distinct ?
I have already set the query to Unique Values or Unique Records and still
no
dice.
The problem may be in the original table of promotions which list the
same
employee with 2 different promotion on different dates. For some reason
though both are carried onto the query eventhough I selected Last and/or
Unique. Thanks


Maurice said:
I think adding the distinct word should do the trick so:

Select distinct .......

hth
--
Maurice Ausum


:

For some reason I have a duplicate in my query that shouldn't be
there.
One
employee has been promoted 2 times and only the most recent time
should
be
displayed. I selected this by chosing Max PromoDate. Thanks !

SELECT tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, Max(tblJobPromotions.dtmPromoDate)
AS MaxOfdtmPromoDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle])
AS strCurJobTitle,
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate])
AS dtmBasePromoDate
FROM (tblPersonalInfo
LEFT JOIN tblJobPromotions
ON (tblPersonalInfo.strLastName = tblJobPromotions.strLastName)
AND (tblPersonalInfo.strFirstName = tblJobPromotions.strFirstName))
INNER JOIN [tblJobTitle&PayRates]
ON tblPersonalInfo.strHiredTitle = [tblJobTitle&PayRates].strJobTitle
GROUP BY tblPersonalInfo.strFirstName, tblPersonalInfo.strLastName,
tblPersonalInfo.dtmHiredDate, tblJobPromotions.strPromoTitle,
IIf([tblPersonalInfo]![dtmHiredDate]<[tblJobPromotions]![dtmPromoDate],[tblJobPromotions]![strPromoTitle],[tblPersonalInfo]![strHiredTitle]),
IIf([tblJobPromotions]![dtmPromoDate]>0,[tblJobPromotions]![dtmPromoDate],[tblPersonalInfo]![dtmHiredDate]),
[tblJobTitle&PayRates].ysnSalary, tblPersonalInfo.strHiredTitle
HAVING ((([tblJobTitle&PayRates].ysnSalary)=No));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top