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 Replies
ADVERTISEMENT
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
Jun 2, 2007
I have a database that is like storage unit business. It gives you a fee once you select which unit you take for rent. When the balance is paid off, and new Month comes around i want the database to create a new balance on the table, how would i be able to do this?
I know that i can do it in two ways as i was told but i dont know how and where to start. I create some of it but i dont know what to include in the appended query etc.
i was told that In Access Help, look for COMMAND LINE OPTIONS and you will find -X macro, which is a way you can run a given macro from a command line.
"Create an append query that adds a $40 charge to every open account. Put this query to run in a macro. Create a separate icon that is private to you so that only you know where it is. Edit the icon properties to launch access on that database file with the -x macroname appended. Now use Windows Task Scheduler to launch the icon at a specific time of day on the 10th of each month. Don't forget that the macro has to end with a QUIT action."
View 1 Replies
View Related
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
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
Mar 4, 2008
Hello,
Thank you for your time.
I want to set up an automated recurring monthly invoice for clients in my database.
I know this is possible but I am having trouble finding the solution and in fact having problems how to search for a solution.
On the 1st of every month I would like to have a query run that inserts a new row into my invoice table for all current clients invoicing them for that month.
Please please help. Invoicing one at a time using a form is taking far too long each month.
Thank you.
View 3 Replies
View Related
Feb 27, 2008
Okay I'm in the process of making a database for a Uni class and need help. The assignment requires a query to be made on a table. The query is meant to be on a table holding records of trainers teaching courses. The query is meant to obtain results of trainers teaching more than one course. I need to know how I can query recurring names or a recurring ID used. Thanks in advance. :)
View 2 Replies
View Related
Mar 6, 2006
I have Datein and Dateout fields with many rows of dates beneath each. I used DatePart in a query to convert each date to a week number; but now need to count, and total, how many times each week number occurs under each field.
I'm sure this can be done easily, but I'm new to expressions, functions, etc, and would greatly appreciate some help!
View 6 Replies
View Related
Jul 7, 2014
I am looking to make an invoicing database.
I create 30 invoices a month. each client gets billed the same amount each month (for example Customer A gets billed $100 every month, Customer B gets charged $200 each month). Only two things get changed on the invoice-'Description' (for example the description would be 'services rendered for July 2014' for July invoice) and 'Invoice Date'.
Now please see the image attached, I have an append query that combines information and creates invoices for all 30 clients.
The problem is, the 'Invoice No' field in the invoice table stays empty, because I do not know how to start numbering at a specific point (for example invoice numbering should start at 14150001) and I want it to add the number (+1) automatically when this append query adds data to the invoice table.
View 13 Replies
View Related
Jul 8, 2012
I need to design a simple clinical database that will allow me to collect and display recurring data. Example. Every year a patient comes into clinic for follow-up. They complete a fixed data set questionnaire, their vital statistics are recorded and any new clinical results recorded. I have created all the tables/Forms but i don't know how to automatically add a new event that repeats the same data. So far i have 5 excessive years worth of follow-up data. Sadly, I have had to make massive tables by copying the identical data with either a 1,2,3,,, to signify a different year.
View 6 Replies
View Related
Aug 20, 2014
See the attached png for sample records.
Many Regions, Many Towers, Many Countries and all..
Has to create a worksheet for Each Region-Tower and Paste the countries' records.
Private Sub Input_Click()
Dim Mainrset As Recordset
Dim Temp As Variant
Set Mainrset = CurrentDb.OpenRecordset("Query_Form")
Mainrset.MoveLast
Mainrset.MoveFirst
[Code] .....
The above code is not correct as some Tower/Process are not associated with some countries. Usually What I do is to loop through all the records and look for the changes in the field. Is that the only way?
View 12 Replies
View Related
Dec 13, 2011
How do I set up a field to store irrational or recurring decimals?
Eg 1/3, 1/6?
I'm trying to store times, so they can be added up later: The idea being that 1h20m would be 1.3333333333333333333 etc.
View 2 Replies
View Related
Feb 24, 2014
I am trying to track a yearly training in Access 2010 but am not quite sure how to accomplish it. The training originally was just a one-time thing, due within two weeks of a new hire's start date so I currently have it displayed as three fields in my employee table (Due Date [Calculated], Completed [Date/Time], Paid [Yes/No]). Now, employees will be required to complete this training every year (beginning January 1st, not from employee's start date) and I don't know how to accomplish the tracking it.
I need to keep each year's completion date and whether or not the employee was paid. Currently, the "Paid" field is linked to a query that populates a list of employees that have not completed the training. If the "Paid" Yes/No box is unchecked, the employee's name will be listed in the query; once I check the Yes/No box then the employee's name is removed.
What would be the easiest way to track this training for each year? There are several other training that I am tracking but are one-time only events; these too are separate fields in my Employee table.
View 1 Replies
View Related
Mar 4, 2007
at the moment i am making a system that calculates the cost of gym membership cost. however, i am stuck on how i can determine whether or not the member's have paid. i have a make table query that calculates each members fees. i would like the payments to be made quarterly. does anyone have any ideas on how i would be able to do this.
View 1 Replies
View Related
May 17, 2007
I have a database with 10 properties which I want to record details of weekly or monthy payments. I have a table for properties, one for tenants details and one called payments. I then have them on a form. I want to open the tenants form and insert a command button to open the payments form and record payments as the occur but i can only enter one - it is giving me an error message as I am duplicating a record. The tables are linked with a property reference which I have set as the primary key. I have obviously done it wrong. Can anyone advise me of a way to do this please.
Thanks Tracey
View 1 Replies
View Related
Mar 21, 2007
i am making a db for a gym. the members pay yearly and the fact that they have paid is shown by a tick box in the members table. i therefore want to reset these boxes to 'No' at the beginning of each year. anyone got any way that this could be done.
thanks
View 4 Replies
View Related
Feb 15, 2012
How to create a table that has a recurring entry, based on a start date and a nominal interval period of e.g. one calendar month?
Is it possible to have these entries applied automatically each time so that they appear, as required, when the table is opened?
For example, for the entry of regular payments due per month.
View 3 Replies
View Related
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
Feb 26, 2006
i am unsure as to whether this can be acheived in a database..
basically this task is almost calendar like.
the company operates 7 days a week.
i have a table of company drivers and each has their own driver ID.
my user would like to store what time each driver comes into work each day and what time each driver leaves. this needs to be quite flexible however because some days certain drivers wont work, but each day..if a driver has a 'come to work' time then they will for sure have a 'leave work' time.
each driver also pays rent to the company once a week. within this calendar like system i would also like to store on what day the driver paid what amount towards their weekly rent.
i cant quite imagine how this can be done in a database. but if these kind of applications are common, i would be most grateful for some direction.
thank you.
one more thing..
i would also need to record during each day what time the driver left and returned from lunch (sometimes our drivers leave for a few hours during lunch time)
i want to store this because if they miss a job while they are on an extended lunch break im gonna roast em.
View 2 Replies
View Related
Apr 27, 2007
hello all.. trying to resolve an issue in my system..
basically i run a taxi firm.. for those that dont know.
this issue deals with credit card payments, and in particular payments for multiple transfers.
lets say you are booking 2 journeys..
the first journey is on 2nd June from London Heathrow to your hotel in Kensington
the return journey is on 5th June from your hotel back to Heathrow
you want to pay for both journeys at once with your credit card.
both transfers cost £40 each, 40x2 is £80 plus 5% transaction fee is £84.
we keep the credit card details on file to save our passengers time when they want to pay for their journey using the same card.
so lets say we have a job table.. and a credit card table..
Card Table
cardid (pk)
cardtype
nameoncard
last4digits
secnumber
expirydate
startdate
issuenumber
Job Table
job id (pk)
jobdate
jobtime
jobnotes
should i create a new transaction table which holds the jobids that were paid for and the cardid that paid for it..?
shall i include the price of the job in the jobtable.. and store the transaction fee in the transaction table?
if this is clear i would like to hear a few opinions on this.. i will be thinking hard about this over the next few days so i might come to some conclusion but what do you guys think
cheers.
the reason im asking this is because at the moment i am storing the credit card details.. pricing information and job details in the job table..
when i send my passenger an email confirmation.. it sends them the job details, card details and payment details..
this is fine for single transfers.. but if they book more then one transfer i have to send them multiple emails outlining each job individually.. and if they wish to use the same card i have to find their card from the original job and copy those details.
if someone books 4 jobs and pays for them all with one card and one transaction i really have difficulties, i would have to send them 4 emails (with 4 job details respectively) and i would have to include all the payment details in each job to verify it has been paid.. this means i would have to enter their card details 4 times.. and even though each job might be £20 each.. i would have to write £80 in the total charged field because it is covering all the jobs..
anyways.. if you can spare me a thought i would be grateful.. thank you
View 8 Replies
View Related
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
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
Jul 16, 2014
I have a project to take. It relates to school fee payments by students. I have created tables, query and forms for the same. The database is able to calculate the Balance using the query. Now my major issue is how do I go about Calculating the Balance for the second fee payment, given that the amount payable on the next payment will be the previous balance. I have tried to think using the nested if to try it, but invain!
View 4 Replies
View Related
Sep 10, 2012
I'm trying to design a billing and invoicing database and can't seem to figure out how to link the tables and track the payments made by customers. How can I proceed to track the payments and make payments applicable to certain line items or invoices? If the line item hasn't been paid, it needs to show up on the next invoice and be included in the total due.
View 14 Replies
View Related
Oct 12, 2015
I have built a simple database for a dance school.
The junction table that handles enrollments is behaving a bit weird.
I have a form that deals with class payments. This is linked to the enrollments table via datasheet.
When i refresh the datasheet (which is embedded in a form) after a payment the first record in the enrollments table changes its value.
Effectively the first student changes to whatever class i am currently viewing.
View 5 Replies
View Related
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
