Duplicate Records

[Database girl]

The following is a example of a record set I am work with:

Order# Category
2335 Dispatcher
2335 Planner
2456 Planner
2567 Dispatcher

I have created a query to pull the recordset. However, I only want to
return one record according to the following criteria:

If there are duplicate Order#, then return the records with Category =
Dispatcher.

I do not know how to write the if then statement that will look for
duplicates and then when it finds one return the correct record.

The following is the select statement for pulling the recordset:
SELECT tblPartsManufactures.SalesDivision, tblDivisions.DivisionName,
tblOrders.ManufacturerID, tblLocations.Locat, tblOrders.DateToPack,
IIf(IsNull([DateSchedule]),"",Mid$([DateSchedule],5,2) & "-" &
Right$([DateSchedule],1)) AS Sch, tblOrders.Location, tblOrders.OrderNumber,
tblOrders.PartNumber, tblOrders.Finish, tblParts.Description,
tblOrders.QuantityOrdered, tblOrders.QuantityToPack,
tblOrders.QuantityToManf, tblOrders.QuantityToShip,
tblCasemailIDs.CaseMailID, tblPartsPeople.Category, tblPartsPeople.Priority,
tblOrders.CorrectiveAction, tblParts.TypeCode, tblCasemailIDs.BusinessUnit,
First(tblPartsPeople.Category) AS NewCategory
FROM ((tblParts INNER JOIN ((tblPartsManufactures INNER JOIN (tblPartsPeople
INNER JOIN (tblOrders INNER JOIN tblLocations ON tblOrders.Location =
tblLocations.Abbrev) ON (tblPartsPeople.PartNumber = tblOrders.PartNumber)
AND (tblPartsPeople.ManufacturerID = tblOrders.ManufacturerID)) ON
(tblPartsManufactures.PartNumber = tblOrders.PartNumber) AND
(tblPartsManufactures.ManufacturerID = tblOrders.ManufacturerID)) INNER JOIN
tblDivisions ON (tblPartsManufactures.ManufacturerID =
tblDivisions.ManufacturerID) AND (tblPartsManufactures.SalesDivision =
tblDivisions.DivisionCode)) ON tblParts.PartNumber = tblOrders.PartNumber)
INNER JOIN tblCasemailIDs ON tblPartsPeople.CaseMailID =
tblCasemailIDs.CaseMailID) INNER JOIN tblMFCalendar ON tblOrders.DateToPack =
tblMFCalendar.DateCalendar2
GROUP BY tblPartsManufactures.SalesDivision, tblDivisions.DivisionName,
tblOrders.ManufacturerID, tblLocations.Locat, tblOrders.DateToPack,
IIf(IsNull([DateSchedule]),"",Mid$([DateSchedule],5,2) & "-" &
Right$([DateSchedule],1)), tblOrders.Location, tblOrders.OrderNumber,
tblOrders.PartNumber, tblOrders.Finish, tblParts.Description,
tblOrders.QuantityOrdered, tblOrders.QuantityToPack,
tblOrders.QuantityToManf, tblOrders.QuantityToShip,
tblCasemailIDs.CaseMailID, tblPartsPeople.Category, tblPartsPeople.Priority,
tblOrders.CorrectiveAction, tblParts.TypeCode, tblCasemailIDs.BusinessUnit
HAVING (((tblOrders.QuantityToManf)>0) AND
((tblPartsPeople.Category)="Dispatcher" Or
(tblPartsPeople.Category)="Planner") AND ((tblPartsPeople.Priority)=1) AND
((tblCasemailIDs.BusinessUnit) Is Not Null));

Thank you in advance for your assistance.

 

[Jerry Whittle]

This is probably too simple of an answer for your needs and depends on you
needing ONLY the two fields below AND Dispatcher and Planner are the only two
catagories.

SELECT [Order#],
First(Category) as TheCategory
FROM TheQuery
GROUP BY [Order#]
ORDER BY [Order#], Category ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The following is a example of a record set I am work with:

Order# Category
2335 Dispatcher
2335 Planner
2456 Planner
2567 Dispatcher

I have created a query to pull the recordset. However, I only want to
return one record according to the following criteria:

If there are duplicate Order#, then return the records with Category =
Dispatcher.

I do not know how to write the if then statement that will look for
duplicates and then when it finds one return the correct record.

The following is the select statement for pulling the recordset:
SELECT tblPartsManufactures.SalesDivision, tblDivisions.DivisionName,
tblOrders.ManufacturerID, tblLocations.Locat, tblOrders.DateToPack,
IIf(IsNull([DateSchedule]),"",Mid$([DateSchedule],5,2) & "-" &
Right$([DateSchedule],1)) AS Sch, tblOrders.Location, tblOrders.OrderNumber,
tblOrders.PartNumber, tblOrders.Finish, tblParts.Description,
tblOrders.QuantityOrdered, tblOrders.QuantityToPack,
tblOrders.QuantityToManf, tblOrders.QuantityToShip,
tblCasemailIDs.CaseMailID, tblPartsPeople.Category, tblPartsPeople.Priority,
tblOrders.CorrectiveAction, tblParts.TypeCode, tblCasemailIDs.BusinessUnit,
First(tblPartsPeople.Category) AS NewCategory
FROM ((tblParts INNER JOIN ((tblPartsManufactures INNER JOIN (tblPartsPeople
INNER JOIN (tblOrders INNER JOIN tblLocations ON tblOrders.Location =
tblLocations.Abbrev) ON (tblPartsPeople.PartNumber = tblOrders.PartNumber)
AND (tblPartsPeople.ManufacturerID = tblOrders.ManufacturerID)) ON
(tblPartsManufactures.PartNumber = tblOrders.PartNumber) AND
(tblPartsManufactures.ManufacturerID = tblOrders.ManufacturerID)) INNER JOIN
tblDivisions ON (tblPartsManufactures.ManufacturerID =
tblDivisions.ManufacturerID) AND (tblPartsManufactures.SalesDivision =
tblDivisions.DivisionCode)) ON tblParts.PartNumber = tblOrders.PartNumber)
INNER JOIN tblCasemailIDs ON tblPartsPeople.CaseMailID =
tblCasemailIDs.CaseMailID) INNER JOIN tblMFCalendar ON tblOrders.DateToPack =
tblMFCalendar.DateCalendar2
GROUP BY tblPartsManufactures.SalesDivision, tblDivisions.DivisionName,
tblOrders.ManufacturerID, tblLocations.Locat, tblOrders.DateToPack,
IIf(IsNull([DateSchedule]),"",Mid$([DateSchedule],5,2) & "-" &
Right$([DateSchedule],1)), tblOrders.Location, tblOrders.OrderNumber,
tblOrders.PartNumber, tblOrders.Finish, tblParts.Description,
tblOrders.QuantityOrdered, tblOrders.QuantityToPack,
tblOrders.QuantityToManf, tblOrders.QuantityToShip,
tblCasemailIDs.CaseMailID, tblPartsPeople.Category, tblPartsPeople.Priority,
tblOrders.CorrectiveAction, tblParts.TypeCode, tblCasemailIDs.BusinessUnit
HAVING (((tblOrders.QuantityToManf)>0) AND
((tblPartsPeople.Category)="Dispatcher" Or
(tblPartsPeople.Category)="Planner") AND ((tblPartsPeople.Priority)=1) AND
((tblCasemailIDs.BusinessUnit) Is Not Null));

Thank you in advance for your assistance.