Query quandry

[Alastair MacFarlane]

Dear All,

If I run the following query on this table, where [JobNo] (Text),
[FeeAmount] (Currency), [Posted] (True/False):

JobNo, FeeAmount, Posted
22, £0.00 , No
4, £55.00, Yes
4, £101.00, Yes
4, £0.00, No


With the following SQL:

SELECT JobNo, Posted, Sum(test.FeeAmount) AS FeeAmount
FROM test
GROUP BY JobNo, Posted;

results in:

JobNo, Posted, FeeAmount
22, £0.00, Yes
4, Yes, £156.00

.... but what I want is:

JobNo, FeeAmount, Posted
22, £0.00, No
4, £156.00, Yes
4, £0.00, No

I wrongly presumed that it would group by the JobNo and by the Posted and
then give me a total for each and if there was only Yes then it would only
give the total of Yes.

What am I doing wrong?

Thanks again...

Alastair MacFarlane

 

[Chris2]

Dear All,

If I run the following query on this table, where [JobNo] (Text),
[FeeAmount] (Currency), [Posted] (True/False):

JobNo, FeeAmount, Posted
22, £0.00 , No
4, £55.00, Yes
4, £101.00, Yes
4, £0.00, No


With the following SQL:

SELECT JobNo, Posted, Sum(test.FeeAmount) AS FeeAmount
FROM test
GROUP BY JobNo, Posted;

results in:

JobNo, Posted, FeeAmount
22, £0.00, Yes
4, Yes, £156.00

... but what I want is:

JobNo, FeeAmount, Posted
22, £0.00, No
4, £156.00, Yes
4, £0.00, No

I wrongly presumed that it would group by the JobNo and by the Posted and
then give me a total for each and if there was only Yes then it would only
give the total of Yes.

What am I doing wrong?

Thanks again...

Alastair MacFarlane

Alastair MacFarlane,

(Please forgive the dates appended to the table names.)

Tables:

CREATE TABLE test_20051120_1
(JobNo TEXT(255)
,FeeAmount CURRENCY
,Posted BIT
,CONSTRAINT pk_test_20051120_1
PRIMARY KEY (JobNo, FeeAmount, Posted)
)

Note: I could not determine what the PK was for certain, and so I
included all the columns.)


Sample Data:

JobNo, FeeAmount, Posted
22, 0.00, 0
4, 55.00, -1
4, 101.00, 0
4, 0.00, -1


Query:

SELECT t1.JobNo
,t1.Posted
,Sum(t1.FeeAmount) AS FeeAmount
FROM test_20051120_1 AS t1
GROUP BY t1.JobNo
,t1.Posted;


Results:

JobNo, FeeAmount, Posted
22, 0.00, 0
4, 156.00, -1
4, 0.00, 0


Desired Results:

JobNo, FeeAmount, Posted
22, £0.00, No
4, £156.00, Yes
4, £0.00, No

Hmmm, I am not sure what went wrong in your query. My copy seems to
do exactly what you want.

I'm running Win2k SP-4, Access 2k SP3, JET 4.0 SP-8.


Sincerely,

Chris O.

 

[Guest]

Chris2,

Thanks for your reply and testing out my problem. The problem is that the
datasource for the query is in fact a query with the same fields and
datatypes as the table and when I run the query from the table it does work,
but when I run it from the query I get only one record for JobNo4.

I ran it on the query and used that as my example and never fully tested it
the table. Sorry. You have highlighted that the problem is with the query and
not the overall data.

I appreciate your help and I will need to do a bit of head scratching now.

Thanks.

Alastair

Dear All,

If I run the following query on this table, where [JobNo] (Text),
[FeeAmount] (Currency), [Posted] (True/False):

JobNo, FeeAmount, Posted
22, £0.00 , No
4, £55.00, Yes
4, £101.00, Yes
4, £0.00, No


With the following SQL:

SELECT JobNo, Posted, Sum(test.FeeAmount) AS FeeAmount
FROM test
GROUP BY JobNo, Posted;

results in:

JobNo, Posted, FeeAmount
22, £0.00, Yes
4, Yes, £156.00

... but what I want is:

JobNo, FeeAmount, Posted
22, £0.00, No
4, £156.00, Yes
4, £0.00, No

I wrongly presumed that it would group by the JobNo and by the Posted and
then give me a total for each and if there was only Yes then it would only
give the total of Yes.

What am I doing wrong?

Thanks again...

Alastair MacFarlane

Alastair MacFarlane,

(Please forgive the dates appended to the table names.)

Tables:

CREATE TABLE test_20051120_1
(JobNo TEXT(255)
,FeeAmount CURRENCY
,Posted BIT
,CONSTRAINT pk_test_20051120_1
PRIMARY KEY (JobNo, FeeAmount, Posted)
)

Note: I could not determine what the PK was for certain, and so I
included all the columns.)


Sample Data:

JobNo, FeeAmount, Posted
22, 0.00, 0
4, 55.00, -1
4, 101.00, 0
4, 0.00, -1


Query:

SELECT t1.JobNo
,t1.Posted
,Sum(t1.FeeAmount) AS FeeAmount
FROM test_20051120_1 AS t1
GROUP BY t1.JobNo
,t1.Posted;


Results:

JobNo, FeeAmount, Posted
22, 0.00, 0
4, 156.00, -1
4, 0.00, 0


Desired Results:

JobNo, FeeAmount, Posted
22, £0.00, No
4, £156.00, Yes
4, £0.00, No

Hmmm, I am not sure what went wrong in your query. My copy seems to
do exactly what you want.

I'm running Win2k SP-4, Access 2k SP3, JET 4.0 SP-8.


Sincerely,

Chris O.