Not knowing your data, can you do a selective edit of the query to see
what is causing the multiple records? By this, I mean delete one table
and
run the query; if you still get the duplicate records, start with the
original query again and delete a different table.
Once you can tell us which join is causing the duplication, then we can
design the SQL statement to eliminate the duplication.
As for the "1" idea, I think that is overcomplicating the setup. We
should
be able to get the results by just restructuring the query.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Thanks a bunch Ken! However, the problem is still the same. With the
tblGoals out of the Query, the Goal is still repeated multiple times
for
each
Rep. Maybe Access isn't able to do what I want to do. I can do it in
Excel,
but I was hoping to just do everything in Access rather than swapping
data
back and forth between Access and Excel.
Is there some way to count Rep's names, maybe in a helper Field,
incrementing by 1, and then restart with 1 each time a new Rep name is
encountered? I think I could do a Dlookup based on the 1s and when a
1
is
encountered pull in that individual's Goal. Would that work? I know
Excel
can do that.
Thanks!
Ryan---
--
RyGuy
:
Change your query to this:
SELECT
[Bookings and Pipe by Advertiser - RVP].Rep,
[Bookings and Pipe by Advertiser - RVP].RVP,
tblRep_Summary.Region,
[Bookings and Pipe by Advertiser - RVP].Advertiser,
Sum([Bookings and Pipe by Advertiser - RVP]![Sum 08Q4 C1]+[Bookings
and
Pipe by Advertiser - RVP]![Sum 08Q4 C2]) AS [Bookings by Advertiser],
DLookup("Total - Q408", "tblGoals", "Rep='" & [Rep] & "'") AS Goal
FROM
(tblRep_Summary INNER JOIN (tblRVP_Mapping INNER JOIN
[Bookings and Pipe by Advertiser - RVP] ON
tblRVP_Mapping.[Sales Rep] = [Bookings and Pipe by Advertiser -
RVP].Rep)
ON tblRep_Summary.AE = tblRVP_Mapping.[Backyard ID])
GROUP BY
[Bookings and Pipe by Advertiser - RVP].Rep,
[Bookings and Pipe by Advertiser - RVP].RVP,
tblRep_Summary.Region, [Bookings and Pipe by Advertiser -
RVP].Advertiser;
Note that I've removed the tblGoals table from the query. It's not
needed
for any of the fields or calculations, and it was causing the
duplication of
the data that you were seeing.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
You are absolutely right Ken! The Dlookup is doing what I want!
However,
I
am still plagued with many, many, many duplicates for Goals. Let
me
explain
once more what I want to do and maybe you, or someone, can tell me
how
best
to do it.
I have a Sales Rep, a VP, a Region, an Advertiser, and then a Total
spent
by
that Advertiser. This part of the query is working just fine. The
problem
occurs when I use Dlookup to pull in the Goals for each Sales Rep.
I
need
the line by line itemized format for everything but the Goals. For
instance,
when I look at Adam, I want to see all 13 of his Advertisers and
the
money
that each Advertiser spent. For Alex, I want to see all 35 of his
Advertisers and the money that each Advertiser spent, and so on and
so
forth.
I was hoping to come up with a way of pulling in the Goal for each
Rep
and
then exporting everything to Excel, do a Subtotal by Rep, and send
this,
via
Email, to a colleague. The thing that I'm stuck on is that I'm
getting a
$3,000,000 Goal for Adam repeated 13 times. I wanted to sum the
dollar
amounts spent by the Advertisers that Adam works with, and compare
this
sum
to his Goal. This is why I want to display the Goal only once.
Perhaps
there is a better way of doing this?
BTW, the Dlookup function is very cool!! I've never used it
before,
but I'm
sure I'll find opportunities to use it more and more in the very
near
future.
Thanks,
Ryan---
--
RyGuy
:
The issue is not the DLookup. It returns only a single value for
each
record
in the query. The problem is that your query itself is returning
the
multiple records. You can confirm this by taking the DLookup field
out of
the query and running it.
With the DLookup field, can you not take the tblGoals table out of
the
query? I assume that that is the source of the duplication of
records.
The
DLookup field does what you were wanting from the tblGoals table
initially.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Evidently, I am still doing something wrong. I have this in the
'Field'
of
the QBE Grid:
Goals: DLookUp("[Total - Q408]","tblGoals","Rep = '" & [Bookings
and
Pipe
by
Advertiser - RVP]![Rep] & "'")
The result is 13 incidences of $3,000,000 for Goals for Adam, I
surmise
it
is because he has 13 Advertisers and I am grouping by Advertiser
(as
well
as
grouping by a couple of other Fields). Next, I have 35
incidences
of
$8,000,000 for Goals for Alex, and again, I have 35 Advertisers
for
Alex.
I
thought I could just show $3,000,000 one time for Adam and show
$8,000,000
one time for Alex. However, the $3,000,000 is repeated 13 times
and
$8,000,000 is repeated 35 times! How can I display the Goal
only
one
time
per Rep? I thought the Dlookup would return the first match
(kind
of
like
a
Vlookup in Excel). There doesn't seem to be any advantage to
using
the
Dlookup, over say, dragging and dropping my Field 'Total - Q408'
into
the
QBE
Grid. Please help!!
Thanks so much,
Ryan---
--
RyGuy
:
No problem... just give the full reference to that field --
assuming
that
it's the [Bookings and Pipe by Advertiser - RVP].Rep field:
SELECT [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings
and
Pipe
by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and
Pipe
by
Advertiser - RVP].Advertiser, Sum([Bookings and Pipe by
Advertiser -
RVP]![Sum 08Q4 C1]+[Bookings and Pipe by Advertiser - RVP]![Sum
08Q4
C2])
AS
[Bookings by Advertiser], DLookup("Total - Q408", "tblGoals",
"Rep='"
&
[Bookings and Pipe by Advertiser - RVP].Rep & "'") AS Goal
FROM (tblRep_Summary INNER JOIN (tblRVP_Mapping INNER JOIN
[Bookings
and
Pipe by Advertiser - RVP] ON tblRVP_Mapping.[Sales Rep] =
[Bookings
and
Pipe
by Advertiser - RVP].Rep) ON tblRep_Summary.AE =
tblRVP_Mapping.[Backyard
ID]) INNER JOIN tblGoals ON tblRVP_Mapping.[Backyard ID] =
tblGoals.ID
GROUP BY [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings
and
Pipe
by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and
Pipe
by
Advertiser - RVP].Advertiser;
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
message
Thanks Ken!! It looks like it should work but...
I received this message:
The specified field '[Rep]' could refer to more than one
table
listed
in
the
FROM clause of your SQL statement.
SQL is below:
SELECT [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings
and
Pipe
by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and
Pipe
by
Advertiser - RVP].Advertiser, Sum([Bookings and Pipe by
Advertiser -
RVP]![Sum 08Q4 C1]+[Bookings and Pipe by Advertiser -
RVP]![Sum
08Q4
C2])
AS
[Bookings by Advertiser], DLookup("Total - Q408", "tblGoals",
"Rep='" &
[Rep] & "'") AS Goal
