Queries :: Monthly Unpaid Students Finder Query?

Jun 23, 2013

i have 3 tables one for student information second for Van information and third is payment.

I want to know how to get the list of students not paid based on specific month which need to mentioned may b through a form.

View Replies


ADVERTISEMENT

Queries :: Query Time Difference AND Monthly Totals

Mar 21, 2013

I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.

View 14 Replies View Related

Queries :: Create Query To Filter Customers When It Is Time Of Their Monthly Payments

May 21, 2014

I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are

1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)

I made another field called "states". In this field I putted the following expression

Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")

Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:

1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.

To overcome the above shortcomings, i modified the expression to

Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")

This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??

View 1 Replies View Related

Queries :: Number Of Students In Age Range

Sep 17, 2013

I have this formula for counting age groups

TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))

But id does not work, if I use between 29 and 31 also do not work

I need to get count (number) of all students that have 29,30 and 31 year of age.

View 5 Replies View Related

Queries :: Counting Different Students Age From Same Table?

Sep 16, 2013

I have one table that has fields:
[ID]
[STUDENT_NAME]
[BIRTH_DATE]
[GENDER]
and I neet to get in qry where students will be counted by age and gender

For example:
younger then 15
tolal: 20
male: 5
female: 15

15-18 years
tolal: 34
male: 12
female: 22

Can this be done in single queri or should I do it in some other way?

View 2 Replies View Related

Queries :: Return List Of Students Attended Certain Number Of Sessions At Gym

Sep 2, 2013

I'm trying to return a list of students in a particular team who have not attended a certain number of sessions at a gym, between two dates. For some reason i get the error. At most one record can be returned by this subquery. (Error 3354).

SELECT [Student ID], Count(*) AS ["Number of Times Attended"]
FROM Gym_Attendance
WHERE [Gym Date] BETWEEN [Forms]![SV_Attendance]![txtStart].Value AND [Forms]![SV_Attendance]![txtEnd].Value
AND [Student ID] Like
(SELECT [Student ID]
FROM Student_Sports
WHERE [Sporting Team] = [Forms]![SV_Attendance]![lstTeam].Value)
GROUP BY [Student ID]
HAVING Count(*) < [Forms]![SV_Attendance]![txtNo].Value;

View 1 Replies View Related

Database With Record Finder

Sep 24, 2013

I prepared a simple database with a record finder. But it does not work and I can't figure it out why...

View 4 Replies View Related

Modules & VBA :: Calculation - Subtract Monthly Installment From Loan Amount On Monthly Basis

Apr 22, 2015

I have a query for loan calculation, fields are;

Loanamount
loandate
monthlyinstalment

what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis

View 3 Replies View Related

Show Certain Records (eg: Unpaid Transactions) As A Different Colour In A Listbox

Nov 30, 2006

i think this post may benefit others because to me it seems like a good idea,

a listbox displays 2 fields from your database

job table
id (autonumber primary key)
pay (text)

here are 5 records,
1,paid
2,paid
3,paid
4,unpaid
5,unpaid

if you make a form with a listbox showin these records..

is there a way to make the unpaid records appear as red?

View 9 Replies View Related

Queries :: Monthly Transaction Of Fee - Arrears To Be Shown

Jul 31, 2013

I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.

View 5 Replies View Related

Queries :: Calculate Fee For Each Student On Monthly Basis

Jul 23, 2013

Me having a db which is having a student table which is keeping all student info, second i have a Van table which is keeping van info. I wanted to know how it is possible to calculate monthly van fee for each student on monthly basis which db do it itself based on system date/time and calculate monthly required fee for each student. I also would like to know that if a student do not pay in a month then checking the next month or checking unpaid student through date criteria it displays all due amount may be in sum.

View 2 Replies View Related

Queries :: Counting Records Which Exceeds Monthly Average Value

Nov 14, 2013

I am currently working on a small database to track my own investment records.

I would like to create the function which enable to count the number of records that has exceeds the average value during that period.

Following is the SQL that I have managed to produce:

SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;

This SQL does work. However, when I try to add the Avg function. The Query stop working.

Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?

View 1 Replies View Related

Queries :: Running Total To Calculate Uptime Monthly

Jul 1, 2013

I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.

I attached a PDF with an example of what I am working with.

View 10 Replies View Related

Queries :: DSUM - Calculating Monthly Tasks Given To Employee

Dec 6, 2013

I have a query, i need to get dsum of total task given to an employee.

I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.

Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.

View 5 Replies View Related

Queries :: Track Monthly Data For Several Thousand Products

Apr 17, 2014

I have a table that track monthly data for several thousand products.

The idea is to grab the first month and the first three months of sales for each product.

So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).

View 2 Replies View Related

Monthly Expense Query

Jul 31, 2006

I have attatched a word document showing values and what I need. I want to create queries (monthly, quarterly, semi-anually, yearly). I am working with the monthly right now and if I can figure that out I am assuming I will be able to figure the others out the same way. For the monthly report I want to divide the quarterly amounts by 3, semi yearly amounts by 6, and yearly amounts by 12. Then I want their respective values to appear in the monthly statement. How do I do this?

