Hi,
I am an ICT Student and I am designing a work related project which is using MS Access 2007 to create a database to monitor leave for fire brigade staff.
I have studied relational database with SQL but am new enough to Access.
The shift patternns follow a 28 day cycle. I have a table with fields containing dates
My question is: how might I, using VBA, or otherwise add 28 days to this table to create a new table of dates, without having to do it manually.
Thanks in advance.
Alan
23 2425 NeoPa 32,556
Expert Mod 16PB @irishalanm
Hi Alan. Welcome to Bytes!
I'm not clear what you mean by adding 28 days to a table. I'm sure we can help you (VBA has the power), but we need a clearer understanding first of precisely what you're trying to achieve.
Ok Sorry about being less than clear!
What I want to do is advance each date in a table of dates by exactly 28 days. I suppose I would have to set up a loop and for each field value use the AddDate() function to add 28 to each and either append the new values in the existing table or create a new table, leaving the original dates unaffected. Does that explain it any better?
Thanks again
Alan
NeoPa 32,556
Expert Mod 16PB
I would do it in SQL, but would need an idea of what the field to be changed is called (when the new record is added), as well as a way of determining which records to copy after there are a number of duplicates in the table.
How about posting the table structure to look at, and explaining, in that scenario, how you would want to identify the records that need to be copied.
Hi NeoPa,
Thank you for your time and apologies for the delay in reply. I think I need to give you some background to my project so you can see what I am trying to achieve. I am designing a database to monitor leave (days off) for a company that has different groups of staff on different shifts. Because the employees work more hours than the standard week they are entitled to different leave throughout the year. These are rostered to every employee at different times of the year but they can exchange or swap dates of leave for different dates provided the shift patterns are the same or similar. My main problem is to devise a method to auto-populate tables with dates so that the dates are correct, as mistakes can be made manually, and also because inputting the dates would be too time consuming.
For each of the groups of workers, their shift pattern repeats ad infinitum EVERY 28 DAYS. So if I can find a way that a table could be populated based on one 28 day block by continually adding 28 days thast might solve my problem.
Also I need to refer to a calender so that I can cross reference what day of the week a particular date is.
I have been trying to find out if I can get 'under the bonnet' of the calender control tool in MS Access 2007 but so far all I have been able to find is a way to add a calender date picker to a form or use the date picker to pick individual dates on a table.
I have seen comments on importing outlook calender from outlook using VBA?
Any other ideas?
Regards
Alan
NeoPa 32,556
Expert Mod 16PB
I'm starting to get a picture, but see various potential problems. I need a better understanding I expect.
What sort of data are you storing for a 28-day block? Some example data would help greatly.
Clearly, advancing by 28 days avoids any weekday issues, but holidays may be lost from one to the next. Possibly a template would be preferable. I still need a clearer understanding to advance any.
NeoPa 32,556
Expert Mod 16PB
Consider also, any block of 28 days will be interesting within that block, as well as for a certain period before and after it. Having only one set of the data in the table at any one time is likely to be too limited.
I am storing several tables with nothing but dates in them and I need to have a calender in a table as well. I plan to cross reference dates. I have seen previous thread where ADezii posted an example database from Philly Fire Dept a while back but the link has been removed so i couldnt see it but it sounds like it could be similar to what I want to achieve.
Regards
Alan
Could you store this shift information as a day number, eg - StafffID DayNo Shift (hrs?)
-
1 1 7.5
-
1 2 7.5
-
1 3 8.5
-
1 4 9
-
…
-
2 1 7.5
-
2 2 7
-
then use a Mod 28 function on said dates to pick out patterns on the fly, storing leave dates elsewhere if needed?
Dan
NeoPa 32,556
Expert Mod 16PB
How about a set of template data. This could be stored in a separate table.
If the results you're after is simply a set of dates (no other fields as indicated in post #8), then the template could be a set of records storing date offsets only. That way, on being passed a base date, you could easily create a set of dated records which match the offsets stored in the template table, beyond the base date given.
This could be called whenever a new block of dates is required.
Does this sound like a viable solution?
The problem is I don't know how to do it. Do I use VBA with the DateAdd() function? Does anyone know how to access the calendar control function in Access 2007 or is this possible?
Thanks
Alan
Hi Dan
The tables contain dates and an important part of the project is to cross reference these dates. I was thinking about using DateAdd() function but I don't know how I would access the table in VBA or otherwise.
Thanks
Alan
If i understand the problem correctly then you dont need to store and update dates,you can use the template to store shift patterns and then refer to that as a cycle of 28 days. you can then in another table if you need to store specific dates for someone to be "off". and with regard to ADezii's calendar you easily adapt it to show information from this template table.
NeoPa 32,556
Expert Mod 16PB @irishalanm
If the template held offset values as suggested, you could create some SQL to use DateAdd() with the offset field value as the offset and a reference to the date on your form somewhere as the base date to which the offset is applied.
IE. Records with offset values of 1, 2 & 5 and a base date (on your form) of 20 April 2009 would produce date records in your destination table of :
21 April 2009, 22 April 2009 & 26 April 2009 respectively.
Does that make the concept easier to follow?
Yes sounds good-could someone attach ADezii's calendar? I'm afraid the attachment is no longer there from thread way back.
Thanks
Alan @Dan2kx @irishalanm irishalanm, If you could provide me with some sample Table Data, I'll see if I can integrate it into the Calendar for you. You can provide Date Ranges and the code will convert it to linear Date Values but remember to keep the Display Fields limited and short, since space is of the essence in the Calendar Display.
Hi ADezii,
I am doing a project for college which is work-related and I want to cross reference dates in tables which are particular to a group of people with a calendar as well as various rosters they have been assiged holidays to. Part of my project relates to researching how a similar problem might have been solved by someone else. I appreciate your offer and understand if you don't want to show your work. But I need to solve the problem myself and need some expert guidance!!! So i don't think there is any point giving you data as I don't even know what your calendar does?
I can put all the dates in my fields manually and I can painstakingly enter the dates and days into a table to form a calendar but I am trying to find out if there is a way to autopopulate a field with calender dates. See word doc attached.
regards
alan @ADezii
Hi, Alan.
I think you may find some tricks described in this thread useful in building your application.
Regards,
Fish.
@irishalanm
Hello Alan, I've modified the Code in the Calendar Database in order to give you some idea of the functionality which it can expose. tblEvent contains the Primary Data, and the Calendar will provide a visual indication as to Events, their Owners, Event IDs, and the specific Ranges for those Events. Should you need any additional help, just let me know. FYI, Date Ranges specified in tblEvent are converted to linear Values in tblEventTemp which then supplies the required Data for the Calendar display. See the Atachment for the DB.
Thanks a million ADezii,
The Calendar application looks amazing-I haven't had a chance to experiment with it yet.
Thanks for the link as well Fish-I haven't had a chance to look at it yet.
Regards
Alan
Hi everyone (who might be interested;))
I have found a way to append dates to the table -it is not sophisticated but it works.
First I create the table which will store the dates I need.
Table [Watchdates-A/B]-contains a single Date/time field [A/B WatchDate] which is a PK
I add dates that are worked in a single 28 day period for a certain shift -This amounts to 7 dates.
I then create an Append table query which will move each date forward by 28 days.
I declare the following field and set the query to append new dates to the existing table:
New Dates: DateAdd("d",28,[Watchdates-A/B].[A/B WatchDate])
I run the query over and over..click tap tap tap(After about a minute I have a table with 5-600 fields enough for the next few years).
Because no duplicates are allowed as it is a primary key no duplicates are appended into the table.
I'm sure there is a more clever and sophisticated way to do this, but it works.
Regards
Alan
@ADezii
Hi Adezii,
I'm not sure what your calendar project does or how it works-I can only see the form-it looks good though!!
regards
Alan
@FishVal
Hi Fish,
I amended your flat calender query to add the day of the week. It is brilliant. i can't believe how easy it is (when you know how!). I guess the moral of the story is it is all about using the in-built functions and SQL. I used the Weekday() function to convert the date to the day of the week.
Thanks very much
Regards
alan
NeoPa 32,556
Expert Mod 16PB
That's good to hear Alan.
However it works, working is good :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bret Shortall |
last post by:
Using the SENDOBJECT action command in a Macro works fine if you hard
code in their email address.
SENDOBJECT TO bret@emailaddress.com
SUBJECT This is the Subject line...
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked...
|
by: Tony Williams |
last post by:
I have two forms, frma and frmb, both are for inputting new records and are
based on a table, tblmonth, both have two controls, txtmonth and
txtmonthlabela which are based on the fields in the...
|
by: Daveyk0 |
last post by:
Hello there,
I have a front end database that I have recently made very many changes
to to allow off-line use. I keep copies of the databases on my hard
drive and link to them rather than the...
|
by: anthonybrough |
last post by:
I have an asp page that has a form to collect user data in a form.
when the user clicks submit the input is validated. If any fields are
not acceptable the user clicks on a button to go back to...
|
by: Lauren Quantrell |
last post by:
I have just put together a vb.net app and now need to provide it to
users. This application needs to run the code in a sub every 60
seconds from a Windows Service application. I have the...
|
by: whizkid |
last post by:
Hi...
Looking for a solution to a problem that I am facing...
Basically I have a table schema defined as follows.
Table X(
br char(2),
Dealno char(8)
SeqNo char(4)
|
by: Atli |
last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t.
In situations where your PHP application...
|
by: hutch75 |
last post by:
Question could also be asked, how to compare data between two arrays and perform an action (print cmd) everytime there is a match?
The problem I'm having with the code below is that the comparison...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |