Hmmm so is there a way to concat and use the AS to get what i want??
doesn't look like it - you would need a criteria of 50 'OR's deep and 2 x 50 nested iifs (and I don't think you can have that many) and still not sure that is what you want
probably not doable, you would need something like this
Code:
SELECT Project, iif(wk1 between thisdate and thatdate,wk1,iif(wk2 between thisdate and thatdate,wk2,iif(.... AS WeekDate, iif(wk1 between thisdate and thatdate,1,iif(wk2 between thisdate and thatdate,2,iif(.... AS WeekNo
FROM myTable
WHERE
wk1 between thisdate and thatdate OR
wk2 between thisdate and thatdate OR
wk3 between thisdate and thatdate OR
wk4 between thisdate and thatdate OR...
better to sort your data correctly as suggested by Ranman
create a new table as he suggests
then this query
Code:
INSERT INTO newtable (Project, WeekDate, Weekno)
SELECT Project, wk1,1
FROM oldtable
WHERE wk1 is not null
run it then change the bits in red to wk2 and rerun again - do for 50 times
once done your data will be normalised and your query would simply be
Code:
SELECT *
FROM newtable
WHERE weekdate between thisdate and thatdate