473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

dating issues

ok im faced with two tasks with my database.

firstly i need to etract a weekday (field) from a date (field) - so that when a date is entered, it looks at the date and calculates the day. ive done this before but long since lost the code to generate a weekday from it.

Secondly i have a table in my database that records the attendance of students to dance classes, i want the system to flag four in a row "non attendances" to any class in order to alert an operator of the system,so that they can then action a procedure(external to the database).

Any ideas?
Oct 17 '07 #1
14 1488
ADezii
8,834 Expert 8TB
ok im faced with two tasks with my database.

firstly i need to etract a weekday (field) from a date (field) - so that when a date is entered, it looks at the date and calculates the day. ive done this before but long since lost the code to generate a weekday from it.

Secondly i have a table in my database that records the attendance of students to dance classes, i want the system to flag four in a row "non attendances" to any class in order to alert an operator of the system,so that they can then action a procedure(external to the database).

Any ideas?
Assuming the 1st Day of the Week is set to Sunday:
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateWeekday(dteDate As Date) As String
  2. Select Case Weekday(dteDate)
  3.   Case 1
  4.     fCalculateWeekday = "Sunday"
  5.   Case 2
  6.     fCalculateWeekday = "Monday"
  7.   Case 3
  8.     fCalculateWeekday = "Tuesday"
  9.   Case 4
  10.     fCalculateWeekday = "Wednesday"
  11.   Case 5
  12.     fCalculateWeekday = "Thursday"
  13.   Case 6
  14.     fCalculateWeekday = "Friday"
  15.   Case 7
  16.     fCalculateWeekday = "Saturday"
  17. End Select
  18. End Function
