?
Solved

Need a incremental value in Access query

Posted on 2011-09-07
19
Medium Priority
?
282 Views
Last Modified: 2012-05-12
I have a few Access queries where I need to have a  incremental value for every record that is fetched. I need this in SQL language, not in VBA.

Say if I have 100 records returned I want a field that will count from 1 to 100

Can some one help me on this?
0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +2
19 Comments
 
LVL 58
ID: 36495337

 You need a field that is comparable and use a sub select.  For example:

SELECT CustCode, CustName,
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode <= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

Jim.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36495374
To do this in SQL you must have a unique ordering key in your data.
The method just counts the number of records less than or equal to the current ordering key value.


Select T.*, (select count(*) from tablename where keyfield<= T.keyfield) as Kounter
from tablename as T
Order By Keyfield
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36495406
you will need a unique record id to do this

select Top 100 uniqueID,(select count(*) from tableX T where T.uniqueID < tableX.UniqueID) + 1
from tableX
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:Gerhardpet
ID: 36495443
I have this query now and it does not work. I'm not sure what I'm doing wrong
SELECT INVENTORY.*
Expr1: (SELECT COUNT(*) FROM [INVENTORY]![CODE]  WHERE  [INVENTORY]![CODE] <= [INVENTORY]![CODE] )
FROM INVENTORY;

Open in new window


This will be part of a more complex formula. The above is just for testing this
0
 
LVL 58
ID: 36495463

  You need an ORDER BY on Code

Jim.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36495533
Still not working
SELECT INVENTORY.*
Expr1: (SELECT COUNT(*) FROM [INVENTORY]![CODE]  WHERE  [INVENTORY]![CODE] <= [INVENTORY]![CODE] )
FROM INVENTORY;
ORDER BY [INVENTORY]![CODE]

Open in new window


I get this error
www.iversa.ca/files/Error.jpg

(for some reason I can upload any files to EE today...hence the link above)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36495548
SELECT tbl.*,
 (SELECT COUNT(*) FROM [INVENTORY]  WHERE  [INVENTORY].[C ODE] <= [tbl].[C ODE] ) as Kounter
FROM INVENTORY as tbl
Order By tbl.[C ode];


(Remove space from [c ode]  - EE uses this word as a keyword)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36495559
change your query to this


SELECT INVENTORY.*
(SELECT COUNT(*) FROM [INVENTORY] As I  WHERE  I![CODE] <= [INVENTORY]![CODE] )
FROM INVENTORY
ORDER BY [INVENTORY]![CODE]

Open in new window

0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36495596
Capriconrn1,
I tried your query and get the same error
0
 
LVL 58
ID: 36495613
SELECT INVENTORY.*
RowCount: (SELECT COUNT(*) FROM [INVENTORY].[C]  I2 WHERE  I2.[C] <= [INVENTORY].[C] )
FROM INVENTORY;
ORDER BY [INVENTORY].[C]

 Change all the [C] to Code with brackets.

Jim.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36495655
This is how:

SELECT INVENTORY.*
(SELECT COUNT(*) FROM INVENTORY AS I  WHERE  I.CODE <= INVENTORY.CODE ) AS ItemCount
FROM INVENTORY
ORDER BY INVENTORY.CODE

But why not VBA? Way faster!

/gustav
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36495657
Jim,
I wish this would work already but no go :-)
 
SELECT INVENTORY.*
RowCount: (SELECT COUNT(*) FROM [INVENTORY].[CODE]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] )
FROM INVENTORY;
ORDER BY [INVENTORY].[CODE]

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36495733
copy and paste this


SELECT INVENTORY.*
 (SELECT COUNT(*) FROM [INVENTORY].[CODE]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] ) As RowCount
FROM INVENTORY
ORDER BY [INVENTORY].[CODE]

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36495738
I don't understand where you are getting your sql code from (the code you are posting) - it is not what Access would give you in the sql view of a query.

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 36495741
sorry use this one, copy and paste



SELECT INVENTORY.*
 (SELECT COUNT(*) FROM [INVENTORY]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] ) As RowCount
FROM INVENTORY
ORDER BY [INVENTORY].[CODE]

