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.
View RepliesI've got a table with a lot of data.
In the table is a SAP Number (ID), a booking date (BuchDatum) and an amount (Betrag)
Now i want to sum up, for each ID the amount in one months.
In the end i want to have a table like this
Date SAP Number Amount
01/2011 12345 1000€
02/2011 12345 0€
03/2011 12345 100€
04/2011 12345 300€
and so on.
So for each ID, a table like this.
I tried it already, but my Problem is that i either sum up the years or the months. I Need a combination.
I put the file in the Appendix.
I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.
I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.
The Query has two fields "Agent Name" and "Logger Date"
The report displays the data as follows
Agent Name Logger Date
User 1 26/5/13
User 1 26/5/13
User 1 27/5/13
User 2 28/5/13
What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.
User Name Login Count
User 1 3
User 2 1
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
I have a database for training. I have a table of employees with one of the criteria being active which is enabled by a check box. I have a second table of all of the training data. When I add a new employee, or I want to schedule someone, I want to be able to have a combobox on a form which I can select the type of training and then with a push of a button find if there is an active employee which has not had this training through results of a query.
View 14 Replies View RelatedI basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies View RelatedI have a qry which gives me this data:
date/code/hrs/amount
12/05/15 ABC 4 243.55
12/5/15 DEF 6 707.12
13/05/15 ABC 1 101.01
etc..
I'd like to sum the amount for each date. Is this possible?
I will be updating the table with new dates and want to eventually create a form where i set the dates and it does the query where I get the amount for the dates specified, e.g. 01/07/20 - 08/07/20
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
I am looking to achieve a column within a query which gives me the count of records that are equal to a string in a different field, e.g.
Field1 Count
aaa21 3
aab01 2
aaa21 3
aaa21 3
aab01 2
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 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]
[Code] ......
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 want to Count the amount of not treated improvements after 1 month in a report. The rules will be if the improvement cell is empty one month after the date the improvement is registered, then it should be counted.
View 3 Replies View RelatedI am trying to get active member count by month in MS Access.
Table looks like this:
admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015
Required output would be like this
2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..
I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)
Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies View RelatedI have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
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've two fields to work with:
[Date of Device]
[DischDate]
If i was explaining it, it would be as follows:
If [DischDate] Is in the next month after [Date of Device] then Y else N.
to add for example if the [Date of Device] is April 2015, and the [DischDate] is also April then i'd expect a N answer
to add for example if the [Date of Device] is April 2015, and the [DischDate] is May then i'd expect a Y answer
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator
FROM MatterContactsMade
GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator
HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator]))
ORDER BY MatterContactsMade.MatterId;
In form (datasheet) I have a three columns "RightColors", "LeftColors" and "AmountOfColors"
I want to do something like this:
If I fill 'RightColors' "red;green;blue;", and fill 'LeftColors' "orange;" then in 'AmountOfColors' column should be "4". Sometimes I can fill only 'RighColors' or 'LeftColors;
I think I should add code in after update event. Is this possible to build code which can count colors using ";"? I need loop for this, right?
I am trying to get deposit amount for the current month but results are in accurate i am using this
DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I have a field named 'Date' and need to run a query that selects all the records where the date value is within the current month.
Anyone know how to do this?
How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
View 8 Replies View RelatedHello all,
I have the following data (Note: dates in dd-mm-yyyy format)
ID DEPT TARGET70 TARGET90 TARGET100
-- ---- -------- -------- ---------
12 ACCT 12/06/2005 31/07/2005 21/12/2005
14 MKT 30/08/2005 02/09/2005 11/04/2006
19 ACCT 13/04/2004 13/05/2005 22/08/2005
20 SAL 12/12/2005 20/12/2005 12/02/2006
21 SAL 21/03/2004 21/03/2005 21/09/2005
etc. etc.
It basically shows what the target dates (TARGETxx) are for certain stages of a deparment’s (DEPT) different projects (ID).
What I have to do next, is proving to be quite trivial to me. Can someone please help? :)
I must compare the target dates to a certain date (which is stored in a global variable, say gdatTheDate), and then return all the projects (ID) for which a target date is later than gdatTheDate. But it (the amount of later dates) has to be counted for each dept and presented as follows:
Say gdatTheDate = 05/08/2005
Then the following is returned
DEPT NUM70 NUM90 NUM100
---- ----- ----- ------
ACCT 0 0 2
MKT 1 1 1
SAL 1 1 2
I know how to use the global variable in a query, but how do I implement the query to do the counting and grouping etc.?
Any help would be appreciated. Thanks,
J
I am using the following code to get results from next month,
Code:
Month([ptdate])=Month(Now())+1
which works great to get next months results. I try to get what is left of this month by adding the code:
Code:
Between Now() And Month([ptdate])=Month(Now())+1
This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.