Re: Union Query with a Crosstab Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Looks like I used the wrong field in the subquery -- the
tblRVP_Mapping.[Sales Rep] field is not in the SELECT clause. Try this for
the query:

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],
IIf((SELECT Min(T.RVP) AS MinRVP FROM [Bookings and Pipe by Advertiser -
RVP] AS T
WHERE T.Rep = [Bookings and Pipe by Advertiser - RVP].[Rep]) =
[Bookings and Pipe by Advertiser - RVP].RVP,
DLookup("Total - Q408", "tblGoals", "Rep='" & [Bookings and Pipe by
Advertiser - RVP].Rep & "'"), NULL) 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;


The use of the T is to give the subquery's table an alias so that Jet/ACCESS
are not confused by which instance of the [Bookings and Pipe by Advertiser -
RVP] table is to be used in the subquery as the FROM table and in the WHERE
clause.


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A35B4226-4708-4D81-8EF4-52CD1FA8AEE1@xxxxxxxxxxxxxxxx
Thanks once again Ken! When I attempt to run both (new) queries, I now
get
prompted to enter a parameter. Message Reads: Enter Parameter Value:
tblRVP_Mapping.Sales Rep

When I click 'OK' nothing is returned.

I fiddled around with the IIF function for a bout ½ hour and couldn't
figure
it out. I've seen the T. method used before, but I am not familiar with
it
at all. Perhaps that is the culprit. Also, why would Access need T.RVP?
Wouldn't it be T.Rep? The Reps are under the RVPs, that's for sure, but I
am
querying for Rep and Advertiser revenue; I'm just pulling in the RVP names
to
see the hierarchy of the teams.

I'll keep playing with it. I hope I can make this work. It has been a
great learning opportunity and if I can get this thing working it will be
very practical and very useful. Any idea what could be causing the
parameter
to jump up?

Thanks once again,
Ryan---


--
RyGuy


"Ken Snell (MVP)" wrote:

OK, then let's try this query.

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],
IIf((SELECT Min(T.RVP) AS MinRVP FROM [Bookings and Pipe by Advertiser -
RVP] AS T
WHERE T.Rep = tblRVP_Mapping.[Sales Rep]) = [Bookings and Pipe by
Advertiser - RVP].RVP,
DLookup("Total - Q408", "tblGoals", "Rep='" & [Bookings and Pipe by
Advertiser - RVP].Rep & "'"), NULL) 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;


The above query should show the Goals amount for just one of the records
for
each SalesRep, though it may not be the first record in the query's order
for that SalesRep. If you want that, you can change the 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],
IIf((SELECT Min(T.RVP) AS MinRVP FROM [Bookings and Pipe by Advertiser -
RVP] AS T
WHERE T.Rep = tblRVP_Mapping.[Sales Rep]) = [Bookings and Pipe by
Advertiser - RVP].RVP,
DLookup("Total - Q408", "tblGoals", "Rep='" & [Bookings and Pipe by
Advertiser - RVP].Rep & "'"), NULL) 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
ORDER BY [Bookings and Pipe by Advertiser - RVP].Rep,
[Bookings and Pipe by Advertiser - RVP].RVP;
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CB197054-9307-4DAD-94C8-99DF146D4CFB@xxxxxxxxxxxxxxxx
Yes, yes, yes! I need some duplicate records in the query, but just
want
the
Goals amount to be displayed for a single record for a Rep. I have a
rep
named Adam and he has 13 Advertisers. I am showing the revenue from
each
Advertiser, which is 13 records, and I want to show Adam's Goal, which
is
one
record. This is one of two select Queries. I then combine the results
in
a
UnionQuery and finally rearrange it in a CrosstabQuery. The UnionQuery
and
CrosstabQuery work great without the Goals in there. Howver, I need
the
Goals in there so I can compare the sum of the revenues from all
Advertisers
with the Goal. This is how I can tell if a rep is 70% to Goal, 90% to
Goal,
100% to Goal, or whatever.20% to Goal. Does that make sense?

I'm trying to learn how to do this in Access. I can do it in Excel
pretty
easy, but I'd like to learn the nuts and bolts of Access and it would
be a
whole lot easier to keep this all in Access, and simply convert the
final
Query to a spreadsheet, after all work is done in Access.

Thanks for the follow through Ken!!


--
RyGuy


"Ken Snell (MVP)" wrote:

Or, perhaps I'm completely misunderstanding what you want. Do you want
those
duplicate records in the query, but just want the Goals amount to be
displayed for a single record for a Rep and not for all the records
for
that
Rep?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:uVNyXQJbJHA.552@xxxxxxxxxxxxxxxxxxxxxxx
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/


"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C453B74D-7BF2-4B7A-B6F3-9E24C14D068C@xxxxxxxxxxxxxxxx
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


"Ken Snell (MVP)" wrote:

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/


"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C97422F-00D4-4934-826F-E0DF59BF8F9E@xxxxxxxxxxxxxxxx
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


"Ken Snell (MVP)" wrote:

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/




"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:362171C4-9DF2-4D9D-BE29-4D2A5AE8BA67@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Union Query with a Crosstab Query
    ... then let's try this query. ... [Bookings and Pipe by Advertiser - RVP].Rep, ...
    (microsoft.public.access.queries)
  • Re: Union Query with a Crosstab Query
    ... The IIf function that I had in the suggested query was intended to do this: ... querying for Rep and Advertiser revenue; I'm just pulling in the RVP ... [Bookings and Pipe by Advertiser - RVP].Rep, ...
    (microsoft.public.access.queries)
  • Re: Union Query with a Crosstab Query
    ... [Bookings and Pipe by Advertiser - RVP].Rep, ... Goals amount to be displayed for a single record for a Rep. ...
    (microsoft.public.access.queries)
  • Re: Union Query with a Crosstab Query
    ... blanks, or nulls, in the Goals Field, for the final SQL-string that you ... [Bookings and Pipe by Advertiser - RVP].Rep, ...
    (microsoft.public.access.queries)
  • Re: Union Query with a Crosstab Query
    ... can you do a selective edit of the query to see what ... [Bookings and Pipe by Advertiser - RVP].Rep, ... occurs when I use Dlookup to pull in the Goals for each Sales Rep. ...
    (microsoft.public.access.queries)