Results of union not appearing in recordset

[sean]

HI There,

I am trying to execute a union query which returns the total value of the
OptionPrice column (amongst other things), when the results are returned the
value returned by the union is correct but I cannot access this value via
the recordset on my page, I have tried to alias the query hoping that the
result of the union will appear in the alised column but that did not work.
Could someone help me out
with a little code help please?

Sean - thank in advance for your answer


PARAMETERS pOrderNumber Long;
SELECT 'Details' AS Source, [tmpproductoptions].[OptionID],
[tmpproductoptions].[OptionValue], [tmpproductoptions].[OptionPrice],
[tmpproductoptions].[OrderNumber], [product_options].[OptionID],
[product_options].[TaxRateID], [TaxRate].[tblTaxRateID], [TaxRate].[TaxRate]
FROM tmpproductoptions, product_options, TaxRate
WHERE [tmpproductoptions].[OrderNumber]=pOrderNumber And
[tmpproductoptions].[OptionID]=[product_options].[OptionID] And
[product_options].[TaxRateID]=[TaxRate].[tblTaxRateID]

UNION ALL SELECT 'Rollup', Null, Null, Sum(TheVal.OptionPrice), Null, Null,
Null,Null, Null
FROM tmpproductoptions TheVal
WHERE TheVal.[OrderNumber]=pOrderNumber
ORDER BY Source;


Source tmpproductoptions.OptionID OptionValue OptionPrice OrderNumber
product_options.OptionID TaxRateID tblTaxRateID TaxRate
Details 97 Chocolate 12 163773040 97 1 1 1.1
Details 96 Balloons 12 163773040 96 1 1 1.1
Rollup 24

[Bob Barrows [MVP]]

sean wrote:

> HI There,
>
> I am trying to execute a union query which returns the total value of
> the OptionPrice column (amongst other things), when the results are
> returned the value returned by the union is correct but I cannot
> access this value via the recordset on my page, I have tried to alias
> the query hoping that the result of the union will appear in the
> alised column but that did not work.

Huh? You should get a single resultset as a result of the union. With the
ORDER BY clause, your rollup should be found in the last record in the 4th
field.

> Could someone help me out
> with a little code help please?
>
> PARAMETERS pOrderNumber Long;
> SELECT 'Details' AS Source, [tmpproductoptions].[OptionID],
> [tmpproductoptions].[OptionValue], [tmpproductoptions].[OptionPrice],
> [tmpproductoptions].[OrderNumber], [product_options].[OptionID],
> [product_options].[TaxRateID], [TaxRate].[tblTaxRateID],

Why are you returning the TaxRateID twice?????

> [TaxRate].[TaxRate] FROM tmpproductoptions, product_options, TaxRate
> WHERE [tmpproductoptions].[OrderNumber]=pOrderNumber And
> [tmpproductoptions].[OptionID]=[product_options].[OptionID] And
> [product_options].[TaxRateID]=[TaxRate].[tblTaxRateID]
>
> UNION ALL SELECT 'Rollup', Null, Null, Sum(TheVal.OptionPrice), Null,
> Null, Null,Null, Null
> FROM tmpproductoptions TheVal
> WHERE TheVal.[OrderNumber]=pOrderNumber
> ORDER BY Source;
>
>
> Source tmpproductoptions.OptionID OptionValue OptionPrice
> OrderNumber product_options.OptionID TaxRateID tblTaxRateID TaxRate
> Details 97 Chocolate 12 163773040 97 1 1 1.1
> Details 96 Balloons 12 163773040 96 1 1 1.1
> Rollup 24

product_options.OptionID is not a valid alias. You cannot have periods in
your aliases.

Have you tested this query in Access? I'm assuming that's how you got the
results you showed.

It's hard to understand what your problem is when you don't tell us the
symptoms or how to reporduces them ("did not work" does not work). The total
value should appear in the last record in the 4th field.

Let's try making your query a little more ANSI-compliant and easier to read
(along with getting rid of that extra TaxRateID column):

