Re: display each record only once

Wow, now I feel stupid. Thanks, John and Marshall for helping me realize
that. I'll learn, I swear!

"John Spencer" wrote:

Group By is optional in the sense that it is NOT required if all the fields
being displayed use an aggregate function. Any fields that don't use an
aggregate function and are being displayed are required to be in the Group
By clause.

SELECT Max(Referrals.Date) AS {"LatestDate"],
TotalAgentListABCD.[Agent Active],
[Agent Coverage Area].[Agent Name],
[Agent Coverage Area].City,
TotalAgentListABCD.AgentName,
TotalAgentListABCD.[Home/Other Phone]
FROM (TotalAgentListABCD
INNER JOIN [Agent Coverage Area]
ON TotalAgentListABCD.AgentName=[Agent Coverage Area].[Agent Name])
INNER JOIN Referrals
ON TotalAgentListABCD.AgentName=Referrals.AN
WHERE (((TotalAgentListABCD.[Agent Active])=Yes)
AND (([Agent Coverage Area].City)=Forms!ACA!City))
GROUP BY TotalAgentListABCD.[Agent Active],
[Agent Coverage Area].[Agent Name],
[Agent Coverage Area].City,
TotalAgentListABCD.AgentName,
TotalAgentListABCD.[Home/Other Phone]


"Gnowor" <Gnowor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:699D14FB-D38A-41D7-BE5B-D933C45AD6F8@xxxxxxxxxxxxxxxx
I tried GROUP BY but I still got the same error. When reading the help
file
for access, it says GROUP BY is an optional clause. Any other
suggestions,
or am I up a creek, with very very very very small paddle? Thanks,
Marshall.

-eD

"Marshall Barton" wrote:

You forgot to include a GROUP BY clause.
--
Marsh
MVP [MS Access]


Gnowor wrote:

Here's the query as it stands now

SELECT Max(Referrals.Date) AS ["LatestDate"],
TotalAgentListABCD.[Agent Active],
[Agent Coverage Area].[Agent Name],
[Agent Coverage Area].City,
TotalAgentListABCD.AgentName,
TotalAgentListABCD.[Home/Other Phone]
FROM (TotalAgentListABCD
INNER JOIN [Agent Coverage Area]
ON TotalAgentListABCD.AgentName=[Agent Coverage Area].[Agent Name])
INNER JOIN Referrals
ON TotalAgentListABCD.AgentName=Referrals.AN
WHERE (((TotalAgentListABCD.[Agent Active])=Yes)
AND (([Agent Coverage Area].City)=Forms!ACA!City));

Here's how these tables relate to the ones in my original example

TotalAgentListABCD = AgentList
Referrals = SalesList
Agent Coverage Area = CityList

Agent Active is a filter to determine whether the agent should be
included
in the query
Forms!ACA!City is the prompt for the user to specify the city for the
report

Right now when I run the query it says I tried to execute a query that
does
not include the specified expression 'Agent Active' as part of an
aggregate
function. (Agent Active turns into whatever the first field in the list
is


"Marshall Barton" wrote:

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