I'm trying to hold some data for a

[ChiTownDiva]

I'm trying to hold some data for a period of time..

I have a table:

RC Code Count Count1 Count2
E8 26
S3 128
QB 56

This is the count is as of today. Next week (11/22), when I look at the table I want it to look like this:

RC Code Count Count1 Count2
E8 26 13
S3 128 122
QB 56 43

The week after that (11/29), I want the table to like like this:

RC Code Count Count1 Count2
E8 26 13 10
S3 128 122 111
QB 56 43 38

The Fourth week (12/6), I want it to look like this:

RC Code Count Count1 Count2
E8 13 10 10
S3 122 111 100
QB 43 38 26

So, I guess in a nutshell, I want the columns to roll based on the date.

I guess I need to know which direction I need to start in. I have the intial table built and populated, but I'm not sure how to write the code to delete the count column, move the data from Count1 and then populate the last count column.

Any help would be appreciated.

Thanks.

ChiTownDivaus

 

[davman2002]

If you are using Access you could try a crosstab query to display the results the way you want. But would you really want the column headings to say "count", or would you prefer to have the column heading equal the date of the week being shown?

 

[davman2002]

what format is the table that contains this information currently in? I.E. what are the current column heading called?

 

[dpimental]

If you could determine the week (number) of the month, and you had 6 week numbers, then you could look up the number and update the field appropriately.

You could have 6 week number fields (named Week1-Week6).

You get the date, using Date().
push it to a variable and pass it through a custom function to get the week number and either do a Select Case, if then else, array, or just myvar = "Week" & myweeknumber.

That would be the name of the field in the update query.

What do you think?

David Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[MichaelRed]

In general, you are constructing a problem. The 'structure' you are contemplating is (badly) denormalized and will generate additional work throughout the the process. A more normalized structure would be:

RC_Code[tab]Date[tab]Count

When a Count set is no longer needed, just run a delete query with the Date criteria. To add records for a 'week', just set up the process to use the week date you want.

Use a crosstab or piviot query to 'organize' the date in the 'original' format.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ChiTownDiva]

Thanks for the response....

Davman2002 - I don't think I need a crosstab query. I need to add counts to a table. The column headings are "RC Code" and "CountofRC Code".

dpimental - I guess what I'm trying to do is populate the columns based on the last time the third column was populated. For instance, if the report is ran at least 1 week after the date the last time it was ran, then "update to null" column1, update column1 with the values in column2, and so on. But if the report is ran in less than a week, just update the 3rd column.

I think somehow I need to be able to capture the date when the report is ran, but I'm not sure how to do that.

MichaelRed - Okay, I see adding a date field in the table and populating it with Now() or some other date function. Are you saying use the Date columns to run test, but how would I write the code? Would I use a "test table"?

ChiTownDivaus

 

[MichaelRed]

Hmmmmmmmmmm,

Ever so many ways to 'skin the cat' here. My 'knee jerk' approach (w/ very little to go on) would be to use the table structure w/ the date and abstract the dates from the most recent N weeks of date in a crosstab query and use that as the report basis. The N could easily be a parameter, with some small (datediff) "logic" to determine which dates then were included and their relative "week" inclusion. And the rest ... would be history. As Noted, this is just a "knee jerk" approach based on very little knowledge of your overall needs.

However, I can say for sure that the overall current trend in db land is in favor of Deep vs. Wide (e.g. more records with fewer fields as opposed to fewer records with fewer fields. And the suggested table follows directly from that trend / concept.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places