increment a field in grouping 1

[GelC]

Hi forum,
I have tblGroup, tblItem. There a relationship bteween these two tables. Below is my desire in tblItem, Order# field is to be incremented by Grouping. How do I make this happen?
Thanks in advanced.

------------------------------
ItemID GroupIDFK Order#
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 2
7 2 3
8 3 1
9 4 1
10 4 2
-------------------------------

 

[PHV]

You want a ranking query ?
SELECT A.ItemID, A.GroupIDFK,
(SELECT Count(*) FROM tblItem WHERE GroupIDFK=A.GroupIDFK AND ItemID<=A.ItemID) AS [Order#]
FROM tblItem AS A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GelC]

Thanks PHV for your response,
That works great!!!

 

[GelC]

I forgot to mention to you that I run ranking in query.
How can I pass values of Order# into tblItem?
Thanks again

 

[PHV]

One way:
UPDATE tblItem
SET [Order#] = DCount("*","tblItem","GroupIDFK=" & [GroupIDFK] & " AND ItemID<=" & [ItemID])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GelC]

I'm not sure where to put the code in your suggestion.
Thanks for your patient

 

[PHV]

This is the SQL code of an UPDATE query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GelC]

Life is so beautiful when there are so many nice souls as you are, PHV.
Thank you very, very, very much for your help

 

[GelC]

Can anyone please tell me how to keep this query update automatically? I just know that I have to update every time there is new records enter.
Thanks for any help

GelC