[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Need a incremental value in Access query

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
Gerhardpet
Asked:
Gerhardpet
  • 6
  • 4
  • 4
  • +2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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
 
peter57rCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GerhardpetAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  You need an ORDER BY on Code

Jim.
0
 
GerhardpetAuthor Commented:
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
 
peter57rCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
GerhardpetAuthor Commented:
Capriconrn1,
I tried your query and get the same error
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Gustav BrockCIOCommented:
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
 
GerhardpetAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
peter57rCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

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

Jim.
0
 
Gustav BrockCIOCommented:
Jim,
I wish this would work already but no go :-)
 
FROM INVENTORY;

No semicolon at that place.

/gustav
0
 
GerhardpetAuthor Commented:
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
 
GerhardpetAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now