I want to count the records with multi iif conditions. I am able to get one condition right but don't know how to put more than 1.
Eg: =Count(IIf([Step #]=1 And [Type of Work]=1,0))
But I am not able to get if I want step#1, step#2 and step#3 all together with type of work = 1
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies View RelatedHello. Can this be done?
I am creating a mini scheduling program, and I have one table that handles all requests (tblRequests), then a second table is the final schedule (tblFinalSchedule). On the table for the requests, I have a field called "locked" that is of type yes/no. The purpose of this field is for a meeting time that is of uptmost importance and that I don't want the program to manipulate it in any way.
Is there a way to program in VBA so that when a button on a separate form is clicked, those records that when the yes/no field is checked (set to true), are copied into the appropriate fields from tblRequests into tblFinalSchedule?
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code:
Private Sub Form_Current()
'Code 1 - Prevent edits if 21 day period has lapsed
If TestDate2(Me.txtStartedHidden) > 0 Then
MsgBox "Editing NOT allowed - 21 day time period has been reached"
Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached
Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days
Edits prevented if invoiced date reached & records past 21 days
Hi,
I wonder if someone could possibly help me.
I'm working on a database used to dispatch first aiders to events. The general structure is a form detailing the event with a subform (currently pulling info straight from a join table though I may change the data source to a query at a later date) containing details of attendees in list format.
I have a record in the events form that shows the number of first aiders attending which is currently updated manually. In the subform however, there is a tick box as to whether they attended as sometimes they enlist but have to cancel for whatever reason.
I wanted to implement something that will count the ticks and update the number attended field automatically.
I don't know alot about VB and have tried using the count expression function in the subform footer then setting the number attended field to equal the count field. The problem I find with this though is firstly there can sometime be a delay in updating this and secondly I need the number attended to appear in a report listing all the duties attended each month for expense claims.
I may be half way to hitting the mark with what I've tried but if anyone could suggest anything, I'd be very grateful.
Thanks in advance for the help
Ian
Quick overview. I have a site # and Subject #. The subject # is 7 digits and the first 4 are the site # (exp. Site # 1000, Subject # 1000001, 1000002, etc). At times the subjects switch sites but their subject # remains the same so Subject # 1000001 now resides at site # 2000.
How would I write a query like the following?
Count [tbl_Enrollment]![Subject #] WHERE [first 4 digits of the subject #] LIKE [tbl_Site_Dem]![Site #]
I have a student with an access table that has fields names week1, week2 etc. The data in these fields is either a '1' - meaning present or a '0' meaning absent. We want to be able to put a formula in a query that counts how many absences there have been (similar to a =countif formula in excel)
any ideas?
Hello everyone,
Got another problem that I'm sure you guys will solve with your eyes closed ;)
I've got a database with a field called courier_no. Imagine this set of data.
1234
1234
1266
1277
1277
1288
1299
I want to run a query to count the number of instances that each number appears. For example I would expect the results
1234 2
1266 1
1277 2
1288 1
1299 1
I then want to run a criteria on the count field (But I think I'm ok with that bit once I've solved the counting of the data.)
I've tried the count function in the query builder but it doesnt total the instances that each record appears.
Help please (and please keep it simple cos you know I struggle with all that VBA stuff :D)
Stu
hi all...
i'm sure there is a simple soultion to what i want to do but my brain isn't helping me at the moment...
i have a table called bookings with a column called nono which consists of dates with each date being a booking for a bed....
i want to be able to run a report for instance which tells me how many beds have been booked between two dates and year to date...
how do i go about doing this the most efficient way.... i can get a count for one particular date without a problem... and i know i could go and write out totals for each date in the range and then add them up...
however, is there a better more effieicient way of doing this???
cheers in advance.....
I have a table with two fields: Company, Product_Name
Currently the table is not normalized (which is what i am now trying to change). While the Product_Name are unique, the Company names repeat themselves (one company has several products).
I will like to find out which company has more than 10 products and see the names of these companies in a query.
Can I achieve this simply through SQL? If not, How do I accomplish this with VB?
As always, all assistance is highly appreciated. Thank you.
In my form (on which I have removed record selectors) I would like to display the number of total records. Is there some way of inserting this information in a text box or other control?
Thx
Hey all,
I am building a database to help my unit in Iraq. Here are the fields I have so far.
Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)
Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide
i'm trying to get a count of how many customers made billing requests during the month of november. the query is based on one table in the database. some customers made multiple requests, but i'm looking for the number of different customers who made requests, not the total number of requests made. the following sql stuff weeds out the duplicate customer numbers and lists each unique customer number.
SELECT DISTINCT TBLBillingRequest.MemberID
FROM TBLBillingRequest
WHERE (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
my problem occurs when i try to to incorporate the count function like so.
SELECT DISTINCT Count(TBLBillingRequest.MemberID) AS CountOfMemberID
FROM TBLBillingRequest
HAVING (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
it gives me a count, but the count includes duplicate customer numbers as well, not the total of unique customer numbers, which is what i want.
any suggestions/help would be greatly appreciated!
Hello All, I have what I hope to be a simple problem to fix.
I have 2 tables: tbl-Agents and tbl-Reviews
Every employee is an agent however; not every employee has a review for
month(x).
I am wanting a count of all reviews done for each employee even if they do not have a review done.
Joe Smith exist in tbl-Agents and also has 10 instances in tbl-Reviews for October. He will show up given the sql below.
Cathy Jones exist in tbl-Agents but does not have any reviews in tbl-Reviews for October. She does not show up given the sql below.
I need the output to look like
Joe Smith October 10
Cathy Jones October 0
SELECT [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth, Count(*) AS Expr1
FROM [tbl-Agents] LEFT JOIN [HISTORY-Q] ON [tbl-Agents].AgentID = [HISTORY-Q].[Emp Num]
GROUP BY [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth
HAVING ((([tbl-Agents].[Dept/Bucket])="Bank Card 61-90dpd") AND (([HISTORY-Q].ReviewMonth)="October"));
Please Help :confused:
i have a query which find all the records of stock that are similar. I want to count how many of these there are. How would i go about doing this?
I then want the user to type in the system they want, how many they want, and this will "remove" the system from the list, but not from stock i.e. makes it unavailable. The query is been created from the stock, and so everytime the query is run, it would overwrite the "unavailable stock". If any one could help, this would be great!
Emily
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 RelatedI have two tables, RMA Numbers and Serial Numbers. Each RMA number will have multiple serial numbers assosiated. I need to find a way to count how many serial numbers are assosiated with each RMA number.
I think the best way to do this is in a query using the dCount function some how.. but I can't figure out how to get it to count different serial numbers for the same RMA number.
I also considered writing my own function to do it.. but it got really messy and there must be an easier way to do it..!?
Hello everyone! I'm new to this forum and this forum has been a very useful resource on creating my first database. I know nothing about coding and this is my first time trying to create an Access database for work. I have been working on this database for more than 4 months and this forum really helps me from creating tables all the way up to forms. THANK YOU!!
Now, I'm struggling on creating reports that counts records from my tables. Here are two of my problems:
First:
I have a main table called "tbl_main" which records students' basic information, including their birthday in a column called [dob] in mm/dd/yyyy format. I need to create a query that counts how many students in age 11, 12, 13, 14, 15,16, and 17 separately so I can list them on a report.
Second:
I have a enrollment history table called "tbl_enrollment" which records dates that students had been in and out of the school. Since a student can re-enroll for more than once, there are more than one record for the same student. I have student ID [idnumber] and admitted date [admitted] columns in this table. I need to create a query that gives me a total number of enrollment, number of one-timer, and number of repeater, so I can list these numbers on a report.
For both reports above, user will input a timeframe to count particular number of records. Thanks to this forum, I already have a working form with start/end fields for user to input. What I'm struggling is creating queries to count records.
Thanks again for spending time reading my first post in this forum and I hope I can get helps from anyone of you. Since I'm not a programer, you may need to step-by-step walk me through the whole process. Sorry!!
I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.
SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;
qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.
(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)
Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.
Any help is greatly appreciated. Thanks.
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
Hello there,
I have tables like so (simplified):
-=Holiday=-
HolidayID
Name
Date
-=Booking=-
BookingID
HolidayID
ClientID
What I would like to do is create a query which returns a list of all the holidays with a field showing how many bookings have been created for each holiday. This almost works:
SELECT Count(1) AS CountOfBookingID, Bookings.HolidayID
FROM Bookings
GROUP BY Bookings.HolidayID;
...but it does not display holidays where there are no bookings.
Is it possible to create one which will show all holidays even if there are no bookings?
Thanks!:cool:
How would i count the total number of records on a form
View 5 Replies View RelatedHello & thank you in advance.
I am fairly new to using Access and would appreciate any help.
I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars
The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3
Is that something that I can do via Acces?
Thank you
i have a query that returns anywhere between 0-20ish records, and i need an efficient way to count how many records are returned. any ideas?
i was also wondering if there is a more efficient way to do this:
Code: [Text127] = DLookup("[Comments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text175] = DLookup("[Performed]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text177] = DLookup("[Model]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text179] = DLookup("[Serial]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text181] = DLookup("[System]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text183] = DLookup("[HComments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text201] = DLookup("[Labor]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)
mabey i could condense them into one Dlookup somehow? any ideas?
thnx for your help
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 |