cannot get query results right

[arage]

cannot get query results right
I have a main query with my transaction data. Transaction are separated into 2 types. I am trying to get a report that lists, by type, every month and the totals of the transaction data per Director, for that month. So my query ought to look like this:

Type Month Director unitSum
-----------------------------------------
OA 1 1100 555
OA 1 1200 25
.
.
OA 12 1500 2455
.
.
BE 1 1100 990
.
.

I’ve been having great difficulty getting this using outer joins to specify my criteria. In total I ought to have 120 rows (5 directors * 12 months * 2 types) but I cannot get these rows without the data repeating per each month.

 

[cfmiles]

How many tables do you have in your query? How do you have them joined? (what fields are you joining on?)

 

[arage]

I’m using 2 objects in the query, a table and a separate query. The table is a left join to the query based on a the Type in my chart above. The problem at this point is that if I group by Type, Month, and then Director….my repeating data starts. The I get all the months all the types and all the directors listed, its just that there data is repeated per group.

 

[Alexandre]

Hi arage,

I hope you re fine
I d like to see your SQL. In particular how you got the month into your query.

Alex

 

[arage]

hi Alex,
I’m good, how about you? Hope things are ok. I’ll try and get back to you on this on Monday, it’s the weekend y’know?

 

[Alexandre]

.

[This message has been edited by Alexandre (edited 09-21-2001).]

 

[Alexandre]

I see...
You re another one who thinks of the US as the center of the world, aren t you

Alex

[This message has been edited by Alexandre (edited 09-23-2001).]

 

[arage]

hi Alex,
here’s my query curently.

SELECT newQuery.PromotionType, tblMonth.Month, newQuery.RegionalDirectorCode, Sum(newQuery.Results) AS SumOfResults, Sum(newQuery.ActivationForecast) AS SumOfActivationForecast, Sum(newQuery.TotalCommits) AS SumOfTotalCommits, Sum(newQuery.TotalPaid) AS SumOfTotalPaid, Sum(newQuery.Accrual) AS SumOfAccrual, Sum(newQuery.ProjectedSpending) AS SumOfProjectedSpending
FROM tblMonth LEFT JOIN newQuery ON tblMonth.Type = newQuery.PromotionType
GROUP BY newQuery.PromotionType, tblMonth.Month, newQuery.RegionalDirectorCode;

The above groups properly, it however, is displaying the same total amounts for each director within a group. So that a directors january totals for type one match theirs for february, march, etc.

Btw, I’m in canada not US, hehe..


[This message has been edited by arage (edited 09-24-2001).]

 

[Alexandre]

Arage,

I suspect that the problem does not come from the query but from the underlying tables. Could you provide more details avbout tblMonth in particular. I suspect that their can be various [month] for one [type] in that table, or the contrary? Can you provide more details about the kind of value you have in tblmonth.type, Month.type, NewQuery.PromotionType and what kind of relationships link those fields (one for many, etc).
Alex

[This message has been edited by Alexandre (edited 09-24-2001).]

 

[arage]

hi Alex,
tblMonth has 3 fields & 1 index (auto num)
the 3 fields are Month, MonthName, and Type
Month is numeric and has 1 to 12 in it, monthName is self explanatory & Type is one of 2 types, OA or BE.

This table has 24 records.

click this link for a screenshot view:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=77095&page=0&view=&sb=&o=&fpart=&vc=


[This message has been edited by arage (edited 09-24-2001).]

 

[Alexandre]

Arage,

Sorry for the delay, I was journeying home.
You have a conceptual problem here. The query would work ok with a one to one or one to many relationship linking your table and your query.
But you have a many to many here.
How do you want ACCESS to understand how the records must be matched? What it does basically is that it creates all the possible combinations of the three fields you are grouping on, and fills in the otherwise void subtotals by duplicating exitant ones.
(whith what subtotal I don t know, but you wouldn t neither in it s shoes: ther is no logical choice).

Hopefull this may not necessarily mean that you have a real design problem, simce one of your tabledefs is a query. Try to re-design this query and see if you need to introduce intermediary table (look at your design) that would breakdown the relation.


Alex

Post back with more details about your DB design if you need help.

[This message has been edited by Alexandre (edited 09-24-2001).]

 

[Alexandre]

You go to bed too early, Arage

Alex

[This message has been edited by Alexandre (edited 09-24-2001).]