What I have: A table with multiple Date/Time columns. Here's an example:
What I would like: A month-by-month summary counting the number of entries in each of the three columns, like this:
I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).
Smaller Pieces: I am able to create such a summary for one column at a time:
Not what I'm looking for: I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:
I have attached the example file for your convenience: Database1.accdb
To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
I am basically looking for a way to display the total number of records for a selected number of tables and queries in my database on one screen or on one single page view. The problem with using reports or forms is that they tend to bind you to a single table as a record source. This summary would require multiple tables and queries. The summary data would look something like this with a total coming from a Count() or count type function:
# of records: 3000 # of records to insert: 500 # of records to delete: 400 # of records to update: 2100 # of records changing location: 100
I have a database design problem that I cannot wrap my head around. There are three tables roughly like this:
tblUsers tblAccuracy tblRecords ----- --------- ------- UserID UserID UserID Name MonthYear Date ... Accuracy NoOfRecords
tblUsers and tblRecords are joined by UserID (Text) in a one-to-many relationship.
tblUsers and tblAccuracy are joined by UserID (Text) in a one-to-many relationship.
tblAccuracy stores User accuracies on a monthly basis where [MonthYear] is a Date/Time column formatted to "mmm yyyy" with each entry being the first of each month (eg. 01/01/2008 to display Jan 2008)
tblRecords stores [NoOfRecords] on a daily basis for each [UserID] where tblRecords.[Date] is a Date/Time column.
---
The problem is that I need a way to query these tables to return a [UserID] from tblUsers, the list of all records entered by that [UserID] for a given month on a daily basis from tblRecords and a single [Accuracy] figure for that [UserID], for that [MonthYear] from tblAccuracy.
tblAccuracy and tblRecords have no primary keys and I'm not sure how to normalize them, or if it is even possible. [MonthYear] is stored eg. as 01/01/2008 for January and [Date] has many records for January and I don't know how to relate them.
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Hi there. Im trying to work out how I can count the dates in my database per month. What ive done is created an unbound box with an sql statement
SELECT Count([tbl Main].[Date of Call]) AS [CountOfDate of Call] FROM [tbl Main] HAVING (((Count([tbl Main].[Date of Call])) Between #4/1/2006# And #4/6/2006#));
When I select the totals button to count the records 0 are pulled out.
Does anyone know if im doing something wrong here. As always im sure its quite simple..
How can I generate a report that contains multiple summary total by year. I run the query to give me selected time period(s) for my report. For example: I want to have the summary for the date ranges from the year 1994 to 2001, and then 2002, 2003, 2004 etc. I'm having difficulty for the first summary total which is the date range from 1994 to 2001.
I have a situation where I use the output of a combobox in a query. When I run it everything is fine.Now I want to make a summary of 9 of these text fields in the query. However, when I do this only the bound columns show up in the summary field. I can't get a summary of the second columns of the combo in the query.
I have been using the following successfully with regular text fields:
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
I have a very simple single-table database with 23 fields. Some of the records have only two or three fields populated. I would like to be able to print a summary of only the populated fields in each record.
It would Ideally look something like: Record 1 Name Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content Field 10 Title: Field 10 Content - Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content Field 21 Title: Field 21 content - Field 22 Title: Field 22 Content
Record 2 Name Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content Field 10 Title: Field 10 Content
Record 3 Name Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content - Field 21 Title: Field 21 content Field 22 Title: Field 22 Content
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID Employee Name Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7 ....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
I have a report that is a summary of multiple records in it. What I have next to the record on the report is a button.I have report that are labeled:
rptIncident Summary rptIncident Report and a button labeled "open report"
I would like to click on the button and then it open the rptIncident Report with all the information, not just the summary. I will put a picture so you know what I am trying to accomplish.Is this a simple Onclick event with a where condition or what? Or does this go beyond to having something to do with VBA?
Is it possible to select 1 MAX date from multiple date fields for a record?
For example, on an employee record there are 10 date fields, each for a performance review date. Some employees may have date fields 1-3 with values, some with just 1, others 1-5, etc.....depending on how many performance reviews they've had.
Is there a way to pull the MAX review date for an employee, knowing that the MAX date could reside in Field 1 for a certain employee, and could be from Field 7 for another?
I am currently developing a database to provide a friend with an auction tracking and ordering system.
I will have a number of questions the first is related to the Home Page/Dashboard/Summary form I am creating. I basically have a few sections, one of which is a combo box offering the user to select an auction to view in the summary section. This summary section contains the list of lots but I also hope to to expand on this and create multiple text boxes, each containing the answer to a number of queries (totals etc).
I have written all the queries and can see the results however as the form does not have a record source I would like to know how to make each text box populate with the result of different queries (ideally in vb - I am using ms access 2010)?
I have tried a few things, control source doesn't apply as I have no record source (i guess). I investigated Dlookup/Dcount but am unsure if these apply for the same reason. I understand I am likely to need a recordset etc in vb and have already tried a few things but unfortunately none of them work.
Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!
Hello all, I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08 Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08 Previous entry Date = 20/08/07
I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.
SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service] FROM HMR GROUP BY HMR.[Claim #], HMR.[Date of Service]
I have a Access database of employees and I need to count how many worked for each organization in the company by month. I can't figure out how to break this problem down and solve it. I can do this ten ways from sunday in VBA, but I'm looking for an SQL solution.
My data looks like this (simplified) example...Table.jpg In this database, a process runs nightly and adds any new employees with the [CreatedOn] date. This value never changes. The [LastUpdate] field is updated to the time the nightly process executes as long as the employee is still at the company. So when an employee leaves, the last update field is no longer updated. So in my example data the employees in the 1st, 4th and 7th line no longer work for the company.
I've been trying to produce a query(s) with these results...Results.jpg I've tried several incarnations of various queries with subqueires to filter [CreateOn] and [LastUpdate] so that I can count the total employees in each organization at month end. None produce correct results.
My goal is to ultimately produce a chart that looks like this...Chart.jpg
I have a table where I use 3 date fields in, all with different views.
Field 1: Date as dd/mm/yyyy Field 2: Quarter: q (shows the same date, but then as quarter of the year. Field 3: Year: YYYY
Now I have a form that fills all of my fields, but is there a simple way () to populate the other two fields Quarter and Year from the value I entered in Date? So if I change Field "Date", Fields "Quarter" and "Year" updates as well.
Maybe I don't need to do it in the form, but in the table?
I'm not that good with VB- scripts, and I'm not sure If it is possible to auto populate the table field Quarter and Year directly in the table itself.
I'm trying to create a query that has a calculated field that shows a maximum value from multiple fields.
As far as I can find, this is not built into Access, so I've used this code from a Microsoft page:
Code: Function Maximum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values.
[code]....
The problem I'm having is, well this doesn't work for me.I'm a bit of an beginner VBA coder, but I understand this code and don't know why it won't work.It only displays the value from the field within the brackets.The values used are Dates, so I need to display the latest date from multiple fields.
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name Start Date Midpoint Date Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
