Only want to see first record

  • Thread starter Thread starter Lori2836 via AccessMonster.com
  • Start date Start date
L

Lori2836 via AccessMonster.com

Good morning. I have one table where there may be many of the same part
numbers with different criteria. The requestor only wants to see the one
with the earliest start date. How would I set that up? Here is the SQL
for the existing query.

SELECT [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master Dispatch].
[MFG Order #], [Master Dispatch].[Order Status], [Master Dispatch].[Order Qty]
, [Master Dispatch].[Balance Due], [Master Dispatch].[Opern Description],
[Master Dispatch].[Work Center], [Master Dispatch].[Start Date], [Master
Dispatch].[Due Date]
FROM [Fiscal Dates] RIGHT JOIN [Master Dispatch] ON [Fiscal Dates].Date =
[Master Dispatch].[Start Date]
GROUP BY [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master Dispatch].
[MFG Order #], [Master Dispatch].[Order Status], [Master Dispatch].[Order Qty]
, [Master Dispatch].[Balance Due], [Master Dispatch].[Opern Description],
[Master Dispatch].[Work Center], [Master Dispatch].[Start Date], [Master
Dispatch].[Due Date]
HAVING (((([Master Dispatch].[Opern Description]) Like "*face*" Or ([Master
Dispatch].[Opern Description]) Like "*machine*" Or ([Master Dispatch].[Opern
Description]) Like "*grumman*" Or ([Master Dispatch].[Opern Description])
Like "*turn*") And ([Master Dispatch].[Opern Description]) Not Like "*side
2*" And ([Master Dispatch].[Opern Description]) Not Like "*2nd*" And ([Master
Dispatch].[Opern Description]) Not Like "*finish grind*") AND (([Master
Dispatch].[Work Center])=" 18000"))
ORDER BY [Master Dispatch].[Item ID];


Thank you for any help you can give me!
 
You used the phrase "may be many of the same part numbers" but there is not a
field name of part number.
What field inconjunction with the "earliest start date" does the requestor
want?

If it is the [Item ID] then try the queries below. I named them Lori2836_X,
Lori2836_X1, and Lori2836_X2.

Lori2836_X ---
SELECT [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master
Dispatch].[MFG Order #], [Master Dispatch].[Order Status], [Master
Dispatch].[Order Qty], [Master Dispatch].[Balance Due], [Master
Dispatch].[Opern Description], [Master Dispatch].[Work Center], [Master
Dispatch].[Start Date], [Master Dispatch].[Due Date]
FROM Lori2836_X1 INNER JOIN ([Fiscal Dates] RIGHT JOIN [Master Dispatch] ON
[Fiscal Dates].date = [Master Dispatch].[Start Date]) ON
Lori2836_X1.[MinOfStart Date] = [Master Dispatch].[Start Date]
GROUP BY [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master
Dispatch].[MFG Order #], [Master Dispatch].[Order Status], [Master
Dispatch].[Order Qty], [Master Dispatch].[Balance Due], [Master
Dispatch].[Opern Description], [Master Dispatch].[Work Center], [Master
Dispatch].[Start Date], [Master Dispatch].[Due Date]
HAVING (((([Master Dispatch].[Opern Description]) Like "*face*" Or ([Master
Dispatch].[Opern Description]) Like "*machine*" Or ([Master Dispatch].[Opern
Description]) Like "*grumman*" Or ([Master Dispatch].[Opern Description])
Like "*turn*") And ([Master Dispatch].[Opern Description]) Not Like "*side2*"
And ([Master Dispatch].[Opern Description]) Not Like "*2nd*" And ([Master
Dispatch].[Opern Description]) Not Like "*finish grind*") AND (([Master
Dispatch].[Work Center])=" 18000"))
ORDER BY [Master Dispatch].[Item ID];

Lori2836_X1 ---
SELECT Min(Lori2836_X.[Start Date]) AS [MinOfStart Date]
FROM Lori2836_X;

Lori2836_X2 ---
SELECT Lori2836_X.*
FROM Lori2836_X1 INNER JOIN Lori2836_X ON Lori2836_X1.[MinOfStart Date] =
Lori2836_X.[Start Date];


--
KARL DEWEY
Build a little - Test a little


Lori2836 via AccessMonster.com said:
Good morning. I have one table where there may be many of the same part
numbers with different criteria. The requestor only wants to see the one
with the earliest start date. How would I set that up? Here is the SQL
for the existing query.

SELECT [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master Dispatch].
[MFG Order #], [Master Dispatch].[Order Status], [Master Dispatch].[Order Qty]
, [Master Dispatch].[Balance Due], [Master Dispatch].[Opern Description],
[Master Dispatch].[Work Center], [Master Dispatch].[Start Date], [Master
Dispatch].[Due Date]
FROM [Fiscal Dates] RIGHT JOIN [Master Dispatch] ON [Fiscal Dates].Date =
[Master Dispatch].[Start Date]
GROUP BY [Fiscal Dates].[Fiscal Year], [Fiscal Dates].[Fiscal Week], [Master
Dispatch].[Item ID], [Master Dispatch].[Item Description], [Master Dispatch].
[MFG Order #], [Master Dispatch].[Order Status], [Master Dispatch].[Order Qty]
, [Master Dispatch].[Balance Due], [Master Dispatch].[Opern Description],
[Master Dispatch].[Work Center], [Master Dispatch].[Start Date], [Master
Dispatch].[Due Date]
HAVING (((([Master Dispatch].[Opern Description]) Like "*face*" Or ([Master
Dispatch].[Opern Description]) Like "*machine*" Or ([Master Dispatch].[Opern
Description]) Like "*grumman*" Or ([Master Dispatch].[Opern Description])
Like "*turn*") And ([Master Dispatch].[Opern Description]) Not Like "*side
2*" And ([Master Dispatch].[Opern Description]) Not Like "*2nd*" And ([Master
Dispatch].[Opern Description]) Not Like "*finish grind*") AND (([Master
Dispatch].[Work Center])=" 18000"))
ORDER BY [Master Dispatch].[Item ID];


Thank you for any help you can give me!
Click to expand...
 
Back
Top