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 Replies


ADVERTISEMENT

Help With Recurring Monthly Payments And First Time Fee

May 31, 2007

Hello,

I have a database for rental units and i have two problems that i cannot solve. I have a table with 4 different options of units with different prices. what i would like to do is for the first time customers there is 1 time fee, how would i be able to charge them only 1 time and no charge after that.

My second question would be i have a payment form also and lets say a person joined today and has to pay 40 for monthly rental and 20$(the 1 time payments) which comes to 60$, and the person pays it in full so the balance is 0$, how would i set it up that a month from now, the 0$ will turn again to 40$ so the person has to pay again?

And also would i be able to set up a late charge if they dont pay on the day that they are supposed to?

Thanks for all the help....

View 14 Replies View Related

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 :: Tracking Time Spent On Customers Records Based On Date Of Entry

Apr 17, 2014

I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.

View 3 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

General :: How To Create Form / Code For Viewing And Consolidating Duplicate Customers

Apr 8, 2014

I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.

Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.

I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?

View 7 Replies View Related

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 1 Replies View Related

How Do I Create Monthly Fees?

Sep 21, 2007

I have stuck in something and i hope someone will help me with this: I have created a DB which shows all the students of a musical school. Each student pays a monthly fee (sep-june, 10 payments per year). I dont want to create all these columns in my main table beacause it will be too crowded. The only thing I can think of is to create 10 different tables one for each month but i think it won't work well when I want to see how much a specific student pays.. any ideas? the information for each month that i want to have is the student first and last name, date of payment, level, fees amount and money paid (and perhaps month name..).
Thank you in advance guys!

View 6 Replies View Related

How To Create Daily Table From Monthly Data

May 23, 2012

How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.

View 1 Replies View Related

General :: Create Multiple Queries Or Reports At The Same Time?

Jul 8, 2015

I need to know if possible to create multiple queries or reports at the same time. We have large table that is updated monthly this report gets broken down manually by Manager (30 managers in total) hen email to each manager.

Report or query specs will never change only the data I could do this manually create and save query/report for each manager once but I was wondering if there was code that will create all the queries and reports at the same time.

View 1 Replies View Related

Create Monthly Table Based On Employee List

Dec 26, 2006

Greetings all!!!

I have a database containing a table of employees and their rank. I want to record monthly details regarding these people: hours, performance, etc. Their rank can change dynamically from month to month. They may be a Supervisor for a month, and I want to change this value on a monthly basis. But I want to generate a Table which shows all employees belonging to each of the eight departments, but which has empty fields enabling me to record these monthly values. I then want to be able to pull up that data at any time to see these values for any given month in the past. I also want to add the values of each field and total them for the various departments providing monthly and yearly values on a departmental and organizational (grand total) level.

The employee names and their respective departments rarely change. I don't want to have to select their names (about 125 of them) every month to create a new blank form in which to put the monthly data. I want to have perhaps a button which creates the records and puts the names in for (1) department, (2) year, and (3) month, but that adds the fixed fields ready to put the data in. This data is required every month and the field names will never change. I'm sure the solution is programmatically simple, but I seem to have a mental block over the issue.

I hope that someone casts some light on this.

Do I use a Create Table query? (The data can remain in one table.) I would like to do it programmatically. My VB is pretty good, but, it seems, not good enough.

:confused:

View 7 Replies View Related

Queries :: Payments Made Between Two Dates

Jun 18, 2015

I'm working two dates that will highlight the accounts that have made a payment Today (Last Payment) + 60 days & move the entire row to another tab

Payment Made: iif ([Date of Last Pay])>60 days from ([Dis Date) highlight and move to tab number 2

and

Payment Made60: iif ([Date ()])>60 days highlight and move to tab number 3

View 1 Replies View Related

Modules & VBA :: Monthly Payment - Create A Worksheet Which Calculate Balance

Mar 3, 2014

I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.

Monthly Payment Period: 1 to 300

Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.

Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12

Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.

The final Balance Remaining should be exactly £0.00.

View 1 Replies View Related

Queries :: Find Customers Who Have Paid With More Than One Currency

Jun 9, 2014

I need a query to pick the bones out of sales transactional data like this...

Code:
CustomerName Currency
A Smith GBP
A Smith USD
B Jones GBP
B Jones GBP
T Brown GBP
T Brown AUD
C Wong GBP
C Wong GBP
S Giles USD
S Giles USD

From the above data, only A Smith & T Brown ...have made purchases in more than one (different) currency. How on earth do I trap that with a query? (or will it need a macro?)

If so, the table is called sales & the columns are as per the data above (CustomerName & Currency)...

View 14 Replies View Related

Tables :: Adding New Customers To Multiple Tables At Same Time

Mar 13, 2014

I am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).

