I am constructing a database for a friend and need to filter data from a daily log to enable a report to be generated for each months actions. I have created fields of: entry date, month and year with the view of filtering by month and year. How do I perform this task?
View Repliesok 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.
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.
Any suggestions?
Valerie
My boss wanted me to develop a way for a department to do a monthly report from Access. In the beginning, I set up a login for the user, then the user would input a start date and an end date to retreive the report. However, I just put part of the report up and will need to put what ever information they needed in the report. So, I did all the queries in Access and it produces correct result. Now I have to some how put it in ASP file including all those queries but dont know exactly how I would do it since I would not have a name for each query that I did in Access. Plus, in Access I used a fix date range to check the query and it works. So, I replaced 8/1/2005 with 'txtStartDate' and replaced 8/31/2005 with 'txtEndDate' and it works for one of the query. Now I when I looked at the rest of the queries I made in Access I would have to make some adjustment including the one that calculate the percentage from 2 queries. Below are several queries I made in Access but will need to somehow incroporate in ASP file.
(I replaced 8/1/2005 with 'txtStartDate' and 8/31/2005 with 'txtEndDate' in ASP file and it works. Need to do that with the rest of the queries)
Query1 (count number of record in group A)
Code:SELECT tblNatureOfCall.NatureDesc, SUM(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountofLogsFROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallIDGROUP BY tblNatureOfCall.NatureDesc;
Query1Total (Sum of count number of record of Query1 group)
Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfDispositionOfPatientID, tblDispositionOfPatient.DispositionFROM tblDispositionOfPatient INNER JOIN tblCallLog ON tblDispositionOfPatient.ID=tblCallLog.DispositionO fPatientIDWHERE tblDispositionOfPatient.Disposition="TRANS"GROUP BY tblDispositionOfPatient.Disposition;
Query1Percent (This find the % from Query1 and Query1Total. Need a code to format in % form as 34.00 not .3400)
Code:SELECT (SUM(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)))/Total AS Expr1FROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallIDGROUP BY tblNatureOfCall.NatureDesc;
Query1TotalPercent (This find the percent of a group out of the total of 2 groups)
Code:SELECT [Query1Total]![CountOfDispositionOfPatientID]/[TotalQuery]![Total] AS Expr1FROM TotalQuery, Query1Total;
Query2 (count number of record in Group B)
Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfDispositionOfPatientID, tblDispositionOfPatient.DispositionFROM tblDispositionOfPatient LEFT JOIN tblCallLog ON tblDispositionOfPatient.ID=tblCallLog.DispositionO fPatientIDWHERE tblDispositionOfPatient.Disposition<>"TRANS"GROUP BY tblDispositionOfPatient.Disposition;
Query2Total (Sum of count number of record in Group B)
Code:SELECT Sum(Query2.CountOfDispositionOfPatientID) AS PatientIDTotalFROM Query2;
Query3 (count number of record of group C)
Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfPatientDestinationID, tblHospital.HospitalNameFROM tblHospital LEFT JOIN tblCallLog ON (tblHospital.ID = tblCallLog.PatientDestinationID) AND (tblHospital.ID = tblCallLog.NotifyHospitalID)WHERE tblHospital.HospitalName <> "HCO"GROUP BY tblHospital.HospitalName;
TotalQuery (sum of Query1 count number of record and Query2 count number of records)
Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS TotalFROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallID;
These are the queries done in Access but will need to incroporate in ASP. Query1 is already in ASP, just need to add the rest of the queries. Dont know how I would do it.
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've got a simple invoice database with 10 amount fields, that needs to be summed up intoa Total including Gst box on a monthly report.
Report only has Date, Invoice Number And Total Amount fields on it, so I need to pull the information from table and sum it into Total Amount.
ie. = sum ( t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10)
and sum above divided by 10% then added together to form total.
I'm just not sure how to do it, everything I've tried so far ain't worked.
Every example I've looked at is only doing maths with 1 field.
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
how to create the report monthly,year and weekly using access 2007?
View 3 Replies View Relatedi have a table on my database from which i generate a monthly report by running a query. when i run the query (date from to date to) i have no problem. it shows me all the relevant records. when i want to generate a report, i input the start date and the closing date for the report. it then gives me an error (reserved error (-1524); there is no message for this error.) i have gone into the table and basically isolated all the corrupted entries. there are 359
what i need to know is how to repair these entries without losing the information. im new to access and this is a simple(kinda) database which i set up to keep track of what the staff in my department are doing on a daily basis.
im running access 2007.
I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.
At this point I can generate a report for any given day.
This could be an example of what I want to achieve
Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0
"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)
I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.
What I am trying to accomplish is import data from a CSV file every month and have it stored as a table and have that table automatically be converted into a printable invoice. I can do the import with no problem. But I am at a bit of a loss as to how I can go about creating the database.
There is only one client that I have to make up an invoice for, but will be more in the future. I need to store the data for each months invoice and then get yearly reports off that data.
#1, How would I go about connecting each months data?
#2, Should I be using a report to create the invoice layout?
#3, How do I go about automating creating the invoice from the imported data? (I am at an intermediate level with VBA, but access seems to be a whole different ball game)
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 RelatedHi there, thanks in advance for any help or input with this. I am working with tables that have annual data on insurance policies that are effective for 1 year. I have Policy number (PolicyNum), current premium (CurrentPrem), the date the policy went into effect (PolicyEffDate), and cancellation (CXDate, which is 1/1/2001 if the policy was not cancelled). I am trying to earn out the CurrentPrem into monthly buckets. The tables are by the effective year going back to 2004, so basically 2004, 2005, 2006.
I'm not sure how to go about tackling this and have tried a number of different ways. I sense that I will need a query that will have 24 fields for all the months over a two year period that any given 1 year policy can be effective over. For instance, a policy created on 5/1/2004 (May 2004) is effective until 4/31/2005 (April 2005), and a policy created on 12/31/2004 is effective until 12/30/2005.
So for every month between January 2004 and December 2005, there needs to be a value generated (either zero or a monthly premium value).
Attached is a sample table with data. Really all I've been able to do is calculate how long a given policy is in effect for (how many months) and then how much the monthly premium is. I cannot figure out how to appropriate the monthly premiums to the corresponding months a policy is effective for.
Note that the current premium value takes into account cancellation which implies that the monthly premium for a cancelled policy is not 1/12 * [CurrentPrem] but 1/[MonthsInEffect] * [CurrentPrem].
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
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?
Dear All:
I have created a database to keep track of transcripts that are requested. This database has their names, ID number, date requested(With a pop-up calender), transcript type requested(This is a combobox where the choices are "Official Copy" and "Student Copy") and address.
So far, it works great. What I wish to do is run a report that I can choose the begining date and ending date for a specified month to see the total "Official Copies" and "Student Copies" for a given month.
Any ideas out there?
Hats off to Colm!
Regards,
Dion
After running a query is it possible to select just one record and place it on a report or print it? If so how do i go about it?
View 3 Replies View RelatedI am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
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 have searched all the forms but could'nt find any answer which would help me , I have created a listbox , it works fine till the moment i set its 'Multi select' property to SIMPLE or EXTENDED , the minute i do that i get no results in my query , could someone please help me with this , i really really need to get this workin!! I have also uploaded my database so u could try it yourself...
View 11 Replies View RelatedI have made a Cascading Combo Boxes form . This form has 3 combo boxes. Its working perfectly
I have to select items in all 3 combo boxes to get a report.
What i want is, if I do not select (leave empty) any item in the third combo box it should give me the report for all the items in the third combo box "Me.SubDesCbo"
How can I do that. I am using the following code:
Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler
Const REPORTNAME = "Yarn Report"
Const MESSAGETEXT = "All Combo's Must Be Selected."
Dim strCriteria As String
' build string expression to filter report
' to selected customer and account
[Code] .....
Basically what I would like to do is create a Combo Box with each month of the year available to select. When I click on a specific month, I want it to pop open a Report based on my table that is filtered by the month I selected. I have a Date column already created in my base table to be my filtering device.
I've already learned how to use query's and reports a bit. I have a Command Button on my form that launches a report based on a query that only shows data for the current year. Likewise, I created one that only shows data for things with a specific item code using a [Which Item Code?] criteria in my query. So in theory, I could do this by creating 12 different buttons, 12 different query's, and 12 different reports. That seems like overkill though and it would seem there has to be an easier way to do that simply by using a combo box to select the month you want to filter.
im new to this so hello every1!!!
umm.. i have a form with a combo box which is linked to a table which gives me customer id numbers. and i have a listbox which i want the names and addresses to appear when i select a cusotmer id number from the combo box
so on the list box i put
" SELECT CustName FROM Customers WHERE CustID=$combo43; " in row source; if i change $combo43 to 0 the name appear but that is fixed and i want it 2 change when i select a cusomer id number from the combo box
can some1 plzz help me, its 4 my college project!
thanxs in advance!
purejoker!
hello,
im trying to select data from an access database via asp. the data must be stored in a recordset and fit the criteria as given in the variable varItemSelected:
heres what i have
SQLstmt = "SELECT * FROM Products WHERE product = " &_ varItemSelected
set rs = con.Execute(SQLstmt)
but i get the error:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters
any ideas?
thanks
I have a database which records time spent on various projects and I would like to be able to calculate the time spent on any particular project between two selected dates. The report and the query behind it already works to display the time accrued on all the dates of a project, but I want to be able to break it down to small ranges of dates
I have a form on which the user selects the start date and the end date. How can I apply this calculated information as a criteria in a query so that I can produce a report with the relevant information on it.
I already have the report and the date selection form designed. The dates selected go into two unbound fields which have a date format.
I hope someone can help me.
Rob
Each record in the database has a subform (one to many relationship) with multiple records. When the user clicks on a record in the subform I want the information from the subform and from the mainform to go into a Word document. I am able to pull the data from the main form but not from the sub form. Any suggestions would be appreciated. Below is an example of what I am using to get the data out of the main form for the bookmark in Word called "ClientName".
.ActiveDocument.Bookmarks("ClientName").Select
.Selection.Text = (CStr(Forms!frmClients!txtFName) & " " & (Forms!frmClients!txtLName))