Make query showing first,last,comp,fax but eliminate duplicate fax

[pcmike]

I have a access database that is essentially a contacts list (meaning it has
first name, last name, company, fax, etc). I'm trying to generate a query
(that I can subsequently use in reports) that will give me firstname,
lastname, companyname, and faxnumber (where faxnumber is not duplicated). The
problem is some contacts are in the same office and therefore share the same
faxnumber. So I end up with 6 lines where all the contact information is
unique EXCEPT the faxnumber. I don't want this to occur, I want the query to
only output the first record that happens to share the same faxnumber. So say
there were 6 contacts that all share the same faxnumber.. I only want to see
the first record that has that faxnumber. Is there a way to do this easily? I
tried using "Totals" and grouping by "first" and all that stuff, but it
continues to list all records.

Thanks for any help you can provide!

 

[vanderghast]

SELECT faxNumber, LAST(firstName), LAST(lastName), LAST(companyName)
FROM table
GROUP BY faxNumber



Note that since records are free to float around, in their storage (table),
there is no really 'fírst' one, unless you specify an order, or unless you
built a recordset (which is much MORE than the storage from which the
recordset is built). So, I used LAST (instead of FIRST) to explicilty made
my point, but in the end, that means the result will take ONE of the record
that has the said faxNumber.



Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
SELECT [YourContactTable].Fax, First([YourContactTable].[company]) AS
Company_Name}, First([YourContactTable].[last name]) AS Last_Name,
First([YourContactTable].[first name]) AS First_Name
FROM [YourContactTable]
GROUP BY [YourContactTable].Fax;