Need help badly!

[David_Skov]

I have created a database and all new rows that will be added can be done by simply importing new data. But a single coloum needs to be recalculated each week.

I have a Year To Data (YTD) coloum and it changes from week to week. So lets say it's week 20 and that I have data for all 2003 and 20 weeks in 2004. I have a coloum in my access database which indicates if the data is YTD or not. To compare 2003 and 2004 I need to see total costs in 2003 YTD (total costs from week 1 to 20 in 2003) compared to total cost in 2004 YTD (Total costs from week 1 to 20 in 2004).

In a excel sheet I can add a formula that makes sure that the YTD flag changes each week. So when we reach week 21 I have to compare YTD 2003 vs. YTD 2004 (now week 1 to 21)

Is there anyway that I access can handle this?

In excel I use this formula:

If(T$1<=$BB$1;"YTD";"-")

In excel BB1 = the week now. So if we are in week 21 then BB1 = 21. Since I need to handle huge amount of data I'm looking into moving data into a database, but I can't seem to find any ways to handle this problem.

Plz help and plz tell if you don't know what I mean

For now my database contains 16000+ rows and 28 coloums.

The idea is to make it dynamical. Now we can only handle 2 years at a time in excel. With Access we can handle 100 years. For now the only use is to make Pivot tables in excel. But the smart thing is that I can have more years in access and even better, link the data with other databases

 

[bat17]

In Access you can do similar things; you would not do it in the table but in a query.
A lot will depend on your data structure, if you store dates Access can calculate the week numbers for you so that you don’t need to store them.

Rather than a YTD flag I normaly have a form with a field that I can add the week number that I am interested and set the criteria to work from this. Doing it this way has the advantage of letting you pick the period to use and you can always set it to default to YTD.

Still to answer your question, Add a new column in a query and use the following formula
YTD: IIf([WeekNum]<=DatePart("ww",Date()),"YTD","-")

This assumes that you have a field called WeekNum storing your week number. If you look in help you will find that there are a couple of options with the DatePart() function that will let you set it to give the correct week number for your locale


HTH

Peter

edited to correct spelling

 

[David_Skov]

In Access you can do similar things; you would not do it in the table but in a query.
A lot will depend on your data structure, if you store dates Access can calculate the week numbers for you so that you don’t need to store them.

Rather than a YTD flag I normaly have a form with a field that I can add the week number that I am interested and set the criteria to work from this. Doing it this way has the advantage of letting you pick the period to use and you can always set it to default to YTD.

Still to answer your question, Add a new column in a query and use the following formula
YTD: IIf([WeekNum]<=DatePart("ww",Date()),"YTD","-")

This assumes that you have a field called WeekNum storing your week number. If you look in help you will find that there are a couple of options with the DatePart() function that will let you set it to give the correct week number for your locale


HTH

Peter

edited to correct spelling

I have the week number coloum. As I say I have used this in Excel until now to make pivottables in which this info about YTD was needed. I only imported the data from Excel into Access 2002, and use it to make the same pivottables as before. So when you talk about making a query, I don't see how that info is added to my database? (the query you are suggesting) How do I use this when I link to my Pivottable? I'm quiet new to this btw

 

[David_Skov]

This is weird

If I enter a criteria in a query like this:

IIf([Gallup]![Uge]<=36;"YTD";"-")

It hide rows were between 31 and 36?

I then make a new coloum in my query and call it ATD, and add the above expression. Then it asks for a value, and all it does is to add that stupid value to all 16000 rows.

I now know how to make a query, but how to add an extra coloum with a criteria like the above? (for more info read my first question in this thread to see what I'm looking for )

 

[bat17]

I did it the hard way LOL
in the criteria just put <= 36 and that is all you need it will only return weeknumbers 1 to 36

Peter

 

[David_Skov]

I figured it out, and tx for your help I have another Q in my other thread