PARAMETERS pOrderNumber Long;

SELECT 'Details' AS Source, t1.[OptionID],
t1.[OptionValue], t1.[OptionPrice],
t1.[OrderNumber], p.[OptionID],
p.[TaxRateID], t.[TaxRate]
FROM
(tmpproductoptions t1 INNER JOIN product_options p
ON t1.[OptionID]=p.OptionID)
INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
WHERE [tmpproductoptions].[OrderNumber]=[pOrderNumber]

UNION ALL

SELECT 'Rollup', Null, Null, Sum(OptionPrice), Null,
Null,Null, Null
FROM tmpproductoptions
WHERE [OrderNumber]=pOrderNumber
ORDER BY Source;

Test this query in Access to make sure it works!

Then, in ASP your result should be in the last record (the record where
Source = 'Rollup')

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[sean]

HI Bob,

Sorry about that I was desperate to get it too work, its working but I have
one more question. Is it possible to make the total as an alias to appear in
a colum with a different name?

I appreciate the help

Sean


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uaauxdMPEHA.4036@TK2MSFTNGP12.phx.gbl...

> sean wrote:

> > HI There,
> >
> > I am trying to execute a union query which returns the total value of
> > the OptionPrice column (amongst other things), when the results are
> > returned the value returned by the union is correct but I cannot
> > access this value via the recordset on my page, I have tried to alias
> > the query hoping that the result of the union will appear in the
> > alised column but that did not work.

>
> Huh? You should get a single resultset as a result of the union. With the
> ORDER BY clause, your rollup should be found in the last record in the 4th
> field.
>

> > Could someone help me out
> > with a little code help please?
> >
> > PARAMETERS pOrderNumber Long;
> > SELECT 'Details' AS Source, [tmpproductoptions].[OptionID],
> > [tmpproductoptions].[OptionValue], [tmpproductoptions].[OptionPrice],
> > [tmpproductoptions].[OrderNumber], [product_options].[OptionID],
> > [product_options].[TaxRateID], [TaxRate].[tblTaxRateID],

>
> Why are you returning the TaxRateID twice?????
>
>

> > [TaxRate].[TaxRate] FROM tmpproductoptions, product_options, TaxRate
> > WHERE [tmpproductoptions].[OrderNumber]=pOrderNumber And
> > [tmpproductoptions].[OptionID]=[product_options].[OptionID] And
> > [product_options].[TaxRateID]=[TaxRate].[tblTaxRateID]
> >
> > UNION ALL SELECT 'Rollup', Null, Null, Sum(TheVal.OptionPrice), Null,
> > Null, Null,Null, Null
> > FROM tmpproductoptions TheVal
> > WHERE TheVal.[OrderNumber]=pOrderNumber
> > ORDER BY Source;
> >
> >
> > Source tmpproductoptions.OptionID OptionValue OptionPrice
> > OrderNumber product_options.OptionID TaxRateID tblTaxRateID TaxRate
> > Details 97 Chocolate 12 163773040 97 1 1 1.1
> > Details 96 Balloons 12 163773040 96 1 1 1.1
> > Rollup 24

>
>
> product_options.OptionID is not a valid alias. You cannot have periods in
> your aliases.
>
> Have you tested this query in Access? I'm assuming that's how you got the
> results you showed.
>
> It's hard to understand what your problem is when you don't tell us the
> symptoms or how to reporduces them ("did not work" does not work). The

total

> value should appear in the last record in the 4th field.
>
> Let's try making your query a little more ANSI-compliant and easier to

read

