querying unique values

  • Thread starter Thread starter Dave Cullen
  • Start date Start date
D

Dave Cullen

I have a table of product shipments and the boss wants to know how many
different product part numbers were shipped since June 1st.

PART_NO is text, SHIP_DATE is time/date data types.

If I make a single column query with only PART_NO, and select
"UniqueValues" in the properties, it'll show me one occurrance of each
number. Good. But when I add another column (the date), I get multiple
records for each part number because Access wants to include the date as
part of the unique qualifier.

How do I specify that only the PART_NO column is used to determine
uniquness?

Thanks
 
Try the SQL String something like:

SELECT DISTINCT S.PART_NO
FROM tblShipment AS S
WHERE S.SHIP_DATE >= #06/01/2003#

HTH
Van T. Dinh
MVP (Access)
 
Dear Dave:

Think about it. If you want to show each part number only once, and
there are two different SHIP_DATEs for a part number, which date is it
supposed to show? You haven't specified any way for it to do that.

If you want to show only the most recent date, for example, when there
is more than one date, then an aggregate is useful:

SELECT PART_NO, MAX(SHIP_DATE) AS MostRecentShipDate
FROM YourTable
GROUP BY PART_NO

In this way you will get each part number only once, with the most
recent ship date. Perhaps this is close to what you want.

I have a table of product shipments and the boss wants to know how many
different product part numbers were shipped since June 1st.

PART_NO is text, SHIP_DATE is time/date data types.

If I make a single column query with only PART_NO, and select
"UniqueValues" in the properties, it'll show me one occurrance of each
number. Good. But when I add another column (the date), I get multiple
records for each part number because Access wants to include the date as
part of the unique qualifier.

How do I specify that only the PART_NO column is used to determine
uniquness?

Thanks
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top