Query Help

[Sean]

I have a query that pulls 3 fields from a table, field1 is a date/time stamp,
field 2 is a part number, and field 3 is the order number. Let say I have
part# Y12345 and there is a quantity of 5 on the order. This part number
will show 5 times in the table beauce each quantity of 1 will have a
different time stamp. I want the partnumber to show 1 time in my query, not
5 times, and I still need the time stamp. Is there a way to limit the query
to pull just the 1st part number? Distinct would work if there wasn't a time
stamp, nut I need the time in this query.

Thanks,

 

[John W. Vinson]

I have a query that pulls 3 fields from a table, field1 is a date/time stamp,
field 2 is a part number, and field 3 is the order number. Let say I have
part# Y12345 and there is a quantity of 5 on the order. This part number
will show 5 times in the table beauce each quantity of 1 will have a
different time stamp. I want the partnumber to show 1 time in my query, not
5 times, and I still need the time stamp. Is there a way to limit the query
to pull just the 1st part number? Distinct would work if there wasn't a time
stamp, nut I need the time in this query.

Thanks,

You need "the time" - but there are five times.

Which of the five is "the time" that you need? It sounds like you have a
contradiction.

You can show the earliest (or latest) time for a given part/order combination
by using a Totals query, grouping by part number and order number and
selecting Min (or Max) on the timestamp. This query will not be updateable
(but you cannot have both one record displayed out of five *and* have the
query be updateable).

John W. Vinson [MVP]

 

[John Spencer]

To show the PartNumber, OrderNumber of the earliest time field you can use.
This query will allow you to have a record to update, but it will be slow
with most datasets.

SELECT TimeField, PartNumber, OrderNumber
FROM SomeTable
WHERE TimeField in
(SELECT Top 1 TimeField
FROM SomeTable as Tmp
WHERE Tmp.PartNumber = SomeTable.PartNumber
AND Tmp.OrderNumber = SomeTable.OrderNumber
ORDER BY TimeField)

If you want to show the part number the earliest time and the total quantity
you could use an aggregate query - see John Vinson's answer
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..