most efficient way to achieve structure ?

[scoob8254]

hello, ive got a problem im wondering if any1 can help me with...

im trying to achieve data to be displayed in either a query or a table in a certain way... atm im using a huge peice of code to sort the data and insert it into a temp table (which still doesnt workk 100% yet) to achieve what im going to ask.. im finding it hard to discribe what im trying to achieve but here goes...

basicaly i have two tables..
first table1 is

-------------------------------------------------------
|username|project1ref|project2ref|project3ref|project4ref|p1startdate|p1enddate| etc etc

table2 is like
-----------------------------------------------------------------
|projectref|projectname|other details etc

i need something to look at the two tables, and either produce a query or table, with this structure

---------------------------------------------------------------
|username|projectname|week|TrueFalse|

the username and the project name are self explanitory. its the week and true or false bit thats getting me

i need it to create a list basicaly but starting from a predefined date test if project 1 would be active on that date and if it is then input true in the truefalse column, then for the same project on the next row move forward 1 week, put that week in the date column and then again test if that project is active that week,

need it to do this 27 times so, basicaly i get 27 dates 1 week apart for project1 testing each time if its active that week.. then once thats done, move onto project2 for that user, then project 3 and so on, THEN do the same for the next user and so on

end up with what could be called a kind of gantt type list, but not

the reason im asking is im wondering id their isnt any easier more efficient way to achieve this, as the code im writing is getting kinda long winded...

any pointers in the right direction much appeicated
scoobs

 

[vbaInet]

Here's the idea.

You need to GROUP BY the username and then for you to get the increment of weeks you need a running count (search the forum for this). You would then use the number you get from the running count in a function called DATEADD() to get the date.

But you haven't explained what criteria will be used for checking for active projects. Should it check against a certain field?

 

[scoob8254]

sorry lol im rubish at explaining myself

the fields p1startdate|p1enddate

are project1 start date and project1 end date and their are similar fields for the other projects

the incremented date is checked against these two dates and if it site between the start date and end date then its still active...

 

[vbaInet]

That last bit shouldn't be too much of a problem. Have you found the thread on performing a running count?

 

[scoob8254]

my current code has a running count and is now working to a certain extent, need to tweak it when its working out if a date is active as would like it to take into account the entire week not just that particular day..

just i feel the code is so big and cumbersome (and lazy, lots of if's) their has to be a smarter way, mainly wondering how other poeple would achieve the same

 

[vbaInet]

What did you call the field that does the running count?

 

[scoob8254]

fldWeek i think

 

[vbaInet]

Something like:

P1IsActive: IIF(DateAdd("ww", fldWeek, [DateField]) BETWEEN p1startdate AND p1endDate, True, False)

 

[scoob8254]

thanks for your help... ive finaly achieved what i wantedf with your help and a few others on different subjects

cheers

 

[vbaInet]

Excellent! Thanks for informing us of your success.