Not sure how to do this in Access... 1

[Turb]

I have the following table (Table1) in an Access 2003 database:
PART# WHLOC #LOCS
PART1 R1SH2
PART2 R3SH1
PART2 R1SH4
PART3 R4SH3

I need to run a query to populate the '#LOCS' field for each record with a number that indicates how many locations each part resides in and append it to 'Table2'. Like this:
PART# WHLOC #LOCS
PART1 R1SH2 1
PART2 R3SH1 1
PART2 R1SH4 2
PART3 R4SH3 1

In Excel, I would populate the #LOCS column using this formula:
=IF(A2<>A1,1,IF(A2=A1,D1+1,""))

I could export Table1, run the above formula and then import the spreadsheet in Access once again as Table2, but I'd like to accomplish this completely in Access is possible.
Can someone help me figure out how to do this?

Each part can ONLY reside in 7 locations, if it matters.

Thanks in advance!


- Turb

 

[PHV]

What about this ?

SELECT A.[PART#], A.WHLOC, Count(*) AS [#LOCS]
FROM Table1 AS A
INNER JOIN Table1 AS B ON A.[PART#] = B.[PART#] AND B.WHLOC <= A.WHLOC
GROUP BY A.[PART#], A.WHLOC

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

 

[Turb]

PH, thank you for your reply!
Question: Should the 'INNER JOIN' in your SQL sample be to 'Table2'?


- Turb

 

[Turb]

Ugh...
That will teach me to question something without trying the solution first!!
PH, your sample works perfectly!
Thank you!


- Turb