Select Distinct II

J

JensB

I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10 500 -"-
300 20 20 1000 -"-
400 30 30 1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) = 3300
with one query on all rows?

Jens
 
M

Michel Walsh

Yes, but someone can call it a hack. Have a table, Iotas, one field, iota,
two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


JensB said:
I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10 500 -"-
300 20 20 1000 -"-
400 30 30
1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) = 3300
with one query on all rows?

Jens
Click to expand...
 
J

JensB

Table Iotas done, 2 rows with values 0 and 1 added.
Unfortunate I get this message:
"You tried to execute a query that does not include the specified expression
'IIf(iota=0,quotehead.CustNo,Null)' as a part of an aggregate function"

JensB



Michel Walsh said:
Yes, but someone can call it a hack. Have a table, Iotas, one field, iota,
two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


JensB said:
I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10 500 -"-
300 20 20
1000 -"-
400 30 30 1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) = 3300
with one query on all rows?

Jens
Click to expand...
Click to expand...
 
G

Gary Walter

I believe Michel meant that last "ORDER BY"
to be "GROUP BY"

JensB said:
Table Iotas done, 2 rows with values 0 and 1 added.
Unfortunate I get this message:
"You tried to execute a query that does not include the specified
expression 'IIf(iota=0,quotehead.CustNo,Null)' as a part of an aggregate
function"

JensB



Michel Walsh said:
Yes, but someone can call it a hack. Have a table, Iotas, one field,
iota, two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


JensB said:
I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10 500 -"-
300 20 20 1000 -"-
400 30 30 1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) =
3300
with one query on all rows?

Jens
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

!!! oops !!! indeed.



SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
GROUP BY iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL);



Vanderghast, Access MVP


Gary Walter said:
I believe Michel meant that last "ORDER BY"
to be "GROUP BY"

JensB said:
Table Iotas done, 2 rows with values 0 and 1 added.
Unfortunate I get this message:
"You tried to execute a query that does not include the specified
expression 'IIf(iota=0,quotehead.CustNo,Null)' as a part of an aggregate
function"

JensB



Michel Walsh said:
Yes, but someone can call it a hack. Have a table, Iotas, one field,
iota, two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS
tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo =
quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10
500 -"-
300 20 20 1000 -"-
400 30 30 1500 -"-
400 30-a 30
300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) =
3300
with one query on all rows?

Jens
Click to expand...
Click to expand...
Click to expand...
 
J

JensB

Unfortunate Michels sql gave an aggregate error on "quotehead.QuoteNo",
then I removed quotehead.QuoteNo
after tPrice. Then I got a pop-up asking for parameter value o.custno and a
second pop-up asking quotehead.Custno
if I do not enter any values in these pop-ups, hourglass continues for
another 20 seconds, and the query result comes out empty.
I have tried many combinations of this SQL but without no luck, so my
conclusion due to my schedules, that I will use the first SQL I got from
Duane,
and then by help of a loop, adding the results from tPrice to a sum.
Thanks anyway for your contribution.
JensB

Michel Walsh said:
!!! oops !!! indeed.



SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
GROUP BY iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL);



Vanderghast, Access MVP


Gary Walter said:
I believe Michel meant that last "ORDER BY"
to be "GROUP BY"

JensB said:
Table Iotas done, 2 rows with values 0 and 1 added.
Unfortunate I get this message:
"You tried to execute a query that does not include the specified
expression 'IIf(iota=0,quotehead.CustNo,Null)' as a part of an aggregate
function"

JensB



Yes, but someone can call it a hack. Have a table, Iotas, one field,
iota, two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS
tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo =
quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10 2000 -"-
200 10-b 10 500 -"-
300 20 20 1000 -"-
400 30 30 1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) =
3300
with one query on all rows?

Jens
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


I haven't see the ",quotehead.QuoteNo", the first time, so, indeed, it
should also be in taken in the GROUP BY, but since you want to sum over all
the QuoteNo, that should be something like:


SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,
iif(iota=0, quotehead.QuoteNo, NULL)

....

GROUP BY iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),

iif(iota=0, quotehead.QuoteNo, NULL)



Vanderghast, Access MVP



JensB said:
Unfortunate Michels sql gave an aggregate error on "quotehead.QuoteNo",
then I removed quotehead.QuoteNo
after tPrice. Then I got a pop-up asking for parameter value o.custno and
a second pop-up asking quotehead.Custno
if I do not enter any values in these pop-ups, hourglass continues for
another 20 seconds, and the query result comes out empty.
I have tried many combinations of this SQL but without no luck, so my
conclusion due to my schedules, that I will use the first SQL I got from
Duane,
and then by help of a loop, adding the results from tPrice to a sum.
Thanks anyway for your contribution.
JensB

Michel Walsh said:
!!! oops !!! indeed.



SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo
ORDER BY
QuoteNo Desc)))
GROUP BY iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL);



Vanderghast, Access MVP


Gary Walter said:
I believe Michel meant that last "ORDER BY"
to be "GROUP BY"

:
Table Iotas done, 2 rows with values 0 and 1 added.
Unfortunate I get this message:
"You tried to execute a query that does not include the specified
expression 'IIf(iota=0,quotehead.CustNo,Null)' as a part of an
aggregate function"

JensB



Yes, but someone can call it a hack. Have a table, Iotas, one field,
iota, two records, one with iota=0 the other with iota=1.

SELECT iif(iota=0, quotehead.CustNo, NULL),
iif(iota=0, quotehead.TrackNo, NULL),
Sum([quotelines].[TotalPrice]) AS
tPrice,quotehead.QuoteNo
FROM (Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID), Iotas
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo =
quotehead.TrackNo ORDER BY
QuoteNo Desc)))
ORDER BY iif(iota=0, quotehead.CustNo, NULL), iif(iota=0,
quotehead.TrackNo, NULL);



Hoping it may help,
Vanderghast, Access MVP


I got this nice pease of SQL from Duane
Showing the total for each valid quote:

SELECT Sum([quotelines].[TotalPrice]) AS tPrice,quotehead.QuoteNo
FROM Quotehead INNER JOIN QuoteLines ON Quotehead.id =
QuoteLines.QuoteHeadID
WHERE (((quotehead.ID) In (SELECT TOP 1 ID FROM quotehead O WHERE
O.CustNo = quotehead.CustNo and O.TrackNo = quotehead.TrackNo ORDER
BY
QuoteNo Desc)))
ORDER BY quotehead.CustNo, quotehead.TrackNo;

When I use above SQL on these rows
CustNo QuoteNo TrackNo (tPrice)
200 10 10 1000(Sum from
quotelines)
200 10-a 10
2000 -"-
200 10-b 10 500 -"-
300 20 20 1000 -"-
400 30 30 1500 -"-
400 30-a 30 300 -"-

I get this result:
200 10-b 10 2000
300 20 20 1000
400 30-a 30 300

My new question is:
Is it posible to the get the sum of the result( 200, 300 and 400) =
3300
with one query on all rows?

Jens
Click to expand...
Click to expand...
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

Nested If conversion to Select case 5
Tough PIVOT problem 1
SQL for Multiple Parameters 2
addition in listbox 2
different scale on x-axis 2
SUMIF Problem 2
SQL JOIN simplifying 5
need to merge 2 tables joined on a field 3

Top