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.
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
I'm trying to create a pop up form that asks userto select months for a report to preview. I've writtent he query and done the report. I can do the command button to get the selectmonth form to pop up, but not sure how I pass the selected months into query for the reports. Any tutorials on this or can someone help. I've been looking around but nothing quite fits. Quite new VB also, but have a good understanding programming generally.
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 receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.
Here's what I'm tracking:
I get monthly excel reports that have unique values from a "Project ID"each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etcSometimes new projects are addedSometimes old projects disappear from the reportA few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.
Here's what I'm trying to automate:
Find out what Project IDs are newFind out if old Project IDs are missing from the new reportSomehow "flag" or identify projects that have status changesSomehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc
Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
I am requesting guidence as well as some advise. I have a database table that is linked to our business software. The data is very very filthy and needs to be scrubbed.
Here is what it requires:
1. TRIM 2. CONCATENATE 3. Blank cells filled with NA
What I would like to know is, can I group these three commands into one query? I want to execute all my commands into one table. So far, I have done each query and created a table for each one. I know this is antiquated method, hence the posting.
Can someone shed some light on how to do this efficiently?
Greetings, I'm a bit new to the world of MS Access and have encountered a problem when making a search form for my database. The query is using a Like Condition with the '*' Wildcard for a single field like so:
Like "*" & [Forms]![frmPickUpReport]![txtAgentName].[Text] & "*"
While the query on its own runs correctly from the viewer of course prompting me to enter the data manually the actual data from the form is being ignored. Instead of taking the text value within my form's text box, when I press the report creation button the same data box is prompted to me asking for me to enter the data manually.
anybody has a solution?
I'm using MS Access 2000 and cannot use any other application such as visual studion
There are 3 drop down button that should filter category of items in a database:
Category 1 Category 2 Category 3
Whatever users choose in category 1, it will filter the items in Category 2. whatever users choose in category 2, it will filter the items in category 3. users can choose 1 to 2 categories or sometimes the 3 categories. the items will show in a subform. the subform are getting the items from a query. the dropdown button gets its value from a Table.
My challenge is that, i want to get the number of items in Category 1 once users chose it. if they choose something in Category 2 the number of items will change also. there will be 3 textbox for Category 1 COUNT, Category 2 COUNT, and Category 3 COUNT.
as of now, what im getting is only the filtering of Category 1 and Category 1 COUNT.
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?
I 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.
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.
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?
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)
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.
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.