Expand|Select|Wrap|Line Numbers
  1. ? fCalculateWeekday(#10/17/2007#)
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
Oct 17 '07 #2
FishVal
2,653 Expert 2GB
Or even simpler.

Expand|Select|Wrap|Line Numbers
  1. ? Format(#10/17/2007#, "dddd")
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
[/quote]
Oct 17 '07 #3
ADezii
8,834 Expert 8TB
Or even simpler.

Expand|Select|Wrap|Line Numbers
  1. ? Format(#10/17/2007#, "dddd")
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
Much, much simpler and definately more practical. I'll blame it on memory lapse.
Oct 17 '07 #4
excellent! however i was probbaly not entirely clear in whati was asking. the date needs to be entered into a field (from a text box) and fromt his generate the weekday into a seperate field (and display in another text box or a label) on a form,but store the inputted and generated info to the table that the fields refer to.

I havent used access in about a year,which is why im seeming like a total dummy, but once ive got things working i suppose ill be back on track.
Oct 17 '07 #5
FishVal
2,653 Expert 2GB
excellent! however i was probbaly not entirely clear in whati was asking. the date needs to be entered into a field (from a text box) and fromt his generate the weekday into a seperate field (and display in another text box or a label) on a form,but store the inputted and generated info to the table that the fields refer to.

I havent used access in about a year,which is why im seeming like a total dummy, but once ive got things working i suppose ill be back on track.
You don't need to store weekday in separate table field. Though you've not used Access for a year the rule not to store duplicate information is still relevant.
You may include weekday calculated field in query - your form RowSource:

something like
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.*, Format(tbl.dteYourDateField, "dddd") AS txtWeekDay FROM tbl;
  2.  
and set weekday form control ControlSource property to "txtWeekDay"

or

set weekday form control ControlSource property to something like
Expand|Select|Wrap|Line Numbers
  1. =Format([dteYourDateField], "dddd")
  2.  
or

populate weekday unbound textbox via VBA code

Storing weekday in separate table field is not an option.
Oct 17 '07 #6
cool!

Now im faced with another interesting paradigm of problems peripheral to the original dating issues. Ideally,i need to design a query will select records from a table (attendance to classes) by a date rangeand / or by class type. What would be the best way for the operator of the query (eventually a report in ideal situations), to generate attendance data from a date range?

I know its a bit "vague" and can provide table information if necessary.
Oct 27 '07 #7
forget the last post. i managed to sort that out this morning.

instead,the last challenge im faced with is thus:

I need to design a querythat will automatically generate a report (or at least collate data) that looks at multiple records for a boolean value that is consistant over a date range (ie four weeks). The theory for this is that the table in question records the attendance of classes, and that a non attendance by a person four weeks running will therefore be flagged by the system and will generate a report for this.

its a bit beyond me but im confident once i understand how to implement it ill be well away. any ideas folks?
Oct 28 '07 #8
FishVal
2,653 Expert 2GB
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Oct 28 '07 #9
Fields for the table are as follows:

member name (text) (fk)
venue (text) (fk)
ClassDay (Text)
Clasdate (Date & time)
Class (text) (fk)
Time slot (text) (fk)
Attendance (boolean)
Oct 29 '07 #10
FishVal
2,653 Expert 2GB
Fields for the table are as follows:

member name (text) (fk)
venue (text) (fk)
ClassDay (Text)
Clasdate (Date & time)
Class (text) (fk)
Time slot (text) (fk)
Attendance (boolean)
I need some more explanations of how do you store data in the table.
  • Do you still store date and weekday in separate fields ?
  • What is [Time slot] field for?
  • What is [Attendance] field for? Does it mean a record is made when a member is absent?
Oct 29 '07 #11
Attendance marks the presence of a student to a particular class

Time slot indicates the time of day in which the class takes place (ie 1pm-2pm)

Weekday is stored in a seperate table and accessed using a lookup / row source (to stop the idiots using the final product from getting it wrong)
Oct 29 '07 #12
FishVal
2,653 Expert 2GB
The following query will give you most resent attendance date for each member.
(Suppose [Clasdate] field is for that.)

Expand|Select|Wrap|Line Numbers
  1. SELECT tblYourTable.[member name], Max(tblYourTable.[Clasdate]) AS dteMostResentMemberAttendance
  2. FROM tblYourTable WHERE  tblYourTable.[Attendance]
  3. GROUP BY tblYourTable.[member name];
  4.  
Save this query, e.g. [qry1] and build another query based on [qry1].

Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.* FROM qry1 WHERE DateDiff("ww", qry1.dteMostResentMemberAttendance, Date()) > 4;
  2.  
Oct 29 '07 #13
Finally i am faced with another interesting conundrum.

In my main members table there are two classifications for students, kids and adults. I need a function that when someone turns over 16, they are assigned to the adult category. I apreciatethis may be done by using the date of brith field to calculate age - but i have no idea how to go about it.

Any ideas, boffins?
Jan 8 '08 #14
FishVal
2,653 Expert 2GB
Hi, there.

The following expression will return true for a person whos age is 16 years and older.

Date() >= DateAdd("yyyy",16, [dteBirthDay])

I would strongly discourage you from storing this as separate field in the table unless you want to run update query daily.

Regards,
Fish
Jan 8 '08 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Michael Lawrence | last post by:
I am looking to start up a comprehensive Adult dating website and I need pre-made PHP scripts to set up the back-end for this project. Preferably someone out there has a set of pre-made scripts...
7
by: fbionyourtail | last post by:
Been looking for a good PHP based dating script that is reasonably priced for a while. Several come close but are unfinished. I've already been burned by scripts almost done and then a complete...
4
by: marvado | last post by:
Hi, Does anybody know of a good and FREE dating/personals script? I need to put one on my website and looked everywhere for it. Only I found was AzDGDating, but doesn't cover many aspects of a...
1
by: magia | last post by:
Start or professionally redesign your dating website with MAGIA Dating Site Design (www.qesign.com/psd-dating.shtml). Online dating is an ever expanding industry! *** Very attractive stylish...
4
by: PD | last post by:
Hello everyone, I had posted a question regarding how to design high traffic fast dating website on different PHP groups. I got a lot of useful responses. I have added links to all the posts...
4
by: Abs | last post by:
Hi all, Wondering if you could help.. i'm looking for an ASP dating application for my site. Preferably classic ASP as i don't know ASP.net. Any pointers would be highly appreciated. Thanks!
4
by: Aaron | last post by:
AJ Dating Script is built with focus on increased ease of users and raised profits of webmasters. PHP/My SQL development are the cornerstone of our work. We also provide solutions in various...
3
by: Aaron | last post by:
AJ Dating is software for Dating sites which is immensely structured by AJ SQUARE INC. AJ Dating is a Dating Software Script. The user can search for users by using quick search, easy search and...
0
by: ahmik.flock | last post by:
AJ Dating is a Dating Software Script. The user can search for users by using quick search, easy search and advanced search. Quick search searches based on Gender and Age, Country living and photo....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.