Date Range Info From Multiple Fields

[NDNobbs]

I want to set up a query that will allow me to enter a begin and end date,
but will pull the dates from data in 10 separate fields.

i.e., The database has dates listed for Wk 1 through Wk10. Not all projects
will start in the same week, so Wk1 could start anywhere within the year, but
I need to pull data into a report based on the dates in all of these fields.
(Wk 3 could be the start of one project and Wk 5 of another project could be
the same week.)

What is the best way to query this? Or is there a better way to get the job
done?

Thanks!

 

[Lord Kelvan]

yeah there is dont have 10 columns have 1 date column and 1 column
stating the week it belongs to.

ie

projectstart projectweek
01/01/2008 week1


to query it you will have to put the date range on an or line of each
column

sql would be

select * from project
where [wk 1] between [enter start date] and [enter end date]
or [wk 2] between [enter start date] and [enter end date]
or [wk 3] between [enter start date] and [enter end date]
or [wk 4] between [enter start date] and [enter end date]
or [wk 5] between [enter start date] and [enter end date]
or [wk 6] between [enter start date] and [enter end date]
or [wk 7] between [enter start date] and [enter end date]
or [wk 8] between [enter start date] and [enter end date]
or [wk 9] between [enter start date] and [enter end date]
or [wk 10] between [enter start date] and [enter end date];

is there any real reason you have 10 columns

Regards
Kelvan

 

[Jerry Whittle]

That you have the dates in 10 different fields is the problem. Instead you
should have one date field and another field that describes what is happening
in that date field. Something like:

TheProject TheWeek
ABC 1/1/2008
ABC 1/7/2008
XYZ 1/1/2008
XYZ 2/1/2008

Then you could easily ask for the dates and projects.

 

[NDNobbs]

On the form we wanted to have all project info at a glance; Proj Name, Proj
Team Lead, Start Date, Quote # and the 10 individual wks showing when
payments are made. Each week has the date, # of hours and amount paid (this
is a calculation of # of hours * a dollar amt), beginning and end balance.

If you know a better way, please let me know. Thanks!