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
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..
employee name
joining date
department
section
employee left (Yes/No)
Left date
Now I am looking for;
i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start
ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)
iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise
iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)
I'm trying to make a graph of Monthly profit and loss
I've got 2 tables: Invoices and Purchases.
Invoices contains the fields:
InvoiceTotal and InvoiceDate
Purchases contains the fields:
PurchaseTotal and PurchaseDate
I know I need to take one from the other to create my profit figure but what I can't figure out is how to incorporate the dates.
InvoiceDate and PurchaseDate might be different but both occurred in the same month, so I would like to sum my PurchaseTotal and InvoiceTotal during this month.
so the query I'm looking for would look like:
| Month | SumofPurchaseTotal | SumofInvoiceTotal | Profit |
I would then graph month and profit so my company could compare it's monthly performance ...
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 RelatedI have a table with certain parameters in, and I need to update it in a monthly basis with new costs. Basically, its a list of component costs which change on a monthly basis, and the query/report needs to pull the latest version. But, the old costs need to be kept for legacy/comparison purposes.
TblMbM Layout (titles)
-Unique_ID
-title
-code
-Feb Cost
-March Cost
At the moment, there are no costs in the march column, but i know next week i will need to update tblMbM with the latest costs.
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
I'm creating an accounts package..I've used access chart wizard to create a chart that shows total gross income per month.This displays correctly but the months start at January and end in December. It would be more useful if the months could start and end for the financial year. The syntax generated by access for the current implementation is:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
How do I edit this to make say September my start date?
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.
ok i think i know what i need (searching the forums gave me the idea)
i have query that generates a report, i need this report to be flitered monthly
is it possible to have afield where i enter the month so when i press the monthly report button it just prints any entries in that month
please can someone oint me in the right direction.
Hi, I have Three tables
the first is a ContractTable
the second is a Monthly Invoice Details Table
and the third is a Detailed Payments table
Contract 1 -> Many Monthly Invoice 1 -> Many Detailed Payments
what I would like to do is ensure that there arent any duplicated Months for a Contract in the Monthly Invoice table
in other words For Contract 00023 I NEVER want it to have more than one invoice record for the month of JUNE 2006....
It may be easy but it has me all tied up, please assist.
I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!
I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.
table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)
My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.
I have played around and got this to work using querys with grouping and sums.
My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled.
Any ideas?
I am Trying To calculate Costs for a particular month how do i do this when taking into account that the months obviously hae different lenghts? I have the overall week cost but how do i do it for the month?
View 3 Replies View RelatedI am trying to create an inventory which list sales by month. I have created a crosstab query from the detail history table which works fine except I would like a record for each month whether there was sales or not. So on the report I would have all 12 months for each item with the months with no sales displaying zero.
Any suggestions?
Valerie
I am having issues trying to run a query. What we have is a private club that keeps track of its members with an access 2007 database. What we need to do is to be able to print out new members monthly for a state audit. The way it is setup now, our members reports prints all of the members since day one. For example, we have a state audit coming up next month for November and we would only like to print out members that have joined during the month of November, but our report is printing out all of the members since we started in August.
We do have a date joined query. The fields we would like to print are: Date Joined, Member ID, FName, LName, City, State
Any help would be greatly appreciated, thank you.
HI THERE!
I'm having troubles creating a query that will be able to view/print out the current records in the next month.
For example:
From today's date (21/02/08), I want to print out a report of all the confirmed court cases from today until the next month.
And I could have an option of all the dates of specific months.
Is this possible?
Thanks guys.
Every month for my construction company, I receive a pay application that consists of the following:
1) about 100 "Line Items" (each one being a different type of work performed)
2) a Subcontractor associated with each line item (a line item may have only 1 Sub, but a Sub may be listed multiple times for different line items)
3) Amount of work performed (in $$) on that line item by that particular Sub during that pay period.
My boss wants a db to track all of this info. She thinks the best way would to create a new table for each Pay Application, every month. Basically, we would be starting from scratch every month with a blank table. But, I think it would work better if the months were linked to each other in some way.
I could have a field called "Pay_App" and enter 1 or 2, etc. on every row for every line item every month. But, then each month, I would be entering a 1 (or whatever the month is) for every single line item and that seems stupid. I want to be able to pull up the entire pay schedule for a particular Sub (all of his payments throughout the entire job). Is there a way to link the different pay application tables once the data has been entered? Or is there an easier way to do this? I would appreciate any help that can be provided. Thanks.
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!
I hope I can convey what I am trying to figure out. I am at a loss right now. I have a form where I input personnel information. One of the fields is for how many months experience they have with the program. What i would like is for this record to update itself every month. for example, if they initially had 2 months experience, I would enter 2. Every month therafter, that number with change to 3, 4, 5...etc. Not everyone comes to our section with the same experience, so most will have a different starting number.
This information would be seen on the personnel form, and in a report to show experience levels. Other than those two, it is not called upon.
Any suggestions would be so helpful!
Thank You!!
Hi,
I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867
The expression I’m using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)
The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867
I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response
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 RelatedHi 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
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
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
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."
Hi all,
I need to create a MONTHLY report and I need help, please!
I created 2 combo box on the main form. The first combo box for the month drop-down list of Jan, Feb, Mar, ..., Dec. The second combo box for the year 2005, 2006, 2007, etc...
On the report underlying data source table, it has the Invoice Date field mm/dd/yyyy (for example: 11/01/2007)
I would like to select to view the report for the invoice of the month of October, 2007. How do I do that if I choose Oct and 2007 on the 2 combo boxes and click the command button View report to pick up the report for only Invoice Date during October 2007.
Please help. Thanks so much!!
On the below data i am trying to get average monthly BALANCE, based on the following query ....but i only getting all records with actual balance and not month wise average balance of the customers......
-----------------------------------
SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, Month ([customer profit].[Balance Date])
FROM [customer profit]
GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];
[Code] .....