Open in new window





0
 
LVL 58
ID: 36495746

 semi-colon is in the wrong place; needs to be after the ORDER BY.  Other then that, looks fine.

Jim.
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 total points
ID: 36495766
Jim,
I wish this would work already but no go :-)
 
FROM INVENTORY;

No semicolon at that place.

/gustav
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36495871
This works like this but as soon as I add more fields to it then I get the error again

SELECT INVENTORY.CODE
 (SELECT COUNT(*) FROM [INVENTORY]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] ) As RowCount
FROM INVENTORY
ORDER BY [INVENTORY].[CODE];

Open in new window


This does not work
 
SELECT INVENTORY.*
 (SELECT COUNT(*) FROM [INVENTORY]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] ) As RowCount
FROM INVENTORY
ORDER BY [INVENTORY].[CODE];

Open in new window


My query I want to use this in is this here
 
SELECT INVENTORY.CODE AS SKU, INVENTORY.INV_DESCRIPTION, INVENTORY.PROD AS PROD_CODE, UNIT_OF_MEASURE.CODE AS UOM_CODE, UNIT_OF_MEASURE.UOM_DESCRIPTION AS UOM_DESCRIPTION, UNIT_OF_MEASURE.WEIGHT, INVE_WB_MAIN.MFG, INVE_WB_META_TITLE.MASK AS META_TITLE, INVE_WB_META_KEYWORDS.MEMO AS META_KEYWORD, INVE_WB_META_DESCRIPTION.MEMO AS META_DESCRIPTION, INVE_WB_META_DESCRIPTION.MEMO AS DESCRIPTION, INVE_WB_SHORT_DESCRIPTION.MEMO AS SHORT_DESCRIPTION, INVE_WB_MIN_QTY.MASK AS MIM_QTY, INVE_WB_MAX_QTY.MASK AS MAX_QTY, UNIT_OF_MEASURE.ALLOW_FRACTIONAL_QTY AS DECIMALS_QTY, INVE_WB_NOTIFY_QTY.MASK AS NOTIFY_QTY, INVE_WB_IINCR_QTY.MASK AS INCR_QTY, INVE_WB_ALT_PROD_1.MASK AS ALT_PROD_1, INVE_WB_ALT_PROD_2.MASK AS ALT_PROD_2, IIf([PROD_CODE_WB_MAIN]![MAG_CAT]=True,[PRODUCT_CODE]![PRD_DESC]) AS CATAGORY, INVENTORY.BVRVMODDATE AS DATE_MODIFIED, INVENTORY.BVRVMODTIME AS TIME_MODIFIED, INVE_WB_MAIN.LAST_CHANGED_DATE AS DATE_MODIFIED_CP
FROM ((((((((((((((INVENTORY LEFT JOIN UNIT_OF_MEASURE ON (INVENTORY.CODE = UNIT_OF_MEASURE.PART_NO) AND (INVENTORY.WHSE = UNIT_OF_MEASURE.WHSE_NO)) LEFT JOIN PRICING ON (UNIT_OF_MEASURE.PRICESOURCECONST = PRICING.BVSPECPRICESOURCEID) AND (UNIT_OF_MEASURE.CODE = PRICING.BVSPECPRICEUOM) AND (UNIT_OF_MEASURE.PART_NO = PRICING.BVSPECPRICEPARTNO) AND (UNIT_OF_MEASURE.WHSE_NO = PRICING.BVSPECPRICEWHSE)) LEFT JOIN INVE_WB_MAIN ON (INVENTORY.CODE = INVE_WB_MAIN.I_PART_NO) AND (INVENTORY.WHSE = INVE_WB_MAIN.I_WHSE)) LEFT JOIN INVE_WB_META_TITLE ON (INVE_WB_MAIN.LINK_METATITLE = INVE_WB_META_TITLE.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_TITLE.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_TITLE.I_WHSE)) LEFT JOIN INVE_WB_META_KEYWORDS ON (INVE_WB_MAIN.LINK_METAKEYW = INVE_WB_META_KEYWORDS.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_KEYWORDS.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_KEYWORDS.I_WHSE)) LEFT JOIN INVE_WB_META_DESCRIPTION ON (INVE_WB_MAIN.LINK_METADESC = INVE_WB_META_DESCRIPTION.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_DESCRIPTION.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_DESCRIPTION.I_WHSE)) LEFT JOIN INVE_WB_SHORT_DESCRIPTION ON (INVE_WB_MAIN.LINK_DESC = INVE_WB_SHORT_DESCRIPTION.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_SHORT_DESCRIPTION.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_SHORT_DESCRIPTION.I_WHSE)) LEFT JOIN INVE_WB_MIN_QTY ON (INVE_WB_MAIN.LINK_MINQTY = INVE_WB_MIN_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_MIN_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_MIN_QTY.I_WHSE)) LEFT JOIN INVE_WB_MAX_QTY ON (INVE_WB_MAIN.LINK_MAXQTY = INVE_WB_MAX_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_MAX_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_MAX_QTY.I_WHSE)) LEFT JOIN INVE_WB_NOTIFY_QTY ON (INVE_WB_MAIN.LINK_NOTIFYQTY = INVE_WB_NOTIFY_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_NOTIFY_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_NOTIFY_QTY.I_WHSE)) LEFT JOIN INVE_WB_ALT_PROD_1 ON (INVE_WB_MAIN.LINK_ALTPROD_1 = INVE_WB_ALT_PROD_1.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_ALT_PROD_1.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_ALT_PROD_1.I_WHSE)) LEFT JOIN INVE_WB_ALT_PROD_2 ON (INVE_WB_MAIN.LINK_ALTPROD_2 = INVE_WB_ALT_PROD_2.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_ALT_PROD_2.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_ALT_PROD_2.I_WHSE)) LEFT JOIN INVE_WB_IINCR_QTY ON (INVE_WB_MAIN.LINK_INCRQTY = INVE_WB_IINCR_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_IINCR_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_IINCR_QTY.I_WHSE)) LEFT JOIN PRODUCT_CODE ON INVENTORY.PROD = PRODUCT_CODE.CODE) LEFT JOIN PROD_CODE_WB_MAIN ON PRODUCT_CODE.CODE = PROD_CODE_WB_MAIN.PROD_CODE
WHERE (((INVENTORY.WHSE)="00") AND ((INVE_WB_MAIN.M_ITEM)=True));

