Creating a column in a query that increases in value numerically

J

Julia Boswell

Hi

I've got the following query:

SELECT QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional, QuoteItems.Price,
Quotes.ROE, [Price]*[Roe] AS USDPrice, [Qty]*[USDPrice] AS TotalValue
FROM Quotes INNER JOIN QuoteItems ON Quotes.RecordID = QuoteItems.QuoteID
GROUP BY QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional, QuoteItems.Price,
Quotes.ROE, [Price]*[Roe], [Qty]*[USDPrice]
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

I want to add an extra column (Item Number) that gives each record a
numerical value starting at 1 and incrementing by 1 for each record.

Is that possible in a query?

Thanks

Julia
 
J

John Spencer

Yes, it is possible - you would use a ranking subquery. However, you need
to be able to specify a unique order of the records.


Why do you want to do this? If you want to do it for a report, it is much
simpler to add a control to the report's detail section, set its source to
"=1" and then set its running sum property to Overall.

The following MIGHT be what you want if you have to do this in a query.
I've taken a wild guess that subHeading and PN will determine a unique
record

SELECT QuoteItems.SubHeading
, QuoteItems.Qty
, QuoteItems.PN
, QuoteItems.Desc
, QuoteItems.LeadTime
, QuoteItems.Optional
, QuoteItems.Price
, Quotes.ROE
, [Price]*[Roe] AS USDPrice
, [Qty]*[USDPrice] AS TotalValue
, (SELECT Count(*)
FROM Quotes as q INNER JOIN QuoteItems as qi
ON q.RecordID = qi.QuoteID
WHERE qi.subheading <= quoteItems.SubHeading
AND qi.Pn <=quoteItems.PN) as PositionCount
FROM Quotes INNER JOIN QuoteItems
ON Quotes.RecordID = QuoteItems.QuoteID
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Julia Boswell

It's not for a report. I'm accessing an Access database through an ASP.Net
application and I want the records displayed on a webpage to have an item
number, which isn't stored in the backend table.

The unique id of the record is actually field recordID. Would that make any
difference to the syntax?

Thanks for your help on this one.

Julia
John Spencer said:
Yes, it is possible - you would use a ranking subquery. However, you need
to be able to specify a unique order of the records.


Why do you want to do this? If you want to do it for a report, it is much
simpler to add a control to the report's detail section, set its source to
"=1" and then set its running sum property to Overall.

The following MIGHT be what you want if you have to do this in a query.
I've taken a wild guess that subHeading and PN will determine a unique
record

SELECT QuoteItems.SubHeading
, QuoteItems.Qty
, QuoteItems.PN
, QuoteItems.Desc
, QuoteItems.LeadTime
, QuoteItems.Optional
, QuoteItems.Price
, Quotes.ROE
, [Price]*[Roe] AS USDPrice
, [Qty]*[USDPrice] AS TotalValue
, (SELECT Count(*)
FROM Quotes as q INNER JOIN QuoteItems as qi
ON q.RecordID = qi.QuoteID
WHERE qi.subheading <= quoteItems.SubHeading
AND qi.Pn <=quoteItems.PN) as PositionCount
FROM Quotes INNER JOIN QuoteItems
ON Quotes.RecordID = QuoteItems.QuoteID
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Julia Boswell said:
Hi

I've got the following query:

SELECT QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional,
QuoteItems.Price, Quotes.ROE, [Price]*[Roe] AS USDPrice, [Qty]*[USDPrice]
AS TotalValue
FROM Quotes INNER JOIN QuoteItems ON Quotes.RecordID = QuoteItems.QuoteID
GROUP BY QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional,
QuoteItems.Price, Quotes.ROE, [Price]*[Roe], [Qty]*[USDPrice]
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

I want to add an extra column (Item Number) that gives each record a
numerical value starting at 1 and incrementing by 1 for each record.

Is that possible in a query?

Thanks

Julia
Click to expand...
Click to expand...
 
J

John Spencer

Julia,
I can't tell. If the two fields you are sorting on determine a unique order
for the data you are displaying then the SQL should work. Did you try it
and if so what results did you see.

You may have to add another level of sorting based on the primary keys and
then include that sorting in your main query also.

SELECT QuoteItems.SubHeading
, QuoteItems.Qty
, QuoteItems.PN
, QuoteItems.Desc
, QuoteItems.LeadTime
, QuoteItems.Optional
, QuoteItems.Price
, Quotes.ROE
, [Price]*[Roe] AS USDPrice
, [Qty]*[USDPrice] AS TotalValue
, (SELECT Count(*)
FROM Quotes as q INNER JOIN QuoteItems as qi
ON q.RecordID = qi.QuoteID
WHERE qi.subheading <= quoteItems.SubHeading
AND qi.Pn <=quoteItems.PN
AND q.RecordID <= Quotes.RecordID
AND qi.PrimaryKey < QuoteItems.PrimaryKey) as PositionCount
FROM Quotes INNER JOIN QuoteItems
ON Quotes.RecordID = QuoteItems.QuoteID
ORDER BY QuoteItems.SubHeading, QuoteItems.PN, Quotes.RecordID,
QuoteItems.PRIMARYKEY

Be prepared for that to be slow, if it works at all.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Julia Boswell said:
It's not for a report. I'm accessing an Access database through an ASP.Net
application and I want the records displayed on a webpage to have an item
number, which isn't stored in the backend table.

The unique id of the record is actually field recordID. Would that make
any difference to the syntax?

Thanks for your help on this one.

Julia
John Spencer said:
Yes, it is possible - you would use a ranking subquery. However, you
need to be able to specify a unique order of the records.


Why do you want to do this? If you want to do it for a report, it is
much simpler to add a control to the report's detail section, set its
source to "=1" and then set its running sum property to Overall.

The following MIGHT be what you want if you have to do this in a query.
I've taken a wild guess that subHeading and PN will determine a unique
record

SELECT QuoteItems.SubHeading
, QuoteItems.Qty
, QuoteItems.PN
, QuoteItems.Desc
, QuoteItems.LeadTime
, QuoteItems.Optional
, QuoteItems.Price
, Quotes.ROE
, [Price]*[Roe] AS USDPrice
, [Qty]*[USDPrice] AS TotalValue
, (SELECT Count(*)
FROM Quotes as q INNER JOIN QuoteItems as qi
ON q.RecordID = qi.QuoteID
WHERE qi.subheading <= quoteItems.SubHeading
AND qi.Pn <=quoteItems.PN) as PositionCount
FROM Quotes INNER JOIN QuoteItems
ON Quotes.RecordID = QuoteItems.QuoteID
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Julia Boswell said:
Hi

I've got the following query:

SELECT QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional,
QuoteItems.Price, Quotes.ROE, [Price]*[Roe] AS USDPrice,
[Qty]*[USDPrice] AS TotalValue
FROM Quotes INNER JOIN QuoteItems ON Quotes.RecordID =
QuoteItems.QuoteID
GROUP BY QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN,
QuoteItems.Desc, QuoteItems.LeadTime, QuoteItems.Optional,
QuoteItems.Price, Quotes.ROE, [Price]*[Roe], [Qty]*[USDPrice]
ORDER BY QuoteItems.SubHeading, QuoteItems.PN;

I want to add an extra column (Item Number) that gives each record a
numerical value starting at 1 and incrementing by 1 for each record.

Is that possible in a query?

Thanks

Julia
Click to expand...
Click to expand...
Click to expand...
 
Top