> (along with getting rid of that extra TaxRateID column):
>
> PARAMETERS pOrderNumber Long;
>
> SELECT 'Details' AS Source, t1.[OptionID],
> t1.[OptionValue], t1.[OptionPrice],
> t1.[OrderNumber], p.[OptionID],
> p.[TaxRateID], t.[TaxRate]
> FROM
> (tmpproductoptions t1 INNER JOIN product_options p
> ON t1.[OptionID]=p.OptionID)
> INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> WHERE [tmpproductoptions].[OrderNumber]=[pOrderNumber]
>
> UNION ALL
>
> SELECT 'Rollup', Null, Null, Sum(OptionPrice), Null,
> Null,Null, Null
> FROM tmpproductoptions
> WHERE [OrderNumber]=pOrderNumber
> ORDER BY Source;
>
> Test this query in Access to make sure it works!
>
> Then, in ASP your result should be in the last record (the record where
> Source = 'Rollup')
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Aaron Bertrand - MVP]

> Sorry about that I was desperate to get it too work, its working but I
have

> one more question. Is it possible to make the total as an alias to appear

in

> a colum with a different name?

Do you mean a different column? Sure, just add a column to the end of both
ends of the UNION and make sure you use the alias in the first half.

e.g.

SELECT
..., ..., Total = NULL
...
UNION ALL
SELECT
NULL, ..., Total = SUM(TotalPrice)
...

If you mean something else, you're going to have to be more specific.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[sean]

HI Aaron,

sorry for all the questions but if I want an extra column with the total of
Sum(OptionPrice) where abouts do I put it in the query?

Sean

PARAMETERS pOrderNumber Long;
SELECT 'Details' AS Source, t1.[OptionID],
t1.[OptionValue], t1.[OptionPrice],
t1.[OrderNumber], p.[OptionID],
p.[TaxRateID], t.[TaxRate]
FROM
(tmpproductoptions t1 INNER JOIN product_options p
ON t1.[OptionID]=p.OptionID)
INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
WHERE [t1].[OrderNumber]=[pOrderNumber]

UNION ALL SELECT 'rollup', Null, Null,Sum(OptionPrice), Null,
Null,Null, Null
FROM tmpproductoptions
WHERE [OrderNumber]=pOrderNumber
ORDER BY Source;



"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:unRI4JOPEHA.1452@TK2MSFTNGP09.phx.gbl...

> > Sorry about that I was desperate to get it too work, its working but I

> have

> > one more question. Is it possible to make the total as an alias to

appear

> in

> > a colum with a different name?

>
> Do you mean a different column? Sure, just add a column to the end of

both

> ends of the UNION and make sure you use the alias in the first half.
>
> e.g.
>
> SELECT
> ..., ..., Total = NULL
> ...
> UNION ALL
> SELECT
> NULL, ..., Total = SUM(TotalPrice)
> ...
>
> If you mean something else, you're going to have to be more specific.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Bob Barrows [MVP]]

sean wrote:

> HI Aaron,
>
> sorry for all the questions but if I want an extra column with the
> total of Sum(OptionPrice) where abouts do I put it in the query?

See below (why do you want an extra column?)

>
> Sean
>
> PARAMETERS pOrderNumber Long;
> SELECT 'Details' AS Source, t1.[OptionID],
> t1.[OptionValue], t1.[OptionPrice],
> t1.[OrderNumber], p.[OptionID],
> p.[TaxRateID], t.[TaxRate], Null Total
> FROM
> (tmpproductoptions t1 INNER JOIN product_options p
> ON t1.[OptionID]=p.OptionID)
> INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> WHERE [t1].[OrderNumber]=[pOrderNumber]
>
> UNION ALL SELECT 'rollup', Null, Null,Null, Null,
> Null,Null, Nul,lSum(OptionPrice)
> FROM tmpproductoptions
> WHERE [OrderNumber]=pOrderNumber
> ORDER BY Source;

Test it in Access.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[sean]

Hi Bob,

I have tested it an access and it works really well thanks, all I want to
know if if I can add the total in an extra column (like an alias)? I have
tried to add another column in both the select statements without any luck.

sean


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eRVhAXOPEHA.988@tk2msftngp13.phx.gbl...

> sean wrote:

> > HI Aaron,
> >
> > sorry for all the questions but if I want an extra column with the
> > total of Sum(OptionPrice) where abouts do I put it in the query?

>
> See below (why do you want an extra column?)

