0

I am trying to use a query to create a Bill of Materials list that gives an item number to a material. I have a column for Material and BOMPart. The Material column gives a list of item numbers that repeat for each item in the Bill of Materials. The BOMPart column lists each part of the item in Material. So Material 1 is created using items a, b, and c. I would like the third column, ItemNo, to start at 1 for each Material and count each BOMPart associated to the material. It should then reset to 1 for the next Material. Any suggestions? I am still pretty new to Access. Here is what I would like the columns to look like, I replaced my actual material numbers for simplicity.
Material BOMPart ItemNo
1 a 1
1 b 2
1 c 3
2 a 1
2 f 2
3 g 1
3 h 2
3 i 3
4 k 1
4 m 2
CJJones
  • 1
  • 2

1 Answers1

0

You want to use row_number for this. I am not certain this works in Access.

select Material
   ,    BOMPart
   ,    Row_Number() over (partition by Material order by BOMPart)
from xxxx
KeithL
  • 5,348
  • 3
  • 19
  • 25
  • Access does not have a row_number() function. Thank you for trying though. – CJJones May 21 '21 at 16:57
  • I've only gotten something like this in Access by creating a public VBA function that saves the previous Material value and checks it to return 1 or previous + 1. Call that function in your query. – Chris Maurer May 22 '21 at 13:44