Numbering....???

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I have the following Table:

Week End Date WK #
10/17/09 11
10/24/09 10
10/31/09 9
11/7/09 8
11/14/09 7
11/21/09 6
11/28/09 5
12/5/09 4
12/12/09 3
12/19/09 2
12/26/09 1
1/2/10

Each new week will be appended to this table (ie. that 1/2/10)
With 1/2/10 being the current week (and just appended) I need to create a
query (queries) that will delete WK# 11 and re-number WK# column (so 10/24/09
is the new 11 and so forth down the line). I've tried working with
auto-number, but that simply adds not re-numbers...

Reason need - I'm using the Dates for a crosstab Column header, but with the
week end dates dropping off/changing, I always need to re-do the crosstab
query. So if I use a common placeholder to identify to week end date, than I
don't have to do this.

Is there anyway I can get what I need via queries (not VBA)?
Please help! Jennifer
 
Jennifer -

You can do this with queries.

First, create a query that will pull the maximum end date from your table:

SELECT Max([tablename].[Week End Date]) AS MaxOfWeekEndDate
FROM [tablename];

Save this query as something like MaxWeekEndDate.

Second, create the query that will give you what you want:

SELECT [tablename].[Week End Date], DateDiff("w",[Week End
Date],[MaxOfWeekEndDate]) AS WeekNbr
FROM [tablename], MaxWeekEndDate
ORDER BY [tablename].[Week End Date];

Use your table/field names and give it a try!
 
Jennifer said:
I have the following Table:

Week End Date WK #
10/17/09 11
10/24/09 10
10/31/09 9
11/7/09 8
11/14/09 7
11/21/09 6
11/28/09 5
12/5/09 4
12/12/09 3
12/19/09 2
12/26/09 1
1/2/10

Each new week will be appended to this table (ie. that 1/2/10)
With 1/2/10 being the current week (and just appended) I need to create a
query (queries) that will delete WK# 11 and re-number WK# column (so 10/24/09
is the new 11 and so forth down the line). I've tried working with
auto-number, but that simply adds not re-numbers...

Reason need - I'm using the Dates for a crosstab Column header, but with the
week end dates dropping off/changing, I always need to re-do the crosstab
query. So if I use a common placeholder to identify to week end date, than I
don't have to do this.

Is there anyway I can get what I need via queries (not VBA)?
Please help! Jennifer
Click to expand...


Yes, but that's a poor approach to the crosstab problem.
Ideally, you should calculate the Wk# on the fly in the
crosstab query using the DateDiff function.
 
If 11 weeks are always desired regardless of gaps in the
data, use the query's ColumnHeadings property with the
numbers 1 through 11 or maybe 0 through 10.

If the number of weeks is a criteria parameter, the utility
table with 1 (or 0) through N is the best way to deal with
gaps.
 
Thanks so much Clifford. I was able to build off what you posted.

Instead of doing exactly what you stated - I simply did this update query:

UPDATE Dates SET Dates.WK = IIf([Dates]![WK] Is Null,1,[Dates]![WK]+1);

Cause once the most current week is appended to the table the WK field will
be null, so update that to 1 and the rest just followed. It worked perfectly!
I'm not sure why I didn't think of this originally, but thanks for the
guidance!

Jennifer

Clifford Bass via AccessMonster.com said:
Hi Jennifer,

If [WK #] is indexed without duplicates you can do it with two queries
without the need to delete any rows or add any rows. First, make sure the
index is in descending order instead of ascending order.

Query 1 to update WK 11 to the new date and shift WK # to 0:

UPDATE tblWeeks SET tblWeeks.[WK #] = 0, tblWeeks.[Week End Date] = DateAdd
("ww",11,[Week End Date])
WHERE (((tblWeeks.[WK #])=11));

Query 2 to shift all of the WK #'s up by 1:

UPDATE tblWeeks SET tblWeeks.[WK #] = [WK #]+1;

If [WK #] is not indexed, you can do it all in one query:

UPDATE tblWeeks SET tblWeeks.[WK #] = IIf([WK #]=11,1,[WK #]+1), tblWeeks.
[Week End Date] = IIf([WK #]=11,DateAdd("ww",11,[Week End Date]),[Week End
Date]);

Clifford Bass
I have the following Table:

Week End Date WK #
10/17/09 11
10/24/09 10
10/31/09 9
11/7/09 8
11/14/09 7
11/21/09 6
11/28/09 5
12/5/09 4
12/12/09 3
12/19/09 2
12/26/09 1
1/2/10

Each new week will be appended to this table (ie. that 1/2/10)
With 1/2/10 being the current week (and just appended) I need to create a
query (queries) that will delete WK# 11 and re-number WK# column (so 10/24/09
is the new 11 and so forth down the line). I've tried working with
auto-number, but that simply adds not re-numbers...

Reason need - I'm using the Dates for a crosstab Column header, but with the
week end dates dropping off/changing, I always need to re-do the crosstab
query. So if I use a common placeholder to identify to week end date, than I
don't have to do this.

Is there anyway I can get what I need via queries (not VBA)?
Please help! Jennifer
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top