Select DISTINCT...won't select DISTINCT

R

ryguy7272

Below is my SQL. Access sums all records, not just DISTINCT records and I
can't figure out why...

SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name], Sum([Display Entity Mapping].[2006Q1
Rev-Book]) AS [SumOf2006Q1 Rev-Book], Sum([Display Entity Mapping].[2006Q2
Rev-Book]) AS [SumOf2006Q2 Rev-Book], Sum([Display Entity Mapping].[2006Q3
Rev-Book]) AS [SumOf2006Q3 Rev-Book], Sum([Display Entity Mapping].[2006Q4
Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM [Display Entity Mapping]
GROUP BY [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name];

Any help or assistance would be greatly appreciated.


Thanks!!
Ryan---
 
J

Jerry Whittle

First gather up just the distinct records then sum them up. Something like
this:

SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];
 
R

ryguy7272

It worked with data from 2006 only! However, when I tried to add some more
fields it didn't work...
SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum( [2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book],
Sum( [2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-Book],
Sum( [2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-Book],
Sum( [2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-Book],
Sum( [2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q3 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book],
[Display Entity Mapping].[2007Q1 Rev-Book],
[Display Entity Mapping].[2007Q2 Rev-Book],
[Display Entity Mapping].[2007Q3 Rev-Book],
[Display Entity Mapping].[2007Q4 Rev-Book],
[Display Entity Mapping].[2008Q1 Rev-Book],
[Display Entity Mapping].[2008Q2 Rev-Book],
[Display Entity Mapping].[2008Q3 Rev-Book],
[Display Entity Mapping].[2008Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];

When I executed the SQL it summed incorrectly, just like it did initially.
Somehow it still doesn't sum DISTINCT records with 2007 fields and 2008
fields in there.

Any ideas about this?

Thanks,
Ryan---

--
RyGuy


Jerry Whittle said:
First gather up just the distinct records then sum them up. Something like
this:

SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ryguy7272 said:
Below is my SQL. Access sums all records, not just DISTINCT records and I
can't figure out why...

SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name], Sum([Display Entity Mapping].[2006Q1
Rev-Book]) AS [SumOf2006Q1 Rev-Book], Sum([Display Entity Mapping].[2006Q2
Rev-Book]) AS [SumOf2006Q2 Rev-Book], Sum([Display Entity Mapping].[2006Q3
Rev-Book]) AS [SumOf2006Q3 Rev-Book], Sum([Display Entity Mapping].[2006Q4
Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM [Display Entity Mapping]
GROUP BY [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name];

Any help or assistance would be greatly appreciated.


Thanks!!
Ryan---
Click to expand...
Click to expand...
 
R

ryguy7272

I think it should be something like this, but it doesn't work...

SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name],
Sum([2006 Display Rev Book].[2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum([2006 Display Rev Book].[2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum([2006 Display Rev Book].[2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum([2006 Display Rev Book].[2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum([2007 Display Rev Book].[2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book],
Sum([2007 Display Rev Book].[2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-Book],
Sum([2007 Display Rev Book].[2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-Book],
Sum([2007 Display Rev Book].[2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-Book],
Sum([2008 H1 Display Rev Book].[2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-Book],
Sum([2008 H1 Display Rev Book].[2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q3 Rev-Book]) AS [SumOf2008Q3
Rev-Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q4 Rev-Book]) AS [SumOf2008Q4
Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2006Q1 Rev-Book],
[Display Entity Mappings].[2006Q2 Rev-Book],
[Display Entity Mappings].[2006Q3 Rev-Book],
[Display Entity Mappings].[2006Q4 Rev-Book],
[Display Entity Mappings].[2007Q1 Rev-Book],
[Display Entity Mappings].[2007Q2 Rev-Book],
[Display Entity Mappings].[2007Q3 Rev-Book],
[Display Entity Mappings].[2007Q4 Rev-Book],
[Display Entity Mappings].[2008Q1 Rev-Book],
[Display Entity Mappings].[2008Q2 Rev-Book],
[Display Entity Mappings].[2008Q3 Rev-Book],
[Display Entity Mappings].[2008Q4 Rev-Book]
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name];

Jerry, or anyone, can you see what I am doing wrong?

Thanks,
Ryan---

--
RyGuy


ryguy7272 said:
It worked with data from 2006 only! However, when I tried to add some more
fields it didn't work...
SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum( [2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book],
Sum( [2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-Book],
Sum( [2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-Book],
Sum( [2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-Book],
Sum( [2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q3 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book],
[Display Entity Mapping].[2007Q1 Rev-Book],
[Display Entity Mapping].[2007Q2 Rev-Book],
[Display Entity Mapping].[2007Q3 Rev-Book],
[Display Entity Mapping].[2007Q4 Rev-Book],
[Display Entity Mapping].[2008Q1 Rev-Book],
[Display Entity Mapping].[2008Q2 Rev-Book],
[Display Entity Mapping].[2008Q3 Rev-Book],
[Display Entity Mapping].[2008Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];

When I executed the SQL it summed incorrectly, just like it did initially.
Somehow it still doesn't sum DISTINCT records with 2007 fields and 2008
fields in there.

Any ideas about this?

Thanks,
Ryan---

--
RyGuy


Jerry Whittle said:
First gather up just the distinct records then sum them up. Something like
this:

SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ryguy7272 said:
Below is my SQL. Access sums all records, not just DISTINCT records and I
can't figure out why...

SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name], Sum([Display Entity Mapping].[2006Q1
Rev-Book]) AS [SumOf2006Q1 Rev-Book], Sum([Display Entity Mapping].[2006Q2
Rev-Book]) AS [SumOf2006Q2 Rev-Book], Sum([Display Entity Mapping].[2006Q3
Rev-Book]) AS [SumOf2006Q3 Rev-Book], Sum([Display Entity Mapping].[2006Q4
Rev-Book]) AS [SumOf2006Q4 Rev-Book]
FROM [Display Entity Mapping]
GROUP BY [Display Entity Mapping].[Ad Entity ID], [Display Entity
Mapping].[Advertising Entity Name];

Any help or assistance would be greatly appreciated.


Thanks!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
L

Lord Kelvan

your table structure is shot making querying hard having a different
column for each year and q you should have three columns

rev-book
q-rating
theyear

and that would make querying so much more powerful and eaiser.

it aint working becuase of this

2008 H2 Display Rev Book 0915
2008 H1 Display Rev Book

where did this come from

your table structure is going to make your query not work in a usefull
way

though regardless to quote john

"doctor is am sick what should i take"

you say you have a problem but what is the problem what error is it
displaying or what is it not doing

and that is for

SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum( [2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book],
Sum( [2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-Book],
Sum( [2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-Book],
Sum( [2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-Book],
Sum( [2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q3 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book],
[Display Entity Mapping].[2007Q1 Rev-Book],
[Display Entity Mapping].[2007Q2 Rev-Book],
[Display Entity Mapping].[2007Q3 Rev-Book],
[Display Entity Mapping].[2007Q4 Rev-Book],
[Display Entity Mapping].[2008Q1 Rev-Book],
[Display Entity Mapping].[2008Q2 Rev-Book],
[Display Entity Mapping].[2008Q3 Rev-Book],
[Display Entity Mapping].[2008Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];

that query not the otherone because that query just looks rubish

Regards
Kelvan
 
R

ryguy7272

I thought I was starting to get the hang of SQL, at least a little, but this
current issue is proving me wrong. When I use Jerry's code as is, I can get
the right values for all 2006 quarters. As soon as I add in Q1 for 2007, the
values get really bizarre... All values for 2006 are increasing 200%, and
Q1 of 2007 is increasing exactly 600%

Below is my SQL with 2006 and Q1 of 2007:
SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum( [2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book],
[Display Entity Mapping].[2007Q1 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];

Long story short, this is still not working. Does anyone have any ideas
why? Also, why is this so hard? I thought a simple 'Select DISTINCT' right
at the beginning of the SQL syntax would take care of things, but Access
seems to do these random things. I must be missing something fundamental
here. It can't be this hard... There must be a way of just summing the
values and grouping the results, right.

TIA,
Ryan---

--
RyGuy


Lord Kelvan said:
your table structure is shot making querying hard having a different
column for each year and q you should have three columns

rev-book
q-rating
theyear

and that would make querying so much more powerful and eaiser.

it aint working becuase of this

2008 H2 Display Rev Book 0915
2008 H1 Display Rev Book

where did this come from

your table structure is going to make your query not work in a usefull
way

though regardless to quote john

"doctor is am sick what should i take"

you say you have a problem but what is the problem what error is it
displaying or what is it not doing

and that is for

SELECT [Ad Entity ID],
[Advertising Entity Name],
Sum( [2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-Book],
Sum( [2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-Book],
Sum( [2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-Book],
Sum( [2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-Book],
Sum( [2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-Book],
Sum( [2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-Book],
Sum( [2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-Book],
Sum( [2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-Book],
Sum( [2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q3 Rev-Book],
Sum( [2008Q2 Rev-Book]) AS [SumOf2008Q4 Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mapping].[Advertising Entity Name],
[Display Entity Mapping].[2006Q1 Rev-Book],
[Display Entity Mapping].[2006Q2 Rev-Book],
[Display Entity Mapping].[2006Q3 Rev-Book],
[Display Entity Mapping].[2006Q4 Rev-Book],
[Display Entity Mapping].[2007Q1 Rev-Book],
[Display Entity Mapping].[2007Q2 Rev-Book],
[Display Entity Mapping].[2007Q3 Rev-Book],
[Display Entity Mapping].[2007Q4 Rev-Book],
[Display Entity Mapping].[2008Q1 Rev-Book],
[Display Entity Mapping].[2008Q2 Rev-Book],
[Display Entity Mapping].[2008Q3 Rev-Book],
[Display Entity Mapping].[2008Q4 Rev-Book]
FROM [Display Entity Mapping])
GROUP BY [Ad Entity ID],
[Advertising Entity Name];

that query not the otherone because that query just looks rubish

Regards
Kelvan
Click to expand...
 
B

Bob Barrows [MVP]

ryguy7272 said:
I thought I was starting to get the hang of SQL, at least a little,
but this current issue is proving me wrong. When I use Jerry's code
as is, I can get the right values for all 2006 quarters. As soon as
I add in Q1 for 2007, the values get really bizarre... All values
for 2006 are increasing 200%, and Q1 of 2007 is increasing exactly
600%
snip

Long story short, this is still not working. Does anyone have any
ideas why? Also, why is this so hard? I thought a simple 'Select
DISTINCT' right at the beginning of the SQL syntax would take care of
things, but Access seems to do these random things. I must be
missing something fundamental here.
Click to expand...

It does sound as if you are missing what DISTINCT does .. the fact that you
used both DISTINCT and Group By in your original query seem to highlight
that. These two rows are distinct:
a b c
a b d

These two rows are not distinct:
a b c
a b c

You see? DISTINCT guarantees distinct _rows_, not distinct fields.
It can't be this hard... There
must be a way of just summing the values and grouping the results,
right.

TIA,
Ryan---
Click to expand...

Can you show us a few rows of sample data that exhibit the problem? Just
include enough fields to cause the problem. Show us what your query returns
from the sample data and what you want it to return.
 
R

ryguy7272

Thanks for the look Bon. Basically, I am trying to sum revenues by
Advertiser ID (field) and this is linked to the Advertiser Name (field).
Let’s say that the advertiser is Gap, and several different sales people
worked on the account for Q1, 2006. Vin had $272,951 in revenue, Chris has
$0, Tony had $0, Laura had $0, Frank had $0 and Geoff had $18,416. The total
for Q1, 2006 should be $291,367. Access gives the correct results when I
only have two tables in the query; the Mappings Table (with Advertiser ID and
Advertiser Name) and the 2006 Revenue Table. However, when I add in another
table, such as 2007 Revenue, things get pretty wacky. I think Access is
creating a Cartesian product because the numbers get pretty huge. How can I
prevent Access from creating a Cartesian product? Another table, perhaps?

Below is the current SQL:
SELECT DISTINCT [Display Entity Mappings].[Advertiser ID], [Display Entity
Mappings].Advertiser, Sum([2006 Display Rev Book].[2006Q1 Rev-Book]) AS
[SumOf2006Q1 Rev-Book], Sum([2006 Display Rev Book].[2006Q2 Rev-Book]) AS
[SumOf2006Q2 Rev-Book], Sum([2006 Display Rev Book].[2006Q3 Rev-Book]) AS
[SumOf2006Q3 Rev-Book], Sum([2006 Display Rev Book].[2006Q4 Rev-Book]) AS
[SumOf2006Q4 Rev-Book], Sum([2007 Display Rev Book].[2007Q1 Rev-Book]) AS
[SumOf2007Q1 Rev-Book]
FROM ([Display Entity Mappings] INNER JOIN [2006 Display Rev Book] ON
[Display Entity Mappings].[Advertiser ID] = [2006 Display Rev
Book].[Advertiser ID]) INNER JOIN [2007 Display Rev Book] ON [Display Entity
Mappings].[Advertiser ID] = [2007 Display Rev Book].[Advertiser ID]
GROUP BY [Display Entity Mappings].[Advertiser ID], [Display Entity
Mappings].Advertiser
ORDER BY [Display Entity Mappings].Advertiser;

Thanks for all the help,
Ryan---
 
J

John W. Vinson

How can I
prevent Access from creating a Cartesian product? Another table, perhaps?
Click to expand...

By correctly normalizing your currently very spreadsheetish, incorrect table
structure, and joining the tables on some appropriate field (which I cannot
identify given your posted information).
 
L

Lord Kelvan

no nononononono

you problem is a cartesian product if the valeus are getting out of
wack it has nothing to do with distinct -_-

i am glad you know what a cartesian product is and i am even more glad
you actually noticed it.

basically it soulds as i stated before your entire table structure is
out of wack

what you need to do is a union or something becuase you are creating
multiple tables so the different years

and as a simple note this is bad you dont create more tables you have
1!!!!! table and just add a field for the date to note which record
belonds to which year.

do a query for 2006 then use union to connect it to 2007 then to 2008

SELECT [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name],
Sum([2006 Display Rev Book].[2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-
Book],
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2006Q1 Rev-Book],
[Display Entity Mappings].[2006Q2 Rev-Book],
[Display Entity Mappings].[2006Q3 Rev-Book],
[Display Entity Mappings].[2006Q4 Rev-Book],
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name]
UNION ALL
SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID], [Display
Entity
Mappings].[Advertising Entity Name],
Sum([2007 Display Rev Book].[2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-
Book],
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2007Q1 Rev-Book],
[Display Entity Mappings].[2007Q2 Rev-Book],
[Display Entity Mappings].[2007Q3 Rev-Book],
[Display Entity Mappings].[2007Q4 Rev-Book],
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name]
UNION ALL
SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID], [Display
Entity
Mappings].[Advertising Entity Name],
Sum([2008 H1 Display Rev Book].[2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-
Book],
Sum([2008 H1 Display Rev Book].[2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-
Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q3 Rev-Book]) AS
[SumOf2008Q3
Rev-Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q4 Rev-Book]) AS
[SumOf2008Q4
Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2008Q1 Rev-Book],
[Display Entity Mappings].[2008Q2 Rev-Book],
[Display Entity Mappings].[2008Q3 Rev-Book],
[Display Entity Mappings].[2008Q4 Rev-Book]
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name];


that query MAY and i say MAY do it but its just a guess have a look at
a chagne of business practice to stop making tables. when you build a
database and after it is built you shoudl NEVER have to create any
more tables or fields if you did it properly. so the fact you add
more tables for each year states you have done it wrong.

hope this helps

Regards
Kelvan
 
R

ryguy7272

Sorry for misspelling your name Bob. So, I finally got the query working.
In my defense, I inherited this from someone else who set it up in a way that
I wasn't used to seeing (kind of a weak defense, I know). Anyway, I created
three select queries, and then joined all in a Union Query:

SELECT [Advertiser], [Q1], [Q2], [Q3], [Q4], "2006"
FROM 2006
UNION ALL SELECT [Advertiser], [07Q1], [07Q2], [07Q3], [07Q4], "2007"
FROM 2007
UNION ALL SELECT [Advertiser], [08Q1], [08Q2], [08Q3], [08Q4], "2008"
FROM 2008;


Thanks for the idea Lord Kelvan! Thanks John, Bob, and Lord Kelvan!!!

Regards,
Ryan---

--
RyGuy


Lord Kelvan said:
no nononononono

you problem is a cartesian product if the valeus are getting out of
wack it has nothing to do with distinct -_-

i am glad you know what a cartesian product is and i am even more glad
you actually noticed it.

basically it soulds as i stated before your entire table structure is
out of wack

what you need to do is a union or something becuase you are creating
multiple tables so the different years

and as a simple note this is bad you dont create more tables you have
1!!!!! table and just add a field for the date to note which record
belonds to which year.

do a query for 2006 then use union to connect it to 2007 then to 2008

SELECT [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name],
Sum([2006 Display Rev Book].[2006Q1 Rev-Book]) AS [SumOf2006Q1 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q2 Rev-Book]) AS [SumOf2006Q2 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q3 Rev-Book]) AS [SumOf2006Q3 Rev-
Book],
Sum([2006 Display Rev Book].[2006Q4 Rev-Book]) AS [SumOf2006Q4 Rev-
Book],
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2006Q1 Rev-Book],
[Display Entity Mappings].[2006Q2 Rev-Book],
[Display Entity Mappings].[2006Q3 Rev-Book],
[Display Entity Mappings].[2006Q4 Rev-Book],
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name]
UNION ALL
SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID], [Display
Entity
Mappings].[Advertising Entity Name],
Sum([2007 Display Rev Book].[2007Q1 Rev-Book]) AS [SumOf2007Q1 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q2 Rev-Book]) AS [SumOf2007Q2 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q3 Rev-Book]) AS [SumOf2007Q3 Rev-
Book],
Sum([2007 Display Rev Book].[2007Q4 Rev-Book]) AS [SumOf2007Q4 Rev-
Book],
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2007Q1 Rev-Book],
[Display Entity Mappings].[2007Q2 Rev-Book],
[Display Entity Mappings].[2007Q3 Rev-Book],
[Display Entity Mappings].[2007Q4 Rev-Book],
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name]
UNION ALL
SELECT DISTINCT [Display Entity Mappings].[Ad Entity ID], [Display
Entity
Mappings].[Advertising Entity Name],
Sum([2008 H1 Display Rev Book].[2008Q1 Rev-Book]) AS [SumOf2008Q1 Rev-
Book],
Sum([2008 H1 Display Rev Book].[2008Q2 Rev-Book]) AS [SumOf2008Q2 Rev-
Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q3 Rev-Book]) AS
[SumOf2008Q3
Rev-Book],
Sum([2008 H2 Display Rev Book 0915].[2008Q4 Rev-Book]) AS
[SumOf2008Q4
Rev-Book]
FROM (SELECT DISTINCT [Display Entity Mapping].[Ad Entity ID],
[Display Entity Mappings].[Advertising Entity Name],
[Display Entity Mappings].[2008Q1 Rev-Book],
[Display Entity Mappings].[2008Q2 Rev-Book],
[Display Entity Mappings].[2008Q3 Rev-Book],
[Display Entity Mappings].[2008Q4 Rev-Book]
FROM [Display Entity Mappings])
GROUP BY [Display Entity Mappings].[Ad Entity ID], [Display Entity
Mappings].[Advertising Entity Name];


that query MAY and i say MAY do it but its just a guess have a look at
a chagne of business practice to stop making tables. when you build a
database and after it is built you shoudl NEVER have to create any
more tables or fields if you did it properly. so the fact you add
more tables for each year states you have done it wrong.

hope this helps

Regards
Kelvan
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

Select vs crosstab query - different result 1
Show records even with NULL data? WHERE...IN? 4
Union query not displaying select statement 1
SELECT DISTINCT only on some fields but display all fields (AC 200 3
Sum based on Yes/No Field 1
Tricky Select Distinct 3
Select All but only Select some Distinct 1
Retrieving lead record from CRM 3.0 7

Top