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."
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?
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.
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. :)
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!
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.
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.
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?
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.
I am creating a db for a property management company. They want to be able to enter the monthly payment made each month on each property (there will be multiple properties involved) and also have a field that totals all payments made by the tenant over the course of their lease.
What might be the best structure for this type of situation? Should I create 12 fields for a year of monthly payments for each record? Should I create a seperate table for each property and it's payments? Should I create an Excel spreadsheet for the payments and link that file to the db as a table?
Any help and suggestions on this are greatly appreciated. Thank you in advance.
Hello,I'm designing a contract and payment processing database for a friends construction company. I've made an attempt at designing the data model, but wondered if anyone out there had ever come accross a similar scenario and could be of any help. I have attached a copy of my initial ERD.Entitys are as follows:CompanyHolds details of the company that are operating the system.CustomerBilling details for the customer.SiteA customer has many different sites at which work is undertaken.ContractA contract is created for all work undertaken at a customer site.PlotThe work a contract covers is carried out on various different plots at a customer site.StageWork is carried out in stages on a plot by plot basis. When a work stage is complete the stage can then be invoiced for payment.InvoiceOnce work stages are complete an invoice can be raised. An invoice can be for one or more stages (always under the same contract).Invoice_LineDetails of the different stages billed under a single invoice.PaymentStores detail of a customer payment.Payment_AllocationA single payment may be split accross multiple invoices. A single invoice may be paid through multiple payments.The main area I was hoping for help with is payment allocation, the requirements of which are listed below:1. When a payment is received it must be possible to split it accross multiple invoices (a payment will only ever be for invoices within a single contract).2. It must be possible for an invoice to be paid in part (i.e. be paid by mupltiple payments).3. If a payment isn't allocated in it's entirity, the the remainder should be put to a payment on account for allocation at a later date.4. An entire payment should be able to be put to a payment on account and then allocated later.5. It must be possible to enter credits against invoices.The reason I have Contract joined to Payment_Allocation is so that a payment on account can be created and linked back to the contract, for allocation at a later date. If a payment on account is stored as an entry in the payment_allocation table it will become difficult to then allocate the payment on account to invoices and keep an audit trail. The payment on account isn't really allocated until it is put against an invoice so I'm loathe to do it this way.On the other hand if I store the payment on account in the payment table (until allocated) it may make it harder for me to produce my statement report.I'm also having difficulty seeing how I can build a statement of account from these two tables...do I need to build up a transaction table in order to do this effectively? Or a temporary table of transations to base my report on?Any advise or pointers would be very helpful.Thanks in advance for your time.webbmatt
We have a special reduced rate that a client must prepay 12weeks before or sooner if a date is put in the [OverRideDate] field
I have been trying to construct a Query / If statement or should I use case statement to work out the actual payment date. Taking in to consideration all our funny little variables like
I will try to put the variable into words
[DateOfArrival]-84Days unless [OverRideDate] & Not Less than today then check its not a [ClosedDates] from the tbl_ClosedDates Then if it’s a Sunday –1 day as the accounts lady doesn’t work on a Sunday
Their simple????
Attached file with sample variable data BalanceDate.mdb
This is the second edited section: I got the update query to work properly. Is this the best way to solve my problem?
This is the original edited section: For the problem below would it be possible to use an update query to update the table? Criteria for the termination_date field would be "is not null" payment_to_date Update to: field would equal DateDiff("m",[payment_start_date],Now())*[monthly_payment] Is it possible to use other fields in the table as part of the UpdateTo: calculation? I get an error when I try this.
This is the original question: I have a field in a table called payment_to_date that I want to always be populated with the current total of how much a customer has paid to date based on their monthly payment rate. I have a form for data entry into this table but once their account info is entered the first time their won't be any changes to the account unless they terminate the service so calculating from an event procedure on the form doesn't make sense to me (I am new at this).
I entered the following in a text box control source on the form and it works but the textbox is not bound to the table so it's not the right way to do it.
=IIf([termination_date] Is Null,(DateDiff("m",[payment_start_date],Now())*[txtpayment]),(DateDiff("m",[payment_start_date],[termination_date])*[txtpayment]))
The calculation from above is basically: If there is no termination date then the payment to date = the number of months between todays date and the date payment was started multiplied by the monthly pay rate.
Can anyone tell me how to keep the information in the table current for the payment_to_date field?
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)
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 :).
Hello everyone. I'm a little new to the Access scene. I have a strong background in C, C++, Java, and assembly. Seems all the access books out there fail to mention the need to know vbs, heh.
Anyhow, I have a form ([WrkR]) based on a table ([ORDERS]) that has a customer's name ([Tables]![ORDERS]![name]), total payments to visa ([Tables]![ORDERS]![visa]), and total payments to cash ([Tables]![ORDERS]![cash]), and total payments to debit ([Tables]![ORDERS]![debit]). My form also has three rows, each containing a text box which holds how much a person has paid, and a combo box ([combo1], [combo2], [combo3]), that allows the user to choose what payment type was used. I have included a screen cap to demonstrate this.
I need to be able to create a function or macro, anything that will sum up the appropriate totals and store them in the corresponding columns in my table. Like in my example, there are 2 visa costs and a single cash cost that should be added up and stored in my table in the appropriate fields.
I have a database where my team will enter manual payment calculations into. Once entered, they will run and print the report for actual payment.
The report I have groups by payment type (see attached image of paymetn types) and then sub totals by group.
I need to somehow get these totals and use them to generate a gross payment. In the attached example, the gross would be the sum of worked hours + before tax allowance + after tax allowance. I'm not sure how I can do this in the group footer.
Some students pays fee for one month, some for two months and some 3 months.
School administration requires me to print notices for every student according their payment mode after comparing with next due months and already paid months.
Some notices will show their fee for the months as
(Id= 1 Jan-14, Feb-14), (Id=2 Jan-14, Feb-14, Mar-14), (Id =3 Dec-13, Jan-14) Considering as Id 3 has not paid due month i.e. Dec-13 Fee and his payment mode is 1 month only.
I am new to Access and have started to move across some Excel based programs.
I have a simple example with two tables and a form where on the form a customer ID is entered which displays the name and below it is necessary to enter the total payments made by that customer for the day ( all records for the same day are entered at one time, which is why I would like to autofill the date).
I can enter the date directly on the form for each customer entry but I would like to automatically enter the required date in the payment table using the value of a datepicker on the form to ensure that no date errors are made.
I have been trying to use the expression builder to do this but the datepicker does not seem to appear as a valid option.
