Re: display each record only once

Tech-Archive recommends: Fix windows errors by optimizing your registry

Gnowor wrote:

Got a report that spits out the names and info for a bunch of sales agents
that cover a certain city (which is selected by the user via a pop up form)

Problem is, I want to sort that list of agents based out when they last
recieved a sale, without seeing duplicate info for each agent.

Really working with 3 tables.

Table 1: AgentList - Contains Agent Info and Unique agent name
Table 2: CityList - Has field linked to agent name in AgentList, and 1
record for each city they cover. This works fine.
Table 3: SalesList - Has field linked to agent name in AgentList, and 1
record for the date of each sale they completed (i.e. John Smith - 05/2006,
John Smith - 04/2006, Jane Smith - 03/2006, John Doe - 04/2006, John Doe -
03/2006)

Let's say that all those agents I just listed cover New York. I want it to
spit them out in the order
Jane Smith - 03/2006
John Doe - 04/2006
John Smith - 05/2006
based on their most recent sale, and I don't want to see duplicate entries
(i.e. for John Smith - 04/2006)

Right now, I'm getting the duplicate info, like
John Smith - 05/2006
John Smith - 04/2006
John Doe - 04/2006
Jane Smith - 03/2006
John Doe - 03/2006


I think you want to use a query like:

SELECT AgentList.AgenName,
CityList.CityName,
Max(SalesList.SaleDate) As LatestSale
FROM (AgentList LEFT JOIN CityList
ON AgentList.AgenName = CityList.AgenName)
LEFT JOIN SalesList
ON AgentList.AgenName =SalesList.AgenName
GROUP BY AgentList.AgenName, CityList.CityName

Depends on your detailed needs, you might want to use INNER
JOIN instead.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: display each record only once
    ... INNER JOIN [Agent Coverage Area] ... Right now when I run the query it says I tried to execute a query that does ... John Doe - 04/2006 ... John Smith - 05/2006 ...
    (microsoft.public.access.reports)
  • Re: display each record only once
    ... bunch of sales agents. ... record for that agent, with the sales date you entered. ... INNER JOIN ... record for the date of each sale they completed (i.e. John Smith - ...
    (microsoft.public.access.reports)
  • Re: display each record only once
    ... INNER JOIN [Agent Coverage Area] ... record for the date of each sale they completed (i.e. John Smith - ...
    (microsoft.public.access.reports)
  • Re: display each record only once
    ... John and Marshall for helping me realize ... INNER JOIN [Agent Coverage Area] ... record for the date of each sale they completed (i.e. John Smith - ...
    (microsoft.public.access.reports)
  • extracting records to an associative array
    ... Name: John Doe ... City: New York ... Name: Jane Doe ... Name: John Smith ...
    (comp.lang.perl.misc)