display each record only once

G

Guest

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

Hope I explained this well enough. Thanks for the help.

-eD
 
M

Marshall Barton

Gnowor said:
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
Click to expand...


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.
 
G

Guest

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

Suggestions? Thanks!

-eD
 
M

Marshall Barton

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

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
Click to expand...
 
G

Guest

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 said:
You forgot to include a GROUP BY clause.
--
Marsh
MVP [MS Access]

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
Click to expand...
Click to expand...
 
J

John Spencer

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 said:
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 said:
You forgot to include a GROUP BY clause.
--
Marsh
MVP [MS Access]

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
Click to expand...
:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

John Spencer said:
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 said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

So I tried posting this separately but wasn't getting any responses, so I'm
hoping one of you helpful people is still watching this post.

---------------------------------

So I have a table that has information (phone numbers, emails, etc.) for a
bunch of sales agents. I have a form based on this table. I also have a
table that has the dates of sales for each agent, that appears as a subform
on the main info form.

My problem is that some of these agents are members of a team, and when I
add a date of a sale to one member of the team, I want it to add that date to
all members of the team.

The subform right now has an unbound control that pulls up the dates of
sales, and if you type in new info into that unbound control, it adds a
record for that agent, with the sales date you entered.

I was thinking of using afterupdate to add the records to all members of the
team. I have the if statement, but then i draw a blank as to the code that
would go through, find each member of that team and add the record for them.
the if statement would go like:

if Forms!InfoForm!team <> ""

Do I need to build a query that takes Forms!InfoForm!team as a parameter to
find all members of that team? Once I have that, how to I add the record for
each agent that the query returns?

Advice greatly appreciated! Thanks!

-eD

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

John Spencer said:
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 said:
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

:

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to sort data from rows into columns? 6
Query to find the most recent record for each duplicate record 3
vlookup using match and date range 1
Copy to another worksheet IF 3
On-Call schedule 3
Rows To Columns -Transpose or Pivot or? 4
Calculating a Total for each record source in a Report 1
Designing a report where matching and unmatching records appear together 1

Top