calculating Week numbers in a time table

I have a weekly timetable table for school terms with the following fields 
WeekID (automatic number), WkbeginDate (date), WkNo ( number 1 or 2). The 
WkNo can be either 1 or 2 . How can I get the Wkno field to automatically 
update with the correct week number for each new record ie if a record has 
week value 2 the next record will have a WkNo value of 1? I would prefer to 
do this using a query rather than a form. 
The timetable is not for the whole year and has 'holiday' breaks, sometimes 
restarting on a week 2, sometimes on a week 1.
Hope this makes sense!
Hilarys
0
Utf
8/13/2007 10:38:04 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
898 Views

Similar Articles

[PageSpeed] 9

=?Utf-8?B?SGlsYXJ5cw==?= <Hilarys@discussions.microsoft.com>
wrote in
news:2E6F096C-FF74-479D-A408-403C5352A711@microsoft.com: 

> I have a weekly timetable table for school terms with the
> following fields WeekID (automatic number), WkbeginDate
> (date), WkNo ( number 1 or 2). The WkNo can be either 1 or 2 .
> How can I get the Wkno field to automatically update with the
> correct week number for each new record ie if a record has 
> week value 2 the next record will have a WkNo value of 1? I
> would prefer to do this using a query rather than a form. 
> The timetable is not for the whole year and has 'holiday'
> breaks, sometimes restarting on a week 2, sometimes on a week
> 1. Hope this makes sense!
> Hilarys
> 

It is much easier to do things like this in a form, A form can 
be made to look just like a query. Please reconsider.

To do this in a query, you could fix a term start date, in a 
separate table with only 1 row.

add this table to your query along with your existing timetable.

datediff("WW",termstartdate,weekbegindate) will return the 
number of weeks since term began.

This number mod 2 will return a 0 or a 1. to which you will add 
1 to get your wkno. 

but that won't work when adding new rows to the table, so after 
you add one you will have to rerun the query. 

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
8/13/2007 10:33:37 PM
Reply:

Similar Artilces:

#DIV/0! displayed in Pivot Tables
I create Pivot Tables that use calced fields. Sometimes the calced fields divide zero by zero which results in #DIV/0! showing up in the RowTotals and TotalTotals. I can use the Table Options For Error Value Show (blank), which removes the #Div/0! from the table display, the only problem with doing this is the TotalTotal cells also show up as blank. Here's my code for creating the calced field. ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "Weighted Avg Price", _ "=rev / PosSold" With ActiveSheet.P...

Smartlist builder calculated field problem
I am trying to build a smartlist with a calculated field that contains the number of hours and minutes until a service call expires. The user defined 1 field is the date and time in smalldate format that shows when the call expires. I need to have it so that the field displays the hours / minutes until this time is reached. I currently have the following in the : DATEDIFF ( hh , GETDATE() , {Service Call Master:User Defined 1} ) this returns absolutly nothing when I add the columns to the smart list. The field description in great plains tells me that the user defined field is a stri...

Table List
I've seen two different Great Plains utilities that have the capability of reading the list of tables out of the dynamics dictionary. One is the DDB utility for browsing the data, and the other is the GenView utility that comes in the SDK. My question is how are they getting a list of tables? I'm still ramping up on SanScript, Dexterity, and Continuum, but I'm not finding anything that would allow me to get a list of tables. Any pointers? -Bill Bill Have a look at the Extras page of http://www.rocktonsoftware.com/ for the Pushing the Limits with Dexterity materials. Thes...

Pivot Table Problem #2
I have a pivot table I created that basically counts items so that I have am item name and count side by side after the pivot table completes as below Item1 24 Item2 15 Now my issue is that I want to count a secondary attribute of these same items (say color for example) and express it as a percent of the count already in the pivot table. I have no problem getting the count but I can't seem to figure out how to get the percentage part. I see options to have the data as a % of the column or row, but not as a percent of the existing count in the table. For example the finishe...

Text "12:00 AM" to Time Format
I have some data that was text and in time format such as: 12:00 AM 12:30 AM 1:00 AM 1:30 AM When I change the format of the column to Time it is still behaving a text. If I sort the column the data is sorting in the followin order: 1:00 AM 1:00 PM 1:30 AM 1:30 PM Even if I paste the columns data into a new spredsheet that already ha the column format in time it will not behave as a time field. Is there a way to convert the text data to time? Thanks in advance. M -- Message posted from http://www.ExcelForum.com Hi after changing the format try the following: - select an empty cell a...

Pivots tables refreshed but not updated
When refreshing data in Pivot Tables it says that it has been refreshed but the data has not been updated. This mostly occurs when linking to data in another spreadsheet ...