> >
> > Sean
> >
> > PARAMETERS pOrderNumber Long;
> > SELECT 'Details' AS Source, t1.[OptionID],
> > t1.[OptionValue], t1.[OptionPrice],
> > t1.[OrderNumber], p.[OptionID],
> > p.[TaxRateID], t.[TaxRate], Null Total
> > FROM
> > (tmpproductoptions t1 INNER JOIN product_options p
> > ON t1.[OptionID]=p.OptionID)
> > INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> > WHERE [t1].[OrderNumber]=[pOrderNumber]
> >
> > UNION ALL SELECT 'rollup', Null, Null,Null, Null,
> > Null,Null, Nul,lSum(OptionPrice)
> > FROM tmpproductoptions
> > WHERE [OrderNumber]=pOrderNumber
> > ORDER BY Source;

>
> Test it in Access.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Aaron Bertrand - MVP]

What Bob just provided you did exactly what you're asking: it put the total
into its own column, with the alias "total"...

What part of your request have we not answered or are we not understanding?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"sean" <sean_NO_SPAM@shopsmart.com.au> wrote in message
news:ulLQlcOPEHA.988@tk2msftngp13.phx.gbl...

> Hi Bob,
>
> I have tested it an access and it works really well thanks, all I want to
> know if if I can add the total in an extra column (like an alias)? I have
> tried to add another column in both the select statements without any

luck.

>
> sean
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eRVhAXOPEHA.988@tk2msftngp13.phx.gbl...

> > sean wrote:

> > > HI Aaron,
> > >
> > > sorry for all the questions but if I want an extra column with the
> > > total of Sum(OptionPrice) where abouts do I put it in the query?

> >
> > See below (why do you want an extra column?)

> > >
> > > Sean
> > >
> > > PARAMETERS pOrderNumber Long;
> > > SELECT 'Details' AS Source, t1.[OptionID],
> > > t1.[OptionValue], t1.[OptionPrice],
> > > t1.[OrderNumber], p.[OptionID],
> > > p.[TaxRateID], t.[TaxRate], Null Total
> > > FROM
> > > (tmpproductoptions t1 INNER JOIN product_options p
> > > ON t1.[OptionID]=p.OptionID)
> > > INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> > > WHERE [t1].[OrderNumber]=[pOrderNumber]
> > >
> > > UNION ALL SELECT 'rollup', Null, Null,Null, Null,
> > > Null,Null, Nul,lSum(OptionPrice)
> > > FROM tmpproductoptions
> > > WHERE [OrderNumber]=pOrderNumber
> > > ORDER BY Source;

> >
> > Test it in Access.
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>

[Bob Barrows [MVP]]

sean wrote:

> Hi Bob,
>
> I have tested it an access and it works really well thanks, all I
> want to know if if I can add the total in an extra column (like an
> alias)? I have tried to add another column in both the select
> statements without any luck.
>
> sean

I think that instead of this:

Details 97 Chocolate 12 163773040 97 1 1 1.1
Details 96 Balloons 12 163773040 96 1 1 1.1
Rollup 24

You want this?

Details 97 Chocolate 12 163773040 97 1 1 1.1 24
Details 96 Balloons 12 163773040 96 1 1 1.1 24

This is possible using a correlated subquery, but it will be horribly
inefficient, because the subquery will need to run wonce for every row of
detail returned. Are you sure you want this? Why??

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[sean]

HI There,

The query at the moment is returning rows with the column "OptionPrice"
rolled into a total on a different row which is great, but can I return the
SUM(OptionPrice) as another seperate column instead of returning the summed
value in the OptionPrice column?

Sorry for all the question, but SQL is not my strong point.

Sean
Source t1.OptionID OptionValue OptionPrice OrderNumber p.OptionID
TaxRateID TaxRate
Details 97 Chocolate 12 217118489 97 1 1.1
rollup

12









"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:e#CZsfOPEHA.620@TK2MSFTNGP10.phx.gbl...

> What Bob just provided you did exactly what you're asking: it put the

