calculate no of weeks used

[mrchinchin25]

I have a table with data like this: (this is abbreviated)

Part Number, Week Number
A1 52
A2 1
A3 1
A4 1
A5 2
A6 2
A7 3

I need some way using SQL or a query to get the system to work out how to calculate how many different weeks we have data for.
In the case above we have 4 different weeks.

as the user will be adding new data each week, this system has to work out automatically.

can anyone help me calculate the number of weeks?

 

[KenReay]

HI

You need to use Group By and Count() see Access Help Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[checkOut]

On the fly, but it will work
Public Function CountWeekNum
dim rst as new adodb.recordset
dim cnn as adodb.connection
set cnn = currentproject.connection
dim strSQL as string
strSQL = "Select DISTINCT Week From table"
rst.open strSQl, cnn, adopenkeyset, adlockreadonly
rst.movelast
rst.movefirst
CountWeeknum = rst.recordcount

End Function

Good luck, come back when not sure about it.