Excel 2007
Hi, weird question. I've got several thousand single-cell alphanumeric values (e.g., CUSA000040, CUSA000041). Basically they range from 01 to 10,000. However, some numbers are missing from the sequence. Is there a way to have Excel show the ranges of values that *do* exist in this long list? As in, 40-150; 151-200; 205-4000, etc., with or without the alpha prefix? Thanks very much! Hope this question makes sense! Assume your values are in column A on Sheet1. Insert a new Sheet2 and in A2 enter this value: CUSA000001 and put this in A3: CUSA000002 Then select those 2 cells and d...

Time formats and adding.
I have a spread sheet that contains times (But they are in the general format.) These times are typically in Minutes and Seconds. Examples (MM:SS)- 2:22 55:25 7:40. Since these are in general format I can’t do any addition to the numbers because it is time. I can convert the General formats to (h):MM:SS and then the examples turn to Ex. 2:21:00 55:25:00 7:40:00 then I would divide by 60 to get the results I need and I am able to add the times and get the correct times. Now if you understand that is there an easier way to do this or how can I set up a macro to do this to an entire works...

Sales Report: current v previous for time period
Does anyone have a way to run sales comparison for a specified time period ie Dec06 vs Dec05 Hi DAS, If you know how to customized the .QRP files then you can easily do this. I am just giving you an idea: 1. Open the Daily Sales by Sales Rep.QRP 2. In the ProcedureCall make it "" 3. In the TabelQueried type your query criteria how you wanted to do this ( you need to write the group by query to get the resultet. I have tried the same way on one of the detail report in RMS You must be good in query writing. It will work. "DAS" wrote: > Does anyone have a way to r...

Pivot Table in a Shared Workbook
Hi! Can I use pivottables in a shared workbook? The reason I ask is that its greyed out in a shared WB but not in a non-shared. I can't find anything on this, perhaps somebody can help? thanks! Chris You can't change or create a pivot table in a shared workbook. For more information on shared workbooks, look in Excel's Help, under the topic 'Features that are unavailable in shared workbooks' Riskybizniz wrote: > Hi! Can I use pivottables in a shared workbook? The reason I ask is that its > greyed out in a shared WB but not in a non-shared. I can't find any...

Numbered Procedural Steps Alignment
I am a technical writer who uses Word a lot to write user documentation. I recently upgraded to Office 2007, and there are several things that I find are very unfriendly for writing procedures. The procedural template I use for one company uses Arial 11 pt font for the document content, and Arial 14 pt for the number of the steps, as well as call out on the screen images. The initial indent level for the procedures is to have the number at 0 left margin and the content that follows it indented to .25". This works great for 1 - 9, but when I get to double-digit numbers, it...

Calculating total amount in a Pivot Table
I'm importering af row of items (posts) from a bookkeeping system to a Pivot Table The posts are like this:: Order1, date, hours, amount Order2, date, hours, amount For each order there is 12 posts, one for each month in the year. Each post has a number og hours and a total amount for the number og hours (per month) times the hourly rate. In the Pivot Table they look like this Order Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dec Total hours Order 1 2 1 4 2 1 4 5 6 3 1 4 2 35 I would like a sum for the total amounts...

Extra columns in Pivot Tables
When I add a second data item into the data area in pivot tables, the second data item appears below the first. Is there any way of making the item appear in a separate column instead? Just grab the Grey cell marked Data (Probably B3), and drag it to the Column Fields, eg C3 Regards Ken.................... "Lee" <Lee@discussions.microsoft.com> wrote in message news:76516B7B-FDD2-45F1-A993-51847FA77866@microsoft.com... > When I add a second data item into the data area in pivot tables, the > second > data item appears below the first. Is there ...

Restricting number of recpients a message can be sent to
Hi Folks Is there a way to restrcit the maximum number of recipients a person can send a meesage to, based on windows group membership? Ie: Group 1 can send to 10 users Group 2 can send to 500 users I know you can set this on a per user basis, just wonderig if there was a better way to do this for multiple users. Ta SuperPlay SuperPlay <SuperPlay@discussions.microsoft.com> wrote: >Is there a way to restrcit the maximum number of recipients a person can >send a meesage to, based on windows group membership? Not that I know of. -- Rich Matheisen MCSE+I, Exchang...

page numbering
Hi I am using Word 2007 - and have looked on the boards for answers but those I have tried haven't worked! I am working on a dissertation and have created sections for Title page/ Contents/ List of Illustrations and want {Page} of {Numpages} to start at Section 4 - Introduction and continue through the rest of the sections/ chapters (so far up to 11 sections). The first page of the Introduction is currently page 4 but I want to start the numbering there with page 1. I seem either to get numbering on all pages - or on the first page of each section and not the rest of th...

How to determine time constant
I was wondering if there was a way to determine the time constant of an exponential curve on chart. You will need to tell us more. I am a chemist and my idea of "time constant" may not match yours. Tell us about your data. I expect a trendine or LOGEST formula is going to be the answer. Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "manav" <anonymous@discussions.microsoft.com> wrote in message news:6b9e01c475a4$506f8a30$a301280a@phx.gbl... > I was wondering if there was a way to determine the time > constant of...

Linked Table in Access database
I have a Bluetooth enabled Laptop & I have 2 Bluetooth enabled mobiles named as AX1 & AY1 My Laptop display these mobiles in main window of Bluetooth as follows AX1 AY1 I am aware that Bluetooth has device list(a data list like table) & the same is dispayed in main window(like a form). My question is how to link this device list in my Microsoft Access database table? hi Ο "apcalogic" <apcalogic@discussions.microsoft.com> έγραψε στο μήνυμα news:F6B834B0-9C8E-49B6-8061-CC3E348D023A@microsoft.com... > > I have a Bluetooth enabled Laptop &a...

Distribution List Max Number of Recipients
Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5 Thanks. "L. Wagoner" <anonymous@discussions.microsoft.com> wrote: >Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5? It's somewhere in the vicinity of 5,000. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

grab distinct values from a bunch of fields in other tables, and store in a new table
Hi, In my access database, I have a few tables (tbl1, tbl2, ...) storing Medication names. Now, to avoid entering same medication in different ways, I am planing to create a table tbl_Lib to store a standard name of every medication from tbl1, tbl2, ... Then, this tbl_Lib will work as a medication name library. Now, I am wondering if there is an easy way to import the existing medication name into this new table tbl_Lib. The medications to import are now stored in the following fields: tbl1.Med1 tbl1.Med2 tbl1.Med3 tbl2.Medication Thank you for suggestions! Joy yeah this is called ...

Dot as time separator
How can i change the default setting for the time format from ":" to ". for inserting time on num pad -- moskit ----------------------------------------------------------------------- moskito's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2781 View this thread: http://www.excelforum.com/showthread.php?threadid=47322 You can set it up to work *temporarily* that way using "AutoCorrect". Works well if you're entering timecards on a Monday morning, and you wouldn't need it again till next week. Just go to: <Tools> <Auto...

Manual calculation does not work
I have a rather large Excel file of approx 26 Mb. It contains around 150 worksheets which are all filled with formulas (if-formulas, sumif-formulas, lookups and so on). Because of the scale of the file I have selected manual calculation (F9) under Tools/options/calculation, so it is easier to work with. I have from time to time experienced that Excel does not respond to F9, even though there are cells that are not yet calculated. I press F9 and nothing happens. Then I locate a cell which I know has to be calculeted. I enter into it by pressing F2 and then I exit it again without making a...

Group header page numbering
Hello All Access Guru out there Would like to have some guide / code / example on how to reset a page numbering to 1 of each group? ie Total pages = 6 Group Pages Site 1 1 / 1 Site 2 1 / 1 Site 3 1 / 2 Site 3 2 / 2 Site 4 1 / 1 Site 5 1 / 1 thaks in advance Hi, Try this (But it's in french) http://officesystemaccess.seneque.net/ex_pagination_groupe.htm TopJB PWYS a �crit : > Hello All Access Guru out there > > Would like to have some guide / code / example on how to reset a page > numbering to 1 of each group?...

Refresh Pivot Chart with Table?
I'm creating a large workbook with master data on one page and on all following pages Pivot Tables with associated Pivot Charts, one worksheet per state, 4 tables and charts per page. When I copy the Alabama formatted page to another worksheeet and change the Report Filter to Arkansas the table reflects the correct data, however the chart still shows Alabama data. Right-clicking on the chart and Select Data Source provides a greyed-out Chart Data Range. How do I fix this? Ideally I'd like to do this in as few steps as possible, but will change each chart individually if ...

timings query
Hi i have a start and end date/time. What i want is for it to miss out the weekends and formulate how muc time it took between the start and finish time. Below i have copied a few lines in. Submitted Approved 09 July 2004 09:54:24 12 July 2004 04:06:18 09 July 2004 11:56:59 12 July 2004 03:42:34 So basically add from 9:54 in the morning till midnight (preferably 5p but dont think it would be possible), then miss the 10th and 11th an add the 4:06. I know its possible but am a little lost! any help welcom -- Message posted from http://www.ExcelForum.com I had a ...

Excel startup bug: VBA Run-time error 76??
Help, please. Yesterday, Excel started giving me error messages whenever I start it up. I uninstalled Office 2000, reinstalled it, and I still get the same error. Here is what happens. MS Visual Basic displays a dialog box that says: Run-time error '76': Path not found When I hit the debug button, it highlights (in yellow) the following line of code: lengthOfFile = Filelen(registryValue) What should I do to fix this annoying problem? Steve Delete the ZZZ. It keeps spam off the server. Start it with a switch "C:\program files\microsoft office\office\excel.exe" /...