Count Of Non-duplicated Clients On Monthly / Quarterly / Yearly Search
Mar 19, 2014
I'm new to Access. It is obviously a very powerful database program I've developed a data base of my agency's service purchases for a variety of clients. I have to generate a report totaling the monthly purchases and counting the number of unduplicated clients. We make multiple purchases for clients monthly. There must be a feature on this program that will sort through the client ID Numbers and count them (rejecting duplicates) in that one month or quarter or year. I've been exporting to excel, sorting by client ID and then counting each new ID number.
View Replies
ADVERTISEMENT
Jan 26, 2008
Need a little professional advice.
I have a product table. Those products expire over time. Their shelf life is monthly, quarterly, or annual.
What should I use for monthly, quarterly, and annual in my table.
My first thought was 30, 90, and 365. Then in my query I would use the datediff function. If the datediff was >= to ...then ...
However there are months with 28,29, and 31 days. With this quarterly can change as well. The annual as well by 1.
What is the rule, if there is one?
View 9 Replies
View Related
Mar 10, 2015
how to create the report monthly,year and weekly using access 2007?
View 3 Replies
View Related
Jul 29, 2005
I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!
View 3 Replies
View Related
Apr 30, 2014
I need to be able to count how many fields per date. I've tried several ways to add this to my query, but nothing seems to combine the dates, it just gives me nothing or 1 as the count for every line even when it is the same date......
View 3 Replies
View Related
Jul 31, 2007
The below code gives me a count based on a month and I would like to get the same count based on date range. I use a calendar control to provide the date range. See quote below: I am hoping someone can steer me int he right direction to convert the code from monthly count to a date range count.
Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]
SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS FORMS,
DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS PQC,
DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)") AS ECN,
DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS MCN
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)");
View 1 Replies
View Related
Jul 7, 2014
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?
View 5 Replies
View Related
Feb 21, 2014
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
View 5 Replies
View Related
Apr 22, 2015
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
View 3 Replies
View Related
Dec 7, 2004
I need help with a calculation in my form. I have a form named families. IN this form I have 12 check box's, one for each month. I would like to set up another box which would take the average of the past 3 months and tell me what percentage of the time the box is checked. For example, since it is december, I would like a box named quarterly average to look at the past 3 months, obviously september, october and november, and tell me in percentages what the percentage is that the past 3 months check boxes have been checked. This is the basic code which I created for my unbounded box, but I want it to be dynamic, so that it recognizes what the month is today and tells me automatically what the percentage is.
Control sourceis set to =Abs(([Sep]+[Oct]+[Nov])/(3))
Thanks,
Tim
View 3 Replies
View Related
Mar 28, 2006
Hello, I’m new to this board and to Access as well. My Database has two tables. Once contains product data including quarterly volumes. The other contains quarterly pricing data. I’d like to query the pricing data so that it returns the proper price based on the quarter the volume ocurred in. This would then be used to calculate Revenue. How do I write a query to select the correct price based on the quarter value in another table?
Thanks in advance.
Table 1
Product_#_____Quarter______Volume
____A__________Q1FY05________1000__
____A__________Q2FY05________1327__
Table 2
Product_#_____Quarter________Price
____A__________Q1FY05__________7.34__
____A__________Q2FY05__________7.05__
Desired Result
Product#___Quarter___Volume___Price____Revenue
___A________Q1FY05_____1000_____7.34_____$7340.00
___A________Q2FY05_____1327_____7.05_____$9355.35
View 3 Replies
View Related
Mar 21, 2007
i am making a db for a gym. the members pay yearly and the fact that they have paid is shown by a tick box in the members table. i therefore want to reset these boxes to 'No' at the beginning of each year. anyone got any way that this could be done.
thanks
View 4 Replies
View Related
Mar 3, 2007
I have created a cross tab quarterly query to sjow the 4 quarters sales.
Problem is if there is no sales in one of the quaerters, the report based on that query gives an error as per attached photo and do not open.
any work around
View 1 Replies
View Related
Jun 2, 2014
I need to creat a field named Q.
Q is the quarterly growth from the YYZ field.
I have another field, Date, which is obviously the date of the record YYZ.
There is one record per date.
So, how do i compute
Q=Y(t+3)-Y(t)
?
I'm very lost doing this operation.
View 7 Replies
View Related
Mar 29, 2007
Here what I want to do. I want to use the autonumber facility which will restart by itself yearly in the following way.
1/2004
2/2004
3/2004
4/2004
.
.
1/2005
2/2005
3/2005
4/2005
Can you help me on this please?
View 6 Replies
View Related
Jan 29, 2008
I'm currently attempting to setup a small database to track a few things for the company I work for, namely attendance. Currently they use an excel spreadsheet that covers the current year. This worked great for them when they were still a small company, but with the number of employees shooting from 50'ish to a couple hundred the spreadsheet is just not a viable option any more.
What I need is suggestions on the best way(s) to go about implementing this.
First, I need to do a 1 year look back, on a montly level is fine. So from January 2008 back to January 2007 will work fine. What i have currently setup is a form with 12 of the basic Calender controls built into access 2003. Unfortunatley these wont quite work.
Basic layout of my database so far will be an employees info table, a lookup table for Attendance codes and their infraction values and an attendance (or I suppose more technically an absence) table. I would like to have a form that displays a calendar view for the last 12 months, that will allow me to view / update any absences for an employee. I would also like a way to insert company holidays / shutdowns in to the attendance table in a way that marks it on any employees calendar as a scheduled day off. I assumed i could do this with a simple "all" employee along with the dates and have the calendar search for entries with the Employee ID as well as the All flag and mark those on the calendar.
I think if i can just get the calendar setup covered I can figure out the rest of the data aspects, I'm just stuck on finding a decent way to implement the Calendar Aspect.
Thanks in advance!
View 14 Replies
View Related
Apr 4, 2007
Got a little system going whereby when a job is created it will display the date that the job is next due by.
I'm trying to work out how to create a qry that will display all due dates & not just the next one.
Here is how it should work.
If a job is created on the 1st of April 2007 & the type of job is MONTHLY then I want it to list the rest of the monthly orders that are planned for the year.
Here are the different PPM types that I'm using to calculate with
8784= Yearly
43848= 5 Yearly
2208= 3 Monthly
744= Monthly
168= Weekly
Has anyone done anything similar cause I can't see how it can be done?
This is what I'm using to create the next date due
NextDue: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported])
Here is my qry that the above code is in.
SELECT tblPPMType.PPMID, tblBuildings.PropertyName, tblPPMType.PPMType, tblTasks.DateReported, tblPPMType.PPMNextDue, DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported]) AS NextDue, tblContracts.ConDescription
FROM tblContracts INNER JOIN (tblBuildings INNER JOIN (tblPPMType INNER JOIN tblTasks ON tblPPMType.PPMType = tblTasks.JobDetails) ON tblBuildings.ProID = tblTasks.BuildingID) ON tblContracts.ContID = tblTasks.ContractNo
ORDER BY tblPPMType.PPMType;
This is the result.
DateReported---------PPMNextDue--------NextDue
14/03/2007 14:35:34----8784----14/03/2008 14:35:34
15/03/2007 15:08:00----8784----15/03/2008 15:08:00
15/03/2007 10:07:11----8784----15/03/2008 10:07:11
14/03/2007 14:52:56----2208----14/06/2007 14:52:56
15/03/2007 15:10:21----2208----15/06/2007 15:10:21
15/03/2007 10:19:35----43848----15/03/2012 10:19:35
14/03/2007 14:54:13----43848----14/03/2012 14:54:13
14/03/2007 14:55:15----4416----14/09/2007 14:55:15
15/03/2007 22:36:27----744----15/04/2007 22:36:27
14/03/2007 14:56:30----744----14/04/2007 14:56:30
15/03/2007 20:34:27----744----15/04/2007 20:34:27
18/03/2007 22:38:41----744----18/04/2007 22:38:41
15/03/2007 13:39:02----744----15/04/2007 13:39:02
15/03/2007 10:58:55----168----22/03/2007 10:58:55
14/03/2007 14:59:21----168----21/03/2007 14:59:21
Any suggestions will be welcomed
thanks :)
View 2 Replies
View Related
Aug 17, 2015
I made a report that show a list of data where there is a date. It sorts the report by year then by month.I collects the information about the date from a field in a table where the whole date is inserted (example28.12.2014. (date/month/year)).What I want is to make a combobox or a listbox where you would chose a year you want it to show you the report for and a button that would open the report.So to make it clear in a form you would have a combobox where years would be displayed (and i would like the combobox to somehow know what years are inserted in the table), you select the year and click on the button that would open the report for that year.
The report name is "rptClients". The table from where the data for report is taken is "tblCustomers" and the field where the date is in the table is called "DateOfUse".Also in the report the date is in a field called "DateOfUse" also.
View 1 Replies
View Related
Jul 30, 2015
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
View 1 Replies
View Related
Feb 24, 2014
I am trying to track a yearly training in Access 2010 but am not quite sure how to accomplish it. The training originally was just a one-time thing, due within two weeks of a new hire's start date so I currently have it displayed as three fields in my employee table (Due Date [Calculated], Completed [Date/Time], Paid [Yes/No]). Now, employees will be required to complete this training every year (beginning January 1st, not from employee's start date) and I don't know how to accomplish the tracking it.
I need to keep each year's completion date and whether or not the employee was paid. Currently, the "Paid" field is linked to a query that populates a list of employees that have not completed the training. If the "Paid" Yes/No box is unchecked, the employee's name will be listed in the query; once I check the Yes/No box then the employee's name is removed.
What would be the easiest way to track this training for each year? There are several other training that I am tracking but are one-time only events; these too are separate fields in my Employee table.
View 1 Replies
View Related
Jun 27, 2013
I have a members DB that apart from full details also show payments of yearly subscriptions. I am trying to print a report that shows a list of all the lapsed members. sounds like a simple simple report, BUT...I have a table that includes male and female members on one club number, idealy man and wife, but if for some reason the part company one may not pay the others subs those making the other a lapsed member. is there anyway i can print one part without the other?
E.g. this is haw it prints now
Male Name Paid Female Name Paid
J Smith No M Smith Yes
and this is what i am after
Male Name Paid Female Name Paid
M Smith Yes
View 1 Replies
View Related
Jan 20, 2005
Ok, folks. I'm confused.
I have a database I've created called 'EngLog'. This database is located on the server and everyone in my department accesses it to add their latest entries.
However, every now and then, for some unknown reason, a second database appears, titled 'EngLog1'. I don't know what creates this second database.
Any ideas?
This is not a FE,BE database, and also, i'm utilizing a .dll file called MouseHook.dll which I have previously heard about in this forum.
Thanks to all in advance. This problem poses a bit of a threat in that some folks here might accidentally be updating the wrong database.
View 1 Replies
View Related
May 16, 2005
Hello! I'm in the process of creating a report, but am running into a problem. I have a number of clients that include fields such as ID, last name, first name, address, etc. and then their account numbers. Each client has a number of different accounts. The problem is that each client's last name, etc is listed again for each different account they have, so the query results looks like this:
ID Last Name Account #
1 Smith 123-56
1 Smith 4458-66
2 Jones 523-63
2 Jones 4525
2 Jones 5236
3 Roberts 5225-56
4 Johnson 523-55
I want the names and client contact info to appear only once in the report and all of the account #s to show also. Any suggestions would be GREATLY appreciated!
View 1 Replies
View Related
Mar 13, 2006
My table doesn't show duplicate records but when I run a query off theat table every single record has a duplicate record. What did I do wrong or how can I fix this?
View 1 Replies
View Related
Oct 5, 2006
Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.
Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb
IDTrading Name Location NoLocation StreetLocation Suburb
41350Arabica Café Grey St
263Arabica Cafe` 1GREY STREET
41294Arashi Japanese Resturant41Courtenay PL
150Arashi Kushi Yaki Bar41COURTENARY PLACE
From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.
SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];
View 2 Replies
View Related
Jun 23, 2006
Hello,
I want to make a query that retrieves me all the duplicated records from a table. It´s possible?
Thankx
View 1 Replies
View Related
