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

Quarterly, Monthly, Annual Advice

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

Reports :: Making Report Monthly / Yearly And Weekly

Mar 10, 2015

how to create the report monthly,year and weekly using access 2007?

View 3 Replies View Related

How To Count Clients For A Certain Date??

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

Monthly Table With Several Dates Count

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

Change Count From Monthly To Date Range

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

Reports :: Count Number Of Monthly Calls - Average Function In Footer

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

Queries :: Making Bird Count Database - Data Recorded On Monthly Basis Week Wise

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

Modules & VBA :: Calculation - Subtract Monthly Installment From Loan Amount On Monthly Basis

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

Quarterly Average

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

Query Help On Quarterly Data

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

Yearly Payments

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

Crosstab Quarterly Sales Query

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

Create Field With Quarterly Growth?

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

Autonumber Restart Yearly

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

Yearly Attendance Tracker/Calendar

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

Yearly Planned Maintenance Report

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

Reports :: Filter Report Yearly

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

Modify Quarterly Sales Report In North-wind Database?

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

General :: Tracking Recurring (Yearly) Training - Table?

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

Reports :: Members Database - Show Payments Of Yearly Subscriptions

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

Duplicated Database??

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

Duplicated Values Problem

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

Duplicated Records In Query

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

How To Find Duplicated Records?

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

Query To Select The Duplicated Records.

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







Copyrights 2005-15 www.BigResource.com, All rights reserved