I working on simple MS Access program which have 2 tables. As you can see in snap shot. What I am trying to achieve is. I would like to run a query which shows data between certain dates(It is done) and also to show me which Technician has done how many jobs(i have 5 technicians) and how much money a technician received by a payment method like Technician ID 1 did Cash Transactions 4 worth $300 similarly Credit Card transactions 2 worth 120$ and so on for other technicians.
The main theme is to get weekly summary report of technicians how many jobs they done and how many transactions each technician made by cash, by credit card along with their amount.
PostCode District District Name Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))
Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
This is kind of a weird problem, and everything I've learned about access has been on my own through empirical experimentation so forgive my if my description doesn't make sense, or if I overlooked something really simple. (Apologies for that, I've looked over all the guides on queries and record counting I could find and got nothing on this.
I can't figure out a better way of explaining my situation so I'll do the best I can.
I need to make a query that shows the number of people that have registered for a class. In one table I have the primary key Schedule ID which is the unique class, which is linked in one-one relationship with another field of the same name. In the second table with that field I have the field schedule ID which shows up multiple times, for each student enrolled in the course, second is the field Participant. There is the following data: Schedule ID...Participant 1 ................ <name1> 1 ................ <name2> 1 ................ <name3> 4 ................ <name4> 4 ................ <name5>
What the result of the query should look like is this:
Schedule ID...Number of participants 1 ................ 3 4 ................ 2
But instead I get Schedule ID...Number of participants 1 ................ 5 1 ................ 5 1 ................ 5 4 ................ 5 4 ................ 5
I have tried various variations of the Count and Dcount functions but I always wind up with that result (if it works at all)
This is the expression I am using:
Expr1: DCount("[Schedule ID]","Class Roster")
where "Class Roster" is the name of the Table that Schedule ID is located. I have tried changing it out with the name of the first table, and I have tried using participant instead of Schedule ID, but neither works. So far I've been able to use a crosstab query to get something roughly what I want but I think the assignment calls for a regular query.
Again, sorry if this doesn't make any sense, I did my best to explain it, and I couldn't find an answer anywhere. Thanks in advance for any help!
Oh, by the way, it's access '03 that I am using.
UPDATE: I have a second problem I'm trying to solve, and this one seems more confusing than the first to me. I have a table with the following fields:
Project Name, Task name, employee ID, Production Week, and Hours Worked. The first two being text and the last 3 being numerical.
I need to create a query that totals up the hours worked for each employee (one employee ID can show up under multiple projects/tasks) and returns the total hours in a particular week (week 20) of each employee that has worked over 40 hours, and only the ones that have worked over 40 hours. I can not figure out how to create a field that will sum up the hours worked for a given employee, nor can I figure out how to show only rows where the total hours record exceeds a certain amount. It seems like I would want a conditional like an IIF statement but those seem to be only for returning values, not showing/hiding.
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example: For today, we would use 4085-0001, 4085-0002, 4085-0003, etc. For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
I am working with 4 tables and I am trying to get a count of the email address field in each table grouped by Email Group Name. I tried the dcount function but returned an error because some of the email addresses have an apostrophe in the field. Is there a way to do this in 1 query? This is an example of the result I would like:
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings FROM tblBookings GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0 The Hilton. Bookings 3 The Carlton. Bookings 0 The Lowry. Bookings 2
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A FROM [One Year Data Lines] GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
- pkID (primary auto), - clientName (text), - q1 (text) - can only be "ABC" or "DEF" (from form's radio button group), - q2 (text) - can only be "ABC" or "DEF" (from form's radio button group), - q3 (text) - can only be "ABC" or "DEF" (from form's radio button group)
I'd really appreciate some help for what I fear is actually a very amature question...any assitance would be greatly appreciated!
I have the following table:
SubCount---------HomeDetailsID------FoodDetailsIDFK--PrimaryName Ruteete--------------199------------------54-----------Amatehe Ruteete--------------218------------------54-----------Amatehe Ruteete--------------199------------------54-----------Amatehe Ruteete--------------198------------------90------------Amoozi Ruteete--------------204------------------90-------------Amoozi Ruteete--------------192------------------20----------Banana Juice
By Subcounty, it shows what homes in the subcounty are consuming a particular food item. What I would like to do is count the number of times that food item is being consumed in each subcounty and generate a new table to compile this data.
And I am trying to use the count function to return the frequency of each Food being consumed. However, the count function counts all the records and returns a value of 6. What I would like is it to count the frequency of each food and ultimately build the following table:
I was thinking that this may involve using a loop to get a count on each specific food but the logistics of doing this are beyond me at the moment. Does anyone have any suggestions?
I have one table with one column that I am trying to extract data from.What I would like to do with the Column is split it into a few counts. The Column has numbers in it with KB usage. What i'm trying to do is count the amount of users that use say 0. Then have a column next to it that count say 500 >= 2000 and so on. This is completely baffling me as to why I can't seem to do this. I've even redesigned the database to see if I could fix this problem, but no luck I can build a query for this to be done sepearetly, but can't seem to find a way to put it into one query.
SELECT Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID, [test Usage].[Nov-14], [test Usage].[Nov-14], Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID1 FROM [test Usage] GROUP BY [test Usage].[Nov-14], [test Usage].[Nov-14] HAVING ((([test Usage].[Nov-14])=0) AND (([test Usage].[Nov-14])>=500));
I have recently been working on a database that is meant to be quite user friendly. It contains two tables, one that lists 'trainees' and another that lists 'job applications' in a 'one to many' type relationship. It is designed to monitor Job applications being made by people doing courses here. So far, I have created a very practical form for data entry. I've created a QBF to allow the user to filter the Job Applications quite substantially. I'm trying to build a form that allows the same criteria to be entered, but that counts the number of Job Applications, and then creates a pie chart that subdivides the applications by 'source of application' (i.e. Indeed.com, Family, Newspaper'), or Industry of the potential employer.
For example, I would like to be able to select a programme or course that someone is on, their starting date, a date range for the application and some other details- i.e. Select an Industry then view a pie charts for the source of those applications and vice versa.
I've created a form for this, with a button for 'Source' and one for 'Industry' the two different pie chart types I would like to be able to select. I have created two separate query by forms for each, with the buttons linking to the respective Macro. The problem I'm having is that the query seems to ignore the WHEN criteria completely.
SELECT COUNT([Job Applications].[Application ID]), [Job Applications].source FROM [Job Applications] INNER JOIN Trainees ON [Job Applications].[Trainee ID] = Trainees.[Trainee ID] WHERE ( [Job Applications].[Application Date] > forms!CustomChart!DateAfter OR forms!CustomChart!DateAfter IS NULL) AND
I know I can use concatenation and a date function for the first and third fields, so my main question is the proper syntax in order to get this layout.
How can I add a field that just populates "1" for each record so I can do a count. I know I can count based on a specific field but my boss wants to see a field that just shows "1" so I can SUM it up in another query.
I have a dataset with "Date", "Product ID", and "Revenue" fields. I'd like to create a query that rolls the data up at the daily level and reports "Count of Product ID" (unique product IDs) and "Sum of Revenue". I'm having trouble figuring out the logic.