Open in new window

0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36497630
Thank you for all the help. I have it working now

Here is the working query
SELECT (SELECT COUNT(*) FROM [INVENTORY]  I2 WHERE  I2.[CODE] <= [INVENTORY].[CODE] ) AS COUNTID, INVENTORY.CODE AS SKU, INVENTORY.INV_DESCRIPTION, INVENTORY.PROD AS PROD_CODE, UNIT_OF_MEASURE.CODE AS UOM_CODE, UNIT_OF_MEASURE.UOM_DESCRIPTION AS UOM_DESCRIPTION, UNIT_OF_MEASURE.WEIGHT, INVE_WB_MAIN.MFG, INVE_WB_META_TITLE.MASK AS META_TITLE, INVE_WB_META_KEYWORDS.MEMO AS META_KEYWORD, INVE_WB_META_DESCRIPTION.MEMO AS META_DESCRIPTION, INVE_WB_META_DESCRIPTION.MEMO AS DESCRIPTION, INVE_WB_SHORT_DESCRIPTION.MEMO AS SHORT_DESCRIPTION, INVE_WB_MIN_QTY.MASK AS MIM_QTY, INVE_WB_MAX_QTY.MASK AS MAX_QTY, UNIT_OF_MEASURE.ALLOW_FRACTIONAL_QTY AS DECIMALS_QTY, INVE_WB_NOTIFY_QTY.MASK AS NOTIFY_QTY, INVE_WB_IINCR_QTY.MASK AS INCR_QTY, INVE_WB_ALT_PROD_1.MASK AS ALT_PROD_1, INVE_WB_ALT_PROD_2.MASK AS ALT_PROD_2, IIf([PROD_CODE_WB_MAIN]![MAG_CAT]=True,[PRODUCT_CODE]![PRD_DESC]) AS CATAGORY, INVENTORY.BVRVMODDATE AS DATE_MODIFIED, INVENTORY.BVRVMODTIME AS TIME_MODIFIED, INVE_WB_MAIN.LAST_CHANGED_DATE AS DATE_MODIFIED_CP
FROM ((((((((((((((INVENTORY LEFT JOIN UNIT_OF_MEASURE ON (INVENTORY.CODE = UNIT_OF_MEASURE.PART_NO) AND (INVENTORY.WHSE = UNIT_OF_MEASURE.WHSE_NO)) LEFT JOIN PRICING ON (UNIT_OF_MEASURE.PRICESOURCECONST = PRICING.BVSPECPRICESOURCEID) AND (UNIT_OF_MEASURE.CODE = PRICING.BVSPECPRICEUOM) AND (UNIT_OF_MEASURE.PART_NO = PRICING.BVSPECPRICEPARTNO) AND (UNIT_OF_MEASURE.WHSE_NO = PRICING.BVSPECPRICEWHSE)) LEFT JOIN INVE_WB_MAIN ON (INVENTORY.CODE = INVE_WB_MAIN.I_PART_NO) AND (INVENTORY.WHSE = INVE_WB_MAIN.I_WHSE)) LEFT JOIN INVE_WB_META_TITLE ON (INVE_WB_MAIN.LINK_METATITLE = INVE_WB_META_TITLE.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_TITLE.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_TITLE.I_WHSE)) LEFT JOIN INVE_WB_META_KEYWORDS ON (INVE_WB_MAIN.LINK_METAKEYW = INVE_WB_META_KEYWORDS.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_KEYWORDS.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_KEYWORDS.I_WHSE)) LEFT JOIN INVE_WB_META_DESCRIPTION ON (INVE_WB_MAIN.LINK_METADESC = INVE_WB_META_DESCRIPTION.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_META_DESCRIPTION.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_META_DESCRIPTION.I_WHSE)) LEFT JOIN INVE_WB_SHORT_DESCRIPTION ON (INVE_WB_MAIN.LINK_DESC = INVE_WB_SHORT_DESCRIPTION.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_SHORT_DESCRIPTION.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_SHORT_DESCRIPTION.I_WHSE)) LEFT JOIN INVE_WB_MIN_QTY ON (INVE_WB_MAIN.LINK_MINQTY = INVE_WB_MIN_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_MIN_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_MIN_QTY.I_WHSE)) LEFT JOIN INVE_WB_MAX_QTY ON (INVE_WB_MAIN.LINK_MAXQTY = INVE_WB_MAX_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_MAX_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_MAX_QTY.I_WHSE)) LEFT JOIN INVE_WB_NOTIFY_QTY ON (INVE_WB_MAIN.LINK_NOTIFYQTY = INVE_WB_NOTIFY_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_NOTIFY_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_NOTIFY_QTY.I_WHSE)) LEFT JOIN INVE_WB_ALT_PROD_1 ON (INVE_WB_MAIN.LINK_ALTPROD_1 = INVE_WB_ALT_PROD_1.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_ALT_PROD_1.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_ALT_PROD_1.I_WHSE)) LEFT JOIN INVE_WB_ALT_PROD_2 ON (INVE_WB_MAIN.LINK_ALTPROD_2 = INVE_WB_ALT_PROD_2.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_ALT_PROD_2.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_ALT_PROD_2.I_WHSE)) LEFT JOIN INVE_WB_IINCR_QTY ON (INVE_WB_MAIN.LINK_INCRQTY = INVE_WB_IINCR_QTY.FIELD_NAME) AND (INVE_WB_MAIN.I_PART_NO = INVE_WB_IINCR_QTY.I_PART_NO) AND (INVE_WB_MAIN.I_WHSE = INVE_WB_IINCR_QTY.I_WHSE)) LEFT JOIN PRODUCT_CODE ON INVENTORY.PROD = PRODUCT_CODE.CODE) LEFT JOIN PROD_CODE_WB_MAIN ON PRODUCT_CODE.CODE = PROD_CODE_WB_MAIN.PROD_CODE
WHERE (((INVENTORY.WHSE)="00") AND ((INVE_WB_MAIN.M_ITEM)=True));

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question