View 1 Replies View Related

How Can A Create A Monthly Query ?

Jul 27, 2007

Hi guys,
Can someone please tell me if there is any way to create a query that gives me ... lets say a sum of smth monthly.
I'd like to create a crosstab query to have at
rows : names
columns : months
values : sum of smth (kilometers for drivers)

Thankyou in advance

View 3 Replies View Related

Monthly Query Update

Jan 8, 2008

Hello;

I'm curious if this is possible:
I have a table set up with a list of items followed by a column for each month's quota that I manually update. I run a query from my production table and quota table that will list all my products I have a quota on followed by how many of each were produced in that particular month. In the query I point to the quota table's current month and the data from the production table is pulled by the following:
Produced: Sum(IIf([production]![status]=4 And [production]![fix date] Between #01-Jan-08# And #31-Jan-08#,1,0))

How can I write this same information to pull only Jan08 information without using the "Between #X# and #X#"?
In the same way, I would need to point to Jan08, Feb08, etc.. columns in my quota table automatically.

Any help would be GREATLY appriciated

View 8 Replies View Related

Running A Query Monthly

Oct 27, 2004

Hi all - I have an append query all set up and running but I can't figure out how to run it on a monthly basis. Could someone help me with the SQL to run the query on the 15th of every month at 7:00am? Thanks loads.

Dan

View 3 Replies View Related

Queries :: Making Bird Count Database - Data Recorded On Monthly Basis Week Wise

Feb 21, 2014

I've been requested to make a bird count database.

The bird count data has been recorded on monthly papers like this:

[bird name] [week 1][week 2][week 3][week4]

The number of birds sighted for a given week is written in the [week x] columns.

What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].

So for example:

Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2

highest: =6

View 5 Replies View Related

Combine Monthly Product Sales Query

Feb 27, 2008

I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.

View 7 Replies View Related

Find Students With GPA 3.0 And Above

Nov 8, 2013

Ihave created a table that contains student name, last name address and GPA. THe instructor asked :You must find all the students that have a GPA of 3.0 or greater. Sort them so the highest GPA is the first student in the query.

View 1 Replies View Related

General :: Monthly Cleaning Program - Crosstab Query To Generate A Report

Jun 12, 2012

I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.

At this point I can generate a report for any given day.

This could be an example of what I want to achieve

Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0

"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)

I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.

View 4 Replies View Related

Mass Emailing To Our Students

May 18, 2005

Hello gang!

I've read through many of the threads relating to sending Email from Access but can't seem to find quite what I'm looking for. I work as a Tutor Coordinator at a local college and I've set up Access to handle a lot of the day-to-day chores that our administrative assistant has to deal with.

One feature that would be absolutely wonderful would be to create and send Emails to students at the click of a button. I have several types of Email I'd like to be able to set up, but the one most needed (and probably most complex) is this:

Students come in to the office and request a tutor. If we have a tutor available, we assign them to an available tutor. If there is no tutor availabe, then every Friday we send out an Email to each individual student saying something like this:

"Dear John Doe - We currently do not have a tutor for MATH 101 at this time, but when one becomes available we will let you know... blah, blah, blah..."

I'd like to set it up so that the body of the message comes from a template - such as a text file (that is easily edited by a user) and populated with fields from a query.

Probably do something like this:

Query the Student_First_Name_field, Student_Last_Name_field, Department_Name_field, Course_Number_field, and Student_Email_field. Send an Email to each Email address pulled from the Student_Email_field and tell them the following - "Dear <first name, last name> we currently do not have a tutor for <department name, course number> at this time... blah, blah, blah..."

Currently the way the administrative assistant handles this is to send each student an Email individually - manually creating each Email with the specific data needed. As you can guess, this is quite time-consuming.

I'm rather new to Access but have been a RDMS programmer (in PICK) for several years. I know what it is I want to do, but don't know how to do it in Access.

Any help?

Would the best way be to use text files for templates?

Can text files be set up with field codes (Access recognizes "<LAST NAME>" to be Student_Master_Table.Last_Name_field)?

I'm guessing this will likely have to be a Visual Basic loop that works with data from a query.

Help? *grin*

View 3 Replies View Related

Creating New Records For Students?

Jan 23, 2013

I have a simple database with 2 tables, students and progress. I need to set up my database so that when I create a new record for a student (using a form I've created) it automatically creates 4 new entries in the progress table using the ID I have generated in the form and a task number (1-4) for each of these entries.

Additionally, once all tasks are set to complete = true, I need to set the field "all tasks complete" to true. I'd like to do this all without vba if possible.

View 9 Replies View Related

Adding Students To Classes

Sep 2, 2015

I am in the process of setting up a DB for my partners school.

I have student and class tables. I have these linked in a many to many with a junction table.

I am making a student data entry form and I cant work out how to add the student to the class.

Ideally I would have a combo box to select the class and then when one is selected a fresh empty combo box would appear beneath it.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved