Stuck in a query!

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

Telesphore

In the following query:

SELECT tbInscriptions.DateInscription, Sum(IIf([ProgBTH],1,0)) AS
TotalProgBTh, Sum(IIf([ProgMDiv],1,0)) AS TotalProgMDiv
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription

I need to count those who said "yes" altogether in ProgBTh and ProgMDiv

Thanks a lot
 
Telesphore,

Do you mean count of those who said "yes" to both? If so...
Sum([ProgMDiv]*[ProgBTH]) AS TotalProgBoth
 
Do you mean count of those who said "yes" to both? If so...
Sum([ProgMDiv]*[ProgBTH]) AS TotalProgBoth
Click to expand...

Thank you, but the problem is that we need to know how many students took
only the two programs.

We know that 22 students took ProgBTh and 2 of them took also ProgMDiv at
the same time. So the answer should be 2.
 
Telesphore,

Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.
 
Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.
Click to expand...


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0 instead of 2!

SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
 
PMFBI

I think you had better give some example
data. I would think Steve's excellent solution
would work if data were like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

so

SELECT
tblInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tblInscriptions
GROUP BY tblInscriptions.DateInscription;

would give (correctly):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2


but it sounds like maybe it must be more like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 0
9/1/2004 A 0 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 0
9/1/2004 D 0 -1
9/1/2004 E -1 0

where the above query would give ("wrongly"):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 0

If this is the case, one method would be to create a
prequery that groups on date and studentid to give
form of first table:

"Q1": (you might want to give it a better name)

SELECT
tblInscriptions.DateInscription,
tblInscriptions.StudentID,
Min(tblInscriptions.ProgMDiv) AS bProgMDiv,
Min(tblInscriptions.ProgBTh) AS bProgBTh
FROM tblInscriptions
GROUP BY
tblInscriptions.DateInscription,
tblInscriptions.StudentID;

giving:

DateInscription StudentID bProgMDiv bProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

Then you can use Steve's method above

SELECT
Q1.DateInscription,
Sum(Abs([bProgMDiv])) AS TotalProgMDiv,
Sum(Abs([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;

giving

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2

Of course I could be wrong.

And apologies again for butting in.

Gary Walter





Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.
Click to expand...


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0 instead of 2!

SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
Click to expand...
 
actually, btw...more efficient to put "Abs"
on outside of "Sum"....it would be important
point if you had sizable data.....

SELECT
Q1.DateInscription,
Abs(Sum([bProgMDiv])) AS TotalProgMDiv,
Abs(Sum([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;


PMFBI

I think you had better give some example
data. I would think Steve's excellent solution
would work if data were like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

so

SELECT
tblInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tblInscriptions
GROUP BY tblInscriptions.DateInscription;

would give (correctly):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2


but it sounds like maybe it must be more like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 0
9/1/2004 A 0 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 0
9/1/2004 D 0 -1
9/1/2004 E -1 0

where the above query would give ("wrongly"):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 0

If this is the case, one method would be to create a
prequery that groups on date and studentid to give
form of first table:

"Q1": (you might want to give it a better name)

SELECT
tblInscriptions.DateInscription,
tblInscriptions.StudentID,
Min(tblInscriptions.ProgMDiv) AS bProgMDiv,
Min(tblInscriptions.ProgBTh) AS bProgBTh
FROM tblInscriptions
GROUP BY
tblInscriptions.DateInscription,
tblInscriptions.StudentID;

giving:

DateInscription StudentID bProgMDiv bProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

Then you can use Steve's method above

SELECT
Q1.DateInscription,
Sum(Abs([bProgMDiv])) AS TotalProgMDiv,
Sum(Abs([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;

giving

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2

Of course I could be wrong.

And apologies again for butting in.

Gary Walter





Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.
Click to expand...
Telesphore wrote:
Thank you, but the problem is that we need to know how many students took
only the two programs.

We know that 22 students took ProgBTh and 2 of them took also ProgMDiv at
the same time. So the answer should be 2.
Click to expand...


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0 instead of 2!

SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
Click to expand...
Click to expand...
 
Thank you Gary,

I copied your first code, plus the date:

SELECT
tbInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));


and here are the results:

TotalProgMDiv gives 2
TotalProgBTh gives 22
That is OK,

But
TotalProgBoth gives 0, meanwhile in your first example, which is good, it
gives 2!

We know already that 2 of the 22 Students take ProgMDiv and ProgBTh, so
TotalProgBoth should give 2.

Something is missing?


"Gary Walter" wrote in message
I think you had better give some example
data. I would think Steve's excellent solution
would work if data were like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

so

SELECT
tblInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tblInscriptions
GROUP BY tblInscriptions.DateInscription;

would give (correctly):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2


but it sounds like maybe it must be more like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 0
9/1/2004 A 0 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 0
9/1/2004 D 0 -1
9/1/2004 E -1 0

where the above query would give ("wrongly"):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 0

If this is the case, one method would be to create a
prequery that groups on date and studentid to give
form of first table:

"Q1": (you might want to give it a better name)

SELECT
tblInscriptions.DateInscription,
tblInscriptions.StudentID,
Min(tblInscriptions.ProgMDiv) AS bProgMDiv,
Min(tblInscriptions.ProgBTh) AS bProgBTh
FROM tblInscriptions
GROUP BY
tblInscriptions.DateInscription,
tblInscriptions.StudentID;

giving:

DateInscription StudentID bProgMDiv bProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

Then you can use Steve's method above

SELECT
Q1.DateInscription,
Sum(Abs([bProgMDiv])) AS TotalProgMDiv,
Sum(Abs([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;

giving

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2

Of course I could be wrong.

And apologies again for butting in.

Gary Walter





Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.
Click to expand...
Telesphore wrote:
Thank you, but the problem is that we need to know how many students took
only the two programs.

We know that 22 students took ProgBTh and 2 of them took also
Click to expand...
ProgMDiv
at
the same time. So the answer should be 2.
Click to expand...


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0 instead of 2!

SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
Click to expand...
Click to expand...
 
Hi Telesphore,

I gave you 2 examples of how your
data might look (and you have given
none).

If your data looks like the 2nd example, you
will have to construct a prequery like
"Q1"

Can you see the differences between
the 2 example data sets?

Is your data like the 2nd example?

Please run a query on tblInscription for
date = 9/1/2004.

SELECT * FROM
tblInscription
WHERE
DateInscription = #9/1/2004#;

Click in the upper left hand corner
of the results of this query to select
all the results (they should all be blackened),
right-mouse click on selection, choose "Copy"
to save results to Clipboard,
then paste results back here in a post.

While discouraged on newsgroups, if you
choose to reply to this post, then set format
of reply to Rich Text(HTML), i.e., in top menu
of reply choose Format/Rich Text, *then* paste
results, it will give HTML table that will
be easier to read.

Thanks,

Gary Walter

I copied your first code, plus the date:

SELECT
tbInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));


and here are the results:

TotalProgMDiv gives 2
TotalProgBTh gives 22
That is OK,

But
TotalProgBoth gives 0, meanwhile in your first example, which is good, it
gives 2!

We know already that 2 of the 22 Students take ProgMDiv and ProgBTh, so
TotalProgBoth should give 2.

Something is missing?


"Gary Walter" wrote in message
I think you had better give some example
data. I would think Steve's excellent solution
would work if data were like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

so

SELECT
tblInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tblInscriptions
GROUP BY tblInscriptions.DateInscription;

would give (correctly):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2


but it sounds like maybe it must be more like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 0
9/1/2004 A 0 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 0
9/1/2004 D 0 -1
9/1/2004 E -1 0

where the above query would give ("wrongly"):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 0

If this is the case, one method would be to create a
prequery that groups on date and studentid to give
form of first table:

"Q1": (you might want to give it a better name)

SELECT
tblInscriptions.DateInscription,
tblInscriptions.StudentID,
Min(tblInscriptions.ProgMDiv) AS bProgMDiv,
Min(tblInscriptions.ProgBTh) AS bProgBTh
FROM tblInscriptions
GROUP BY
tblInscriptions.DateInscription,
tblInscriptions.StudentID;

giving:

DateInscription StudentID bProgMDiv bProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

Then you can use Steve's method above

SELECT
Q1.DateInscription,
Sum(Abs([bProgMDiv])) AS TotalProgMDiv,
Sum(Abs([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;

giving

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2

Of course I could be wrong.

And apologies again for butting in.

Gary Walter





Either I am missing your meaning, or the suggestion I made will give the
right answer i.e. 2.

Telesphore wrote:
Thank you, but the problem is that we need to know how many students
took
only the two programs.

We know that 22 students took ProgBTh and 2 of them took also ProgMDiv
at
the same time. So the answer should be 2.


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0 instead of 2!

SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Gary,

You were right, my query was similar to your 2nd example. That was the
reason, as you said, it couldn't work with the first example.

First, I didn't put the StudentID in the query.

Second, when I putted the students name in alphabetic order, I realized that
the 2 students were doubled, so that in the two cases, 1 x 0 = 0!

I'll use the 2 queries for the statistics report we need. That was my fist
task!

Now, for the same statistics, I must work on a second task. We need to know
now, how many students of the 22 didn't take the MDiv program ([ProgMDiv])
and only the BTh program ([ProgBTh]), i.e. 20.

If I am stuck, I'll ask again.

Thank you so much.

...
I gave you 2 examples of how your
data might look (and you have given
none).

If your data looks like the 2nd example, you
will have to construct a prequery like
"Q1"

Can you see the differences between
the 2 example data sets?

Is your data like the 2nd example?

Please run a query on tblInscription for
date = 9/1/2004.

SELECT * FROM
tblInscription
WHERE
DateInscription = #9/1/2004#;

Click in the upper left hand corner
of the results of this query to select
all the results (they should all be blackened),
right-mouse click on selection, choose "Copy"
to save results to Clipboard,
then paste results back here in a post.

While discouraged on newsgroups, if you
choose to reply to this post, then set format
of reply to Rich Text(HTML), i.e., in top menu
of reply choose Format/Rich Text, *then* paste
results, it will give HTML table that will
be easier to read.

Thanks,

Gary Walter

I copied your first code, plus the date:

SELECT
tbInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));


and here are the results:

TotalProgMDiv gives 2
TotalProgBTh gives 22
That is OK,

But
TotalProgBoth gives 0, meanwhile in your first example, which is good, it
gives 2!

We know already that 2 of the 22 Students take ProgMDiv and ProgBTh, so
TotalProgBoth should give 2.

Something is missing?


"Gary Walter" wrote in message
I think you had better give some example
data. I would think Steve's excellent solution
would work if data were like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

so

SELECT
tblInscriptions.DateInscription,
Sum(Abs([ProgMDiv])) AS TotalProgMDiv,
Sum(Abs([ProgBTh])) AS TotalProgBth,
Sum([ProgMDiv]*[ProgBTh]) AS TotalProgBoth
FROM tblInscriptions
GROUP BY tblInscriptions.DateInscription;

would give (correctly):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2


but it sounds like maybe it must be more like:

DateInscription StudentID ProgMDiv ProgBTh
9/1/2004 A -1 0
9/1/2004 A 0 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 0
9/1/2004 D 0 -1
9/1/2004 E -1 0

where the above query would give ("wrongly"):

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 0

If this is the case, one method would be to create a
prequery that groups on date and studentid to give
form of first table:

"Q1": (you might want to give it a better name)

SELECT
tblInscriptions.DateInscription,
tblInscriptions.StudentID,
Min(tblInscriptions.ProgMDiv) AS bProgMDiv,
Min(tblInscriptions.ProgBTh) AS bProgBTh
FROM tblInscriptions
GROUP BY
tblInscriptions.DateInscription,
tblInscriptions.StudentID;

giving:

DateInscription StudentID bProgMDiv bProgBTh
9/1/2004 A -1 -1
9/1/2004 B -1 0
9/1/2004 C 0 -1
9/1/2004 D -1 -1
9/1/2004 E -1 0

Then you can use Steve's method above

SELECT
Q1.DateInscription,
Sum(Abs([bProgMDiv])) AS TotalProgMDiv,
Sum(Abs([bProgBTh])) AS TotalProgBth,
Sum([bProgMDiv]*[bProgBTh]) AS TotalProgBoth
FROM Q1
GROUP BY Q1.DateInscription;

giving

DateInscription TotalProgMDiv TotalProgBth TotalProgBoth
9/1/2004 4 3 2

Of course I could be wrong.

And apologies again for butting in.

Gary Walter






Either I am missing your meaning, or the suggestion I made will
Click to expand...
give
the
right answer i.e. 2.

Telesphore wrote:
Thank you, but the problem is that we need to know how many students
took
only the two programs.

We know that 22 students took ProgBTh and 2 of them took also ProgMDiv
at
the same time. So the answer should be 2.


Thank you Steve,

You didn't miss the meaning, but the following code gives me 0
Click to expand...
instead
of 2!
SELECT tbInscriptions.DateInscription, Sum([ProgMDiv]*[ProgBTh]) AS
TotalProgBoth
FROM tbInscriptions
GROUP BY tbInscriptions.DateInscription
HAVING (((tbInscriptions.DateInscription)=#9/1/2004#));
Click to expand...
Click to expand...
Click to expand...
 
No apology necessary, Gary. On the contrary. As usual, you have done a
magnificent job of handling this one. I obviously made the mistake of
assuming a more normalised data structure than is the case.
 
Back
Top