Basic Query Theory?

[Shawn]

I ran across a problem today that never occurred to me...
To simplify, let's say I have a Division field, with
divisions (Marketing, R&D, etc) and a Sponsor field, with
names of people. I want to run a report that groups by
Division, and then Sponsor, and then run a couple Count
calculations (Each Sponsor has X number of males, x
number of females allocated to the sponsor, etc...).
Everything runs perfectly until the rare case that a
Sponsor is in multiple Divisions. Then, it totally throws
off all my calculations for that sponsor. Is there
something basic I am missing here, to have it group FIRST
by Division, and then by Sponsor? Right now, if I group
by Division (using the QBE grid; the Totals button) and
then by Sponsor, it doesn't recognize that a sponsor
could be in multiple divisions....
Thanks very much in advance.
Shawn

 

[Jeff Boyce]

Shawn

That will depend on how your data is structured -- you could be querying a
text file, or an Excel spreadsheet, given the description you've offered.

More info, please...

Jeff Boyce
<Access MVP>

 

[Shawn]

Jeff,
The data is part of a table in Access XP given to me.
Both Division and Sponsor fields are text fields. This
query is a single table query, so no relationships are
involved. I am using the QBE grid, not SQL (don't know
it) to perform the query. What else can I provide you
with?
Thanks,
Shawn

 

[Dale Fye]

If an Sponsor is in more than one Division, then a normal group by
query will display the Division, Sponsor, and the aggregate
computations that go with that Division/Sponsor combination.

Open your query in design view. Then select View-SQL. Copy the SQL
and paste it in your response to this message.

--
HTH

Dale Fye


Jeff,
The data is part of a table in Access XP given to me.
Both Division and Sponsor fields are text fields. This
query is a single table query, so no relationships are
involved. I am using the QBE grid, not SQL (don't know
it) to perform the query. What else can I provide you
with?
Thanks,
Shawn

 

[Shawn]

This is the query that is causing me problems. It is a
query based off of two other queries that have been
joined by the Sponsor field. I have tried all three types
of relationships in the Join window(inner and outer
joins). This may be confusing, since this query is built
off others. Thanks for looking this over.
(SIDE NOTE - 'count of completed listing 2' is the name
of the main table - I did a 'Count (*)' in an earlier
query to count the # of males and females / sponsor.)

SELECT qryGenderBreakdownSponsor.Division,
qryGenderBreakdownSponsor.Sponsor,
qryGenderBreakdownSponsor.[Male/Female],
qryGenderBreakdownSponsor.[Count Of completed listing 2]
AS [Count], qryTotalsforGenderSponsor.[CountOfMale/Female]
FROM qryTotalsforGenderSponsor INNER JOIN
qryGenderBreakdownSponsor ON
qryTotalsforGenderSponsor.Sponsor =
qryGenderBreakdownSponsor.Sponsor
ORDER BY qryGenderBreakdownSponsor.Division,
qryGenderBreakdownSponsor.Sponsor;

 

[Shawn]

Dale and Jeff,
I sincerely thank you for your efforts. This morning, I
ran a CT query on the data, and then built a query off
that for the calculations, and it worked like a charm,
although I have absolutely no idea why. It does not make
sense that it would group properly through the CT query
but not through a normal query.

In any case, the problem has been solved. Again, big
thanks for the attempted assistance. You guys who work
the Newsgroups do a great job, and a source I rely on
when in trouble.

Shawn

 

[Sirocco]

Whenever you don't have a pure hierarchy of data, groupings will return
unexpected results, and the desired results will require a few more steps
than initially realized.