total

> into its own column, with the alias "total"...
>
> What part of your request have we not answered or are we not

understanding?

>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "sean" <sean_NO_SPAM@shopsmart.com.au> wrote in message
> news:ulLQlcOPEHA.988@tk2msftngp13.phx.gbl...

> > Hi Bob,
> >
> > I have tested it an access and it works really well thanks, all I want

to

> > know if if I can add the total in an extra column (like an alias)? I

have

> > tried to add another column in both the select statements without any

> luck.

> >
> > sean
> >
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eRVhAXOPEHA.988@tk2msftngp13.phx.gbl...

> > > sean wrote:
> > > > HI Aaron,
> > > >
> > > > sorry for all the questions but if I want an extra column with the
> > > > total of Sum(OptionPrice) where abouts do I put it in the query?
> > >
> > > See below (why do you want an extra column?)
> > > >
> > > > Sean
> > > >
> > > > PARAMETERS pOrderNumber Long;
> > > > SELECT 'Details' AS Source, t1.[OptionID],
> > > > t1.[OptionValue], t1.[OptionPrice],
> > > > t1.[OrderNumber], p.[OptionID],
> > > > p.[TaxRateID], t.[TaxRate], Null Total
> > > > FROM
> > > > (tmpproductoptions t1 INNER JOIN product_options p
> > > > ON t1.[OptionID]=p.OptionID)
> > > > INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> > > > WHERE [t1].[OrderNumber]=[pOrderNumber]
> > > >
> > > > UNION ALL SELECT 'rollup', Null, Null,Null, Null,
> > > > Null,Null, Nul,lSum(OptionPrice)
> > > > FROM tmpproductoptions
> > > > WHERE [OrderNumber]=pOrderNumber
> > > > ORDER BY Source;
> > >
> > > Test it in Access.
> > >
> > > Bob Barrows
> > >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >

> >
> >

>
>

[sean]

Hi Bob,

This is what I want, the application that uses this is only small and the
fact that it is inefficient won't impact the users as such.

sean

> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> Details 96 Balloons 12 163773040 96 1 1 1.1 24

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eFYlzjOPEHA.2636@TK2MSFTNGP10.phx.gbl...

> sean wrote:

> > Hi Bob,
> >
> > I have tested it an access and it works really well thanks, all I
> > want to know if if I can add the total in an extra column (like an
> > alias)? I have tried to add another column in both the select
> > statements without any luck.
> >
> > sean

>
> I think that instead of this:
>
> Details 97 Chocolate 12 163773040 97 1 1 1.1
> Details 96 Balloons 12 163773040 96 1 1 1.1
> Rollup 24
>
> You want this?
>
> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> Details 96 Balloons 12 163773040 96 1 1 1.1 24
>
> This is possible using a correlated subquery, but it will be horribly
> inefficient, because the subquery will need to run wonce for every row of
> detail returned. Are you sure you want this? Why??
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Aaron Bertrand - MVP]

Can you explain the purpose of having the total in every single row?

I would rather do it this way:

(a) retrieve the SUM in query 1
(b) store the value in an ASP variable
(c) retrieve your other data in query 2
(d) when displaying each row, inject the variable at the end

This will be FAR more efficient and logical than executing the same query
over and over again for every row in the table. Yuck.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"sean" <sean_NO_SPAM@shopsmart.com.au> wrote in message
news:e2b$IoOPEHA.3128@TK2MSFTNGP10.phx.gbl...

> Hi Bob,
>
> This is what I want, the application that uses this is only small and the
> fact that it is inefficient won't impact the users as such.
>
> sean
>

> > Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> > Details 96 Balloons 12 163773040 96 1 1 1.1 24

>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eFYlzjOPEHA.2636@TK2MSFTNGP10.phx.gbl...

> > sean wrote:

> > > Hi Bob,
> > >
> > > I have tested it an access and it works really well thanks, all I
> > > want to know if if I can add the total in an extra column (like an
> > > alias)? I have tried to add another column in both the select
> > > statements without any luck.
> > >
> > > sean

> >
> > I think that instead of this:
> >
> > Details 97 Chocolate 12 163773040 97 1 1 1.1
> > Details 96 Balloons 12 163773040 96 1 1 1.1
> > Rollup 24
> >
> > You want this?
> >
> > Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> > Details 96 Balloons 12 163773040 96 1 1 1.1 24
> >
> > This is possible using a correlated subquery, but it will be horribly
> > inefficient, because the subquery will need to run wonce for every row

of

> > detail returned. Are you sure you want this? Why??
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>

[sean]

Hi Aaron,

(b) store the value in an ASP variable

sean


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:#Ri$4sOPEHA.3748@TK2MSFTNGP09.phx.gbl...

> Can you explain the purpose of having the total in every single row?
>
> I would rather do it this way:
>
> (a) retrieve the SUM in query 1
> (b) store the value in an ASP variable
> (c) retrieve your other data in query 2
> (d) when displaying each row, inject the variable at the end
>
> This will be FAR more efficient and logical than executing the same query
> over and over again for every row in the table. Yuck.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "sean" <sean_NO_SPAM@shopsmart.com.au> wrote in message
> news:e2b$IoOPEHA.3128@TK2MSFTNGP10.phx.gbl...

> > Hi Bob,
> >
> > This is what I want, the application that uses this is only small and

the

> > fact that it is inefficient won't impact the users as such.
> >
> > sean
> >

> > > Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> > > Details 96 Balloons 12 163773040 96 1 1 1.1 24

> >
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eFYlzjOPEHA.2636@TK2MSFTNGP10.phx.gbl...

> > > sean wrote:
> > > > Hi Bob,
> > > >
> > > > I have tested it an access and it works really well thanks, all I
> > > > want to know if if I can add the total in an extra column (like an
> > > > alias)? I have tried to add another column in both the select
> > > > statements without any luck.
> > > >
> > > > sean
> > >
> > > I think that instead of this:
> > >
> > > Details 97 Chocolate 12 163773040 97 1 1 1.1
> > > Details 96 Balloons 12 163773040 96 1 1 1.1
> > > Rollup 24
> > >
> > > You want this?
> > >
> > > Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> > > Details 96 Balloons 12 163773040 96 1 1 1.1 24
> > >
> > > This is possible using a correlated subquery, but it will be horribly
> > > inefficient, because the subquery will need to run wonce for every row

> of

> > > detail returned. Are you sure you want this? Why??
> > >
> > > Bob Barrows
> > >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >

> >
> >

>
>

[Aaron Bertrand - MVP]

Is this a question, or ...?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"sean" <sean_NO_SPAM@shopsmart.com.au> wrote in message
news:#WmeRvOPEHA.2976@TK2MSFTNGP10.phx.gbl...

> Hi Aaron,
>
> (b) store the value in an ASP variable
>
> sean

[Bob Barrows [MVP]]

Incredible, but, here you go:

PARAMETERS pOrderNumber Long;
SELECT 'Details' AS Source, t1.[OptionID],
t1.[OptionValue], t1.[OptionPrice],
t1.[OrderNumber], p.[OptionID],
p.[TaxRateID], t.[TaxRate],
(SELECT Sum(OptionPrice) FROM tmpproductoptions
WHERE [OrderNumber]= t1.[OrderNumber]) Total
FROM
(tmpproductoptions t1 INNER JOIN product_options p
ON t1.[OptionID]=p.OptionID)
INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
WHERE [t1].[OrderNumber]=[pOrderNumber]


sean wrote:

> Hi Bob,
>
> This is what I want, the application that uses this is only small and
> the fact that it is inefficient won't impact the users as such.
>
> sean
>

>> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
>> Details 96 Balloons 12 163773040 96 1 1 1.1 24

>
>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand - MVP]

Don't worry, he'll be back asking how to fix it in a week when users start
complaining that the page is crawling. And the MDB file becomes corrupt
because of all that IO to work tables... ;-)




"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#$g0NxOPEHA.3708@TK2MSFTNGP10.phx.gbl...

> Incredible, but, here you go:
>
> PARAMETERS pOrderNumber Long;
> SELECT 'Details' AS Source, t1.[OptionID],
> t1.[OptionValue], t1.[OptionPrice],
> t1.[OrderNumber], p.[OptionID],
> p.[TaxRateID], t.[TaxRate],
> (SELECT Sum(OptionPrice) FROM tmpproductoptions
> WHERE [OrderNumber]= t1.[OrderNumber]) Total
> FROM
> (tmpproductoptions t1 INNER JOIN product_options p
> ON t1.[OptionID]=p.OptionID)
> INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> WHERE [t1].[OrderNumber]=[pOrderNumber]
>
>
> sean wrote:

> > Hi Bob,
> >
> > This is what I want, the application that uses this is only small and
> > the fact that it is inefficient won't impact the users as such.
> >
> > sean
> >

> >> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> >> Details 96 Balloons 12 163773040 96 1 1 1.1 24

> >
> >

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[sean]

Thanks guys for all your help, I need to have this finshed in the morning
and you guys have helped me do this. I know that its not the way to do
things, I have just run completely out of time.

Thanks again - Sean


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#$g0NxOPEHA.3708@TK2MSFTNGP10.phx.gbl...

> Incredible, but, here you go:
>
> PARAMETERS pOrderNumber Long;
> SELECT 'Details' AS Source, t1.[OptionID],
> t1.[OptionValue], t1.[OptionPrice],
> t1.[OrderNumber], p.[OptionID],
> p.[TaxRateID], t.[TaxRate],
> (SELECT Sum(OptionPrice) FROM tmpproductoptions
> WHERE [OrderNumber]= t1.[OrderNumber]) Total
> FROM
> (tmpproductoptions t1 INNER JOIN product_options p
> ON t1.[OptionID]=p.OptionID)
> INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> WHERE [t1].[OrderNumber]=[pOrderNumber]
>
>
> sean wrote:

> > Hi Bob,
> >
> > This is what I want, the application that uses this is only small and
> > the fact that it is inefficient won't impact the users as such.
> >
> > sean
> >

> >> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> >> Details 96 Balloons 12 163773040 96 1 1 1.1 24

> >
> >

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[sean]

Hi Aaron,

I thought about what Bob and yourself said and I went back to original
solution. Thanks for all your help

sean


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:evL$A1OPEHA.2920@tk2msftngp13.phx.gbl...

> Don't worry, he'll be back asking how to fix it in a week when users start
> complaining that the page is crawling. And the MDB file becomes corrupt
> because of all that IO to work tables... ;-)
>
>
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:#$g0NxOPEHA.3708@TK2MSFTNGP10.phx.gbl...

> > Incredible, but, here you go:
> >
> > PARAMETERS pOrderNumber Long;
> > SELECT 'Details' AS Source, t1.[OptionID],
> > t1.[OptionValue], t1.[OptionPrice],
> > t1.[OrderNumber], p.[OptionID],
> > p.[TaxRateID], t.[TaxRate],
> > (SELECT Sum(OptionPrice) FROM tmpproductoptions
> > WHERE [OrderNumber]= t1.[OrderNumber]) Total
> > FROM
> > (tmpproductoptions t1 INNER JOIN product_options p
> > ON t1.[OptionID]=p.OptionID)
> > INNER JOIN TaxRate t ON p.[TaxRateID]=t.tblTaxRateID
> > WHERE [t1].[OrderNumber]=[pOrderNumber]
> >
> >
> > sean wrote:

> > > Hi Bob,
> > >
> > > This is what I want, the application that uses this is only small and
> > > the fact that it is inefficient won't impact the users as such.
> > >
> > > sean
> > >
> > >> Details 97 Chocolate 12 163773040 97 1 1 1.1 24
> > >> Details 96 Balloons 12 163773040 96 1 1 1.1 24
> > >
> > >

> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>