Increment up to certain number

  • Thread starter Thread starter Job
  • Start date Start date
J

Job

I have a table that has x number of weeks of data. I have a query that
counts the unique weeks, say 31. I want to create a drop down on a form that
shows 1, 2, 3...etc up to the calculated number of weeks, or 31 in the above
example.

Cheers,
Job
 
Does your table have more than one row of data per week?
How do you identify the week? ( is it week number, date, etc)
Does your query return just the number of weeks, or does it return a
distinct row for each week?

My last question may be your answer. You could create a query that would
return the unique identifier for each week and use the query as your row
source.
 
Yes, my table has more than one row of data per week. To identify the week,
it's actually a week ending date. The main table has a date field, I match
that date field to a table that has all of the dates and the week ending
date for each date. I'm counting the unique week ending dates in the data
to get the number of weeks available. The query returns 31, not a distinct
for each week.

Thanks for your help.
Job
 
Okay, we can do this with an SQL statment as the row source for the combo box:

"SELECT TOP 31 MyTableName.WeekEndDate FROM MyTableName GROUP BY
MyTableName.WeekEndDate;"

If there are fewer than 31 weeks, it will only return the number of weeks it
finds. One thing I am not sure of is whether it needs the ; at the end of
the statement or not.
 
Back
Top