Query To Show All Of Various Counts On One Page
Dec 14, 2012
I am in the process of converting a very large, multi-page Excel spreadsheet into a Access Database.
This database is going to be used to quickly look up and update members of our building's emergency response team, their location, training, etc.
In my main db list I have things like last name, first name, the building the work in, the floor they work on, and what training certs they currently posses.
One of the pages on the spreadsheet has a floor by floor count of personnel. Example - tower #1 - floor 1 = 2 people, Tower 1 - floor 2 = 6 people, Tower 3 - Floor 3 = 4 people.
In the database table, I have a column named "Building" and a column named "Floor". Is there a way to run a query that would show all of the various counts on one page. Meaning, if I have 10 floors, a 60 people, can I have access tell me what floors have what number of people on them, if so, how do I do it?
View Replies
ADVERTISEMENT
Jun 11, 2007
How come when I try to count 2 differ fields in a query they become equal numbers even though they shouldn't be? Can you count multiple fields in one query?
View 4 Replies
View Related
Oct 15, 2007
Alright, completly new user to access here and this forum has been a great resource for me the last 4 days thanks.
Heres my issue. Im trying to create a report based off of fields ( in progress, wins, and losses) I want to be able to add up each instance of a win loss or in progress sum them up totally and then find ratio's of each
I really have no experience using access or count functions...I know its like math, but the wording (typing the logic out) baffles me.
Such as wins over total or loss ratio losses over total. The problem is I dont know how to create a query, or do it in the report that will let me add them up carry them over to a new field and especially do calculations...
I've been playing with the expression creator* googling like crazy and checking out this forum My data looks like this
StatusID Status
1 Loss
1 Loss
1 Loss
2 In Progress
3 Win
3 Win
2 Win
and I would like it to look like this
Total Projects Win Ratio Loss Ratio In progress Ratio
(Sum) Wins Over Total Losses over Total (ditto)
Is there a way to do this in one query? Or create a few different ones, Or even do it straight in a report?
And what the heck does the formula look like?
Are there special Functions that do this or is like like Field A + Field B = Field C?
Thanks
View 1 Replies
View Related
Feb 11, 2008
This might be a bit difficult to explain, but i will give it my best shot. I need queries to give me the count in various fields, but with specific thresholds within the particular field, and with a criteria. So for example, I have an age field calculated. Now i created a new query to give me the "count" for how many people are between age 1-10, 11-20, 21-29, and so on. I did this by putting in coresponding criterias such as "between 1 and 10" in each field. However, because all thresholds (which are my fields) in that query are using the same calculated fields (age, from another query), only one criteria is actively working. Two or more fields using the same "age" field just returns whatever my first criteria is over and over. So if i make a field "1-10" with it's criteria, then the fields "11-20" with ITS corresponding criteria, both fields just end up giving me the count of the first criteria. Am i doing something wrong? Is there an easier way to do this? Also once i can get this working, there's another condition i need to implement, which is: count of "1-10" for "2006", all in that query. then another query for the count of the ages thresholds in 2007, and so on. Any help is greatly appreciated! Thanks.
View 7 Replies
View Related
Mar 14, 2008
Hi. I am trying to do a query on a table that has 3 columns
ID, Person1, Person2
I want to query the data to find out how many rows each person's name appears in either Person1 or Person2.
I don't need seperate counts for when each name appears in Person1 or Person2, just the total for each person.
Result example that I'm looking for:
If table contained:
1 Eddie Sam
2 Pete Max
3 Sam Pete
4 Dave Eddie
Query to look like:
PersonsName Total
Eddie 2
Sam 2
Pete 2
Max 1
Dave 1
Surely this is easy to do?
Anyone? Thanks. Martin
View 6 Replies
View Related
Feb 13, 2013
I have a roster of people where each person may work on a specific task during a given period (a month, or a quarter, etc.) and each person is assigned to a team.
Team Jones has 30 people on it. There are 5 tasks that a person on that team may or may not work on during Q1 of 2013. Here are those tasks:
1. Business Analysis
2. Project Planning
3. Testing
4. Test Planning and Preparation
5. Quality Inspection
Now, what I want to do is to find the unique instances for each task where 1 or all of the 30 people worked on that task for the quarter. To further illustrate, say for Business Analysis that Mary Smith did Business Analysis in January, February, and March. She performed that task 3 times (1x in each month) but I am only concerned about knowing that she did perform Business Analysis during the quarter (so I'd like to show a 1 instead of 3).
Ideally, if everyone on Team Jones did Business Analysis during the quarter at least one time, I am only concerned about the one instance where they did and the first occurrence of it, not the other occurrences. So if everyone did do Business Analysis for the quarter, my total count should not exceed the count of members on the team (in this case, the 30 people). So the distribution could look like this (I'm just making this up but this is what I'd like to see):
Task
Team
Jan
Feb
Mar
Total
Business Analysis
Jones
7
15
8
30
My fields in the table are:
1. Task
2. User
3. Date
4. Team (there are 5 different teams)
Is this doable in a query?
View 3 Replies
View Related
May 1, 2015
I have a form with records in chronological order, as this has hundreds of records I would like to open the form and go to the bottom of the form to display the most recent records by date in the window, I know I could invert the order of the records displayed but I rather have them sorted by chronological order 1->999.
View 4 Replies
View Related
Mar 4, 2015
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have attached a screenshot.
View 4 Replies
View Related
May 7, 2015
Is there away to have the report footer only to display on the first page?
View 4 Replies
View Related
Oct 18, 2013
Im working on an access 2000 application, So far I'm able to open internet explorer page and navigate to any URL and get the sorce of the page. But what I realy want to do is browsing to page inside the form, and I need to do it with access 2000.
View 1 Replies
View Related
Sep 4, 2013
I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.
Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.
I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"
Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;
I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...
View 5 Replies
View Related
Jul 3, 2015
I have a report which shows the results of two separate queries in a simple table view.
One set of data varies in size depending on the amount of records and the other is a fixed size and never changes.
I'm wanting to make my report always show the fixed size data in the bottom right corner of the page when its printed.
I've tried putting it in the footer section of the report but don't want it to affect the size of the details section and just show next to the other query results.
Is this possible and if so how would I achieve this?
View 6 Replies
View Related
Feb 11, 2015
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
View 8 Replies
View Related
Apr 21, 2014
I have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
View 2 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jul 5, 2005
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
View 10 Replies
View Related
Jun 11, 2006
I wondered if someone could help.
I have 3 columns of data within a table and from a select query am trying to seperate certain groups.
Table 1
yr period valuehome
2006 0 100
2006 0 10
2006 1 1000
2006 1 800
2006 2 60
2006 2 50
2006 3 40
2006 3 10
2006 3 5
2006 3 70
2006 4 50
2006 4 5
I wish to show both results for period 0 and an accumulative total for periods 1-4
so results would be
year period Total sum
2006 0 110
2006 1-4 2090
Can anyone help?
thanks
Paul
View 5 Replies
View Related
Nov 12, 2013
I have a report which includes several subreports. There will be times when a subreport prints partially on one page and completes on the next. I don't always want to break to a new page with this subreport. However, I would want to break to a new page if the subreport cannot fully print on the current page.
View 3 Replies
View Related
Jul 8, 2005
I’m starting to wonder how I ever made anything work, before I found this place.
I built 2 queries and put them in a query
They share a common field
The join type is all record from Query1 and Matching from Query2
Next I added a count field for each
Then I added a calculated field that subtracts the count from Query1 to Query2.
So far it all works
When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.
View 2 Replies
View Related
May 1, 2006
Hey, I dont know much about Access but i need a little help with my queries.
I need to make it so that if a specific tick box was ticked, a number would be counted. This will be the case with many many tick boxes. What wud I put as the criteria.
Exmaple:
I wanted it so that every time I clicked a specific tick box (made with a yes/no) the number 2 was added to every other tick box that was selected (but those tick boxes may be a different number). It would be counted all up to a particular number.
I hope you get what I mean. It's difficult to explain.
Hope you can help
Thanx
8am81
View 4 Replies
View Related
May 6, 2006
I've been trying to use a sum in a query to add up the currency of several records in a field from another table but as yet have had no success. The sum simply shows all the records but on there own and not in one total :confused:
I also need to use a count to total up the number of records in a table with a certain piece of data, in this case a 1, but again it does the same as the totals and simply shows each record on its own
If anyone knows what I am doing wrong I would greatly appreciate some help and advice
Thankyou
View 4 Replies
View Related
Dec 19, 2006
Hi All
I need to be able to do a count before and after the current date.
My table has fields named month and year and I need to find the count before and after the current month. Any help would be much appreciated.
ChrisD
View 2 Replies
View Related
May 21, 2007
Hi all
I have a problem trying to create a query to count 2 dates in one query. I have recorded dates for the return of forms from specific types of vehicles, the first date is the date the form is for, the second is the date I received it.
I have a query that returns the count of the forms received per vehicle type. I have tried adding a second field in this query to count the days where the date for is within "x" days of the date received. However when I try to run the query I get an error saying that the query has a field that is not part of an aggregate function.
Is it possible to create this as 1 query or do I need multiple queries to get a result.
Thanks
Craig
View 2 Replies
View Related
Oct 27, 2014
How could I set up a numbering system where you have a main number and then have a choice of a sub number? i.e.:
Main = 1
Sub = 1.01
But still be able to move on to 2. Would I need a routine to ask if the next number is a sub number?
View 8 Replies
View Related
Nov 22, 2004
I have a report that lists states and cities within the states. When a state name happens to be at the end of the page the individual cities appear on the next page with no State heading. I solved the second page problem by setting the "repeatSection = Yes" in the Section Header (though I haven't shown that in the example below).
But the previous page (which just shows the State Name and no cities looks dumb. Is there some sort of solution.??
(Actually I would also like any State that continues to a next page to not just have the state name but something like
" Colorado (Continued)" Is there anything I can do in VB to make a page break if the section is going to print but therer isn't enough room for one detail line?
This is what I currently see
Alaska
Ancorage
Prudo Bay
California
Whittier
Anaheim
Colorado
------------------------Page Break ---------------------------------
Denver
Pueblo
Colorado Springs
Deleware
Dover
-------------------------------------------------------------------------------------------
Thanks !
View 1 Replies
View Related
Jun 13, 2015
If I have a report and it has controls (labels representing column titles) in the page header. Now when I print the report - if it happens that the last page has no detail records - but there is text boxes and so forth in the report footer. Is there a way to not display the page header on the last page?
I have a report where the last page shows the page header - and the field/column labels on the page header - but for which there is no detail records left to display - on the last page. There is report footer information that should display. It just looks weird because the field/column labels show at the top of the page - but there is no data remaining to print under them on that last page.
View 1 Replies
View Related