The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.

View 14 Replies View Related

How To Create Filter Button On Form And Filter Records

Nov 26, 2012

How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.

View 3 Replies View Related

All Payments By Customer Query

Mar 27, 2006

Hello.
I am having dificulty setting up a query. What I have so far is two tables one with customer contact info (name address phone etc..) and one with transactions. I want to have a query that can show me the customer and all of his transactions in one record. I am able to list it now as multiple records but i want to have it all in one record.

View 3 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

Adding Payments Due For Family Members In Query.

Sep 28, 2005

Hello,

I have a query that lists members who have outstanding payments. The results of this query are being used to populate a winword mail merge document.

The problem with this query is that if there are multiple members from the same family who owe then I will get an entry for each of them in my query results. What I would like to do is add the amounts due so that I only have 1 entry for that family. Additionally, since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due. The following is the sql statement for the current query.

SELECT Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName;

Any assistance would be greatly appreciated.

Regards,
Chris

View 4 Replies View Related

Queries :: Start Time - End Time Query

Nov 3, 2014

I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using

SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE
FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID
WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));

how i get end time due to CHECKTYPE]=O

View 1 Replies View Related

Query For Late Payments - Partial Payment Quandry

Jan 4, 2008

Hi all:

Well, as part of my new job I get to revise a current database for a client and I am hitting a brick wall in my mind at the moment and could use some ideas thrown my way on how to deal with it.

First of all I have two tables -

Table AR_Due
AR_Due_ID - Autonumber (PK)
Entity_ID - Long Integer (FK)
AR_Due_Amt - Currency
AR_Due_Date - Date/Time
AR_Due_Waive (boolean and if checked the amt due is forgiven and not due)

Table AR_Pay
AR_Pay_ID - Autonumber (PK)
AR_Due_ID - Long Integer (FK)
AR_Pay_Date - Date/Time
AR_Pay_Amt - Currency


Now, I have to pull all AR_Due where the payments are late (past the AR_Due_Date) and have no in the Waive column and, if partial payments were made those payments can't be past the due date or else they need to be included as late (that's my main issue - trying to figure out the logic on that one).

So, any ideas on how that might be best accomplished? I need a report that shows all late payments (not counting lates more than two years ago) including any where there were partial payments made but the total partial payments were not made before the due date.

I'll take any ideas, although I might not be able to use some of them due to having to follow certain standards here (don't know all of them yet but I've kind of figured a few out based on the existing db). And, I didn't put this all together so there are some things I can't change (at least at this point), but I'm happy to get any ideas that might spark the "aha" moment :).

View 7 Replies View Related

Query - Extracting Customers Between Two Dates

Jan 2, 2007

My customer searches hires a car between two dates - StartDate and EndDate. I want to build an expression that searches my database for all the customers currently using cars (using cars on the day of the search.) How can this be done?

View 2 Replies View Related

Customers Who Have Not Ordered During The Last Week Query

Dec 18, 2007

Hello all, I'm new to this forum.

I'm having problems creating a query for a database (which I am building as part of my university assignment).

The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.

I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.

In case it is needed here is a list of fields in each of my tables:

Customer:
Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode

Order:
Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode

Order/Product:
Order ID (CK)
Product ID (CK)
Quantity Ordered

Product:
Product ID (PK)
Product Description
Product Cost


Any help would be appriciated. Thanks for reading.

View 2 Replies View Related

Query Customers That Havent Purchased In The Last Six Months

Nov 15, 2007

I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.

I have a table with the customer details in and another table with the purchases in.

I have been using the folowing criteria:

Not Between Date() And #01/05/2007#

But i think this still still shows customers who have actually bought more recent than 01/05/2007.

Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.

View 4 Replies View Related

Query To Find Which Customers Have Birthday Today

Aug 13, 2014

Query which I want to create about finding the date of birth of the customer.

I explain:

I have a table (tblCustomers) which among others contains the field "DateOfBirth". I would like to create a query so that looking into tblCustomer about the dates where the month and day of birth of the client is the same as the Date () so that I know every day which customers have birthdays.

View 5 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







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