Queries :: Counting Number Of Machines In Department And Total Migrated

Nov 7, 2013

I have an access dbase with a single table

I am interested in reporting so need totals, I can write a query that will count the number of machines in a dept like so

SELECT [Computer Inventory].Department, Count([Computer Inventory].ID) AS CountOfID
FROM [Computer Inventory]
GROUP BY [Computer Inventory].Department;

This works fine, however I now want to add more so are two fields

To_be_migrated
Is_migrated

These are just checkboxes so I assume criteria is true or false

I need a query which will give me

The total number of machines by dept
The total marked for migration
The total migrated

View Replies


ADVERTISEMENT

Queries :: Counting Total Number Of Returned Results From Query?

Aug 11, 2015

I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.

The process goes: Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]

It then gives me the requested information.I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.

Can I simply add an additional line to the Macro?

View 7 Replies View Related

Calculate Total Hours (for All Employees) On Each Department?

Sep 28, 2005

Hello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"

And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.

eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department

would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?

View 3 Replies View Related

Queries :: Counting Number Of IDs For Each Week - GroupBy

Oct 22, 2013

I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.

Code:
SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;

View 2 Replies View Related

Queries :: Crosstab Query - Counting Number Of Records For Each FY

Sep 19, 2013

I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.

View 7 Replies View Related

Queries :: Counting Number Of Days When Item Used For Last Week

May 27, 2014

I need to count the days that an item of equipment is used for the last week?

The problem comes if an item is deployed within the week i.e not used for the full week?

I have two fields - MobDate and DeMobDate

I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.

View 14 Replies View Related

Queries :: Count Total Number Of Records

Dec 18, 2013

I am wanting to display in a text box or on my report the total number or records in my database. Also I have some buttons that filter the results a little, I'm wanting to display the number after I hit the button(s). Would I just add the query to the end of each button I have?

View 6 Replies View Related

Queries :: Calculate Total Number Of Days Between Two Dates In A Query

Feb 18, 2015

Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.

I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.

The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.

This is a formula I tried to piece together but obviously not correct as it's not returning anything;

IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],
Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))

View 2 Replies View Related

Queries :: Calculating Total Number Of Records From Union Query

Jun 16, 2014

I am using the following UNION QUERY to total up equipment tested for a report.

SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]

[code]...

I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?

View 2 Replies View Related

Queries :: DISTINCT COUNT Of Name If Total Number Of Items Greater Than 0

Jan 29, 2014

I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....

TBL-STORE

NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere

TBL-PURCHASES

NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14

I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.

This is one way I've tried to write the SQL, but I keep getting an error...

SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");

I also tried an Count(IiF( and can't get that to work either..

View 3 Replies View Related

Queries :: List Box Data Based On Logged In Users Department

Jan 4, 2014

I have a list box with a row source query as follows...

SELECT qryPendingCompletions2.ID, qryPendingCompletions2.Department, qryPendingCompletions2.[Employee Name], qryPendingCompletions2.CompletionDate AS [Completion Date], qryPendingCompletions2.[Entered By], qryPendingCompletions2.Goal, qryPendingCompletions2.Comments, qryPendingCompletions2.[Verified By]
FROM qryPendingCompletions2
WHERE (((qryPendingCompletions2.Department)=[cbocurrentemployee].[column](3)) AND ((qryPendingCompletions2.[Verified By]) Is Null))
ORDER BY qryPendingCompletions2.Department;

I also have a combo box at the top of the form showing what user is logged in where the row source query is the following...

SELECT Employees.UserID, [First Name] & " " & [Last Name] AS EFullname, Employees.AccessLevelID, Employees.Department
FROM Employees
WHERE (((Employees.AccessLevelID)=1 Or (Employees.AccessLevelID)=2 Or (Employees.AccessLevelID)=3 Or (Employees.AccessLevelID)=4));

How to only list the data in the list box when the department is the same as the logged in user.

So I'd like to add the criteria to the first query something like criteria = [cbocurrentemployee].[column](3) but this doesn't seem to work as criteria in queries.

View 3 Replies View Related

Queries :: Count Same And Differences / Calculate Percentage Of Number Of Same Over Total Amount

Jul 9, 2013

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.

View 14 Replies View Related

Forms :: Counting Multiple Checkboxes And Calculate Total Of Whole Group Of Users

Aug 27, 2014

Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....

View 1 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

Jul 23, 2015

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..

View 9 Replies View Related

Total Daily Sales Queries By Model/Total

Mar 8, 2008

Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance :o:(

Thanks (In advance) & God Bless.

View 2 Replies View Related

Counting The Number Of Entries

Aug 13, 2004

Whats up all U brilliant people,

I am baffled again. I need to print a report that can count the number of entries in a database or query. Basically I am looking for the equivalent of the countA function in Excel. All advice is welcome.

Eternally Grateful,
Khepra

View 5 Replies View Related

Counting Number Of Days That Contain Records

Feb 11, 2007

I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.

Thanks ... Erik

View 5 Replies View Related

Counting The Number Of Records On A Form

Dec 7, 2006

How would i count the total number of records on a form

View 5 Replies View Related

Counting Number Of Values For A Particular Field

Nov 5, 2012

I need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.

Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc)
......1 ................1
......2 ..........................1
......3 .....................................1
......4 ..........................1

Total ..................1 ......2 .........1

View 14 Replies View Related

Counting Number Of Occurrences By Date?

Sep 20, 2013

I have a table that tracks different events and the actual date they occurred. I am trying to write a query that counts the number of each event that occured by month. One issue I am having is if an event had no occurrence during a month I want to return 0 raher than no data for that event.

View 2 Replies View Related

Installing Db's On Machines Without Access

Mar 21, 2005

Hi. I've written a database which I may need to install on a lot of machines which don't have MS Access. Does anyone know of any software I can buy that creates the set up.exe files that allow people to just install it regardless of whether they have MS Access?
Many Thanks

View 2 Replies View Related

Counting Number Of Records In Relation To A Query

Aug 20, 2004

Hi all,

Is there any option to count the number or records relating to
a specific field. Here is the details..The db is for a school and there is a tables for
class and students. I am using a form to enter students details. I had created a
relationship with these two tables. i am using a query to get the details regarding a specific
class like classname, class teacher like that.. Is there any way to calculate the number of students in that specific class.

now i am using the query as follows

SELECT Class.*
FROM Class
WHERE (((Class.ClassID)=[Forms]![Class]![ClassID]));

I had created the relationship with the above two tables with classid as the primary key


thanks in advance

Thanks

View 1 Replies View Related

Counting The Number Of Records Returned In A Query

Aug 24, 2004

Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.

I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.

What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )

thanks for your help!

tony-t

View 1 Replies View Related

Forms :: Counting Number Of Records Within A Table

May 24, 2013

I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.

View 1 Replies View Related

Parsing Through A String And Counting Number Of Occurrence

Feb 12, 2013

I have about 150k rows that look like this

SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on

Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:

Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1

Is this possible?

View 6 Replies View Related

Counting Number Of Used Months In Each Year Within A Query

May 11, 2012

I have a query to calculate the monthly depreciation for a machine for specific period of time.

My problem is how to get the number of months for each period assuming i want to start from month September 2009 till may 2012.

Attached the data extracted by the query.

I want to calculate the depreciation to be grouped year by year in report.

[TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]

View 3 Replies View Related







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