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 RepliesI need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
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
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
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?
I'm running into an issue where I'm trying to tie several queries together into a list one running total. I have six queries that pull data from the same table, but that meet specific criteria. What I was trying to accomplish was to have a 7th query count the records in each of the six queries, and return the results as a different value for each. Here's an example:
Queries:
qry_1A
qry_1B
qry_2A
qry_2B
qry_3A
qry_3B
Final product:
qry_totals:
1A | 1B | 2A | 2B | 3A | 3B |
20 | 15 | 33 | 19 | 12 | 6 |
What I tried:
Field: 1ATotal: Count([qry_1A].[valueName])
Total: Expression
And I did this for each field that I wanted the query to return, so:
Field: 1BTotal: Count([qry_1B].[valueName])
Field: 2ATotal: Count([qry_2A].[valueName])
Field: 1BTotal: Count([qry_2B].[valueName])
Field: 3ATotal: Count([qry_3A].[valueName])
Field: 3BTotal: Count([qry_3B].[valueName])
The problem is that I don't get what I expected - the query appears to be totaling all the records counted and applying that value to all the fields, so I get this:
1A | 1B | 2A | 2B | 3A | 3B |
105|105 |105 |105 |105 |105 |
I have constructed a neat database for randomly quizzing myself on French translations. However I need a simple way of counting the records in an underlying query "vocabularyQ" inside a sub routine. I have tried all sorts of statements the most recent being
SELECT Count(VocabularyQ.ID) AS CountOfID FROM VocabularyQ
I have attached part of my database.
I need to show the complete rows for all the status in each year with or without any record/data.
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code:
TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
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
How would i count the total number of records on a form
View 5 Replies View RelatedI'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 RelatedI'm creating a report for an imaginary "medical clinic's database", the intended function of which is described as follows: "Create a statistic that shows the total number of distinct drugs prescribed to a patient."
Where I'm at:
I've created a query called UniqueDrugs containing drug and patient info. The SQL is:
Quote:
SELECT DISTINCT Drugs.Drug_Name, Patients.[Patient _ID]
FROM Patients INNER JOIN (Drugs INNER JOIN Prescriptions ON Drugs.[Drug_ID] = Prescriptions.[Drug_ID]) ON Patients.[Patient _ID] = Prescriptions.[Patient_ID]
GROUP BY Drugs.Drug_Name, Patients.[Patient _ID]
ORDER BY Patients.[Patient _ID];
The results of this query seem to be what I need. All I need is for the count formula that I use to return the number of distinct drug names there are that are related to a patient's ID.
I've created a report and I've put the following formula into a text box:
=DCount("[Drug_Name]","[UniqueDrugs]","[Patient_ID]=[PID]")
Where PID is the name of another text box in the report that contains the Patient ID that I want to compare to the drug names returned by the query.
However, this *always* results in #Error, no matter how I change the formula.I have been led to believe that syntax is not the issue, as the following formula worked as intended for a different task:
=DCount("[Prescription_Date]","[UniqueYearQuery]","[Patient_ID]=[PID]")
How do I use a DCount to return the number of different drugs that have been prescribed to a patient?
If it did not require the number of different drugs, I would simply use the following formula, which works just fine:
=Count(Drug_Name)
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;
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.
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
If I have a table called "Calls" with an autonumber and another field with names for stores like this:
1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store
How can I build a query to count the number of different store names?
For example, in this case I need the query to return the number 3.
I have a date field in a table. I wish to display in a query:Column A representing the month. Column B representing the number of records in the table corresponding to that month.
View 5 Replies View RelatedI build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.
I create a table named EVENTS.
Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date
Then I create a query which returns the number of days the event will take place on.
EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?
I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)
I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)
I've managed to come up with the following which provides the list of ID's :
Quote:
SELECT tblA.CommentId
FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id
WHERE tblB.Id Is Null;
Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?
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]
I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.
For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.
There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position.
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?
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
I am writing a query to determine employee's commissions. The commission rate changes for all order over the first 15 per employee per week. What would be the best way to write the formula?
View 12 Replies View Related