Hi All,
How can I count records with a by-statement? For example: I want to count all the citizens by city. All fields are part of the table and form.
I used dcount, but doesn't seem to work, or I am just to stupid :) .
Thanks in advance.
i have a crosstab query that contains employees and their sassocaited assignements he/she is working on. each assisgnment is associated with a start and end date. there are monthly columns that forecast until 2007.
i need to create a headcount report that provided a count "1" for each employee in a monthly column.
currently i there is a Vaule Field that Counts the number of Employee......yeilding the desired "1" BUT i want it to only populate a "1" if the Montly Column is Between the Start and End Date for the assocaited assignment.
is it possible to add a If Statement in the Employee Column to only Count if the Month from the column is between the assignments start and end?
if so, can you please provide?
gracias
I know how to use the count statement that counts vertically, how can I count horizontally (only if the value is greater than 0) ?
CustID 1st 2nd 3rd 4th 5th 6th 7th Count
2233 7 3 0 0 2 8 0 4
5730 3 0 0 0 1 0 6 3
7590 0 0 0 4 0 0 1 2
I need to capture the value in the 'Count' column (as shown above).
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a table tblBookings.
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
For every hotel.
That kind of thing.
If you need more information please shout.
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
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.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
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"));
Hi,
Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.
I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.
All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.
Can I do a count of the count or do I do some sort of sum of the count results?
I've tried everything I'm capable of as a newbie, and I'm not having any progress.
Any help appreciated.
Hi all,
Do someone have a way to do a count function in a Reports to let the report showing the details at the same time do the accumulating of the number??
I had try so many way but it not work~~!!! Pls i need someone help cause i stuck my report there without the accumulating of the number, Thanks.
Regard,
alex
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0
but if either one has a status greater than 0 then the text box would be green.
Thanks
Jon
I'm using this code to do some calculations on a form. This code works fine
as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then
Me.[GtoG] = [GtoGtotal]
Else
End If
If Me.[qual] < 4 And Me.[completed] > 61 Then
Me.[GtoG] = Me.[qual] + 61
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Else
End If
If Me.[qual] > 4 And Me.[completed] < 61 Then
Me.[GtoG] = Me.[completed] + 4
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If
If Me.[qual] > 4 And Me.[completed] > 61 Then
Me.[GtoG] = 65
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If
Is there a better way to do this as it's not giving me the right results - so maybe I should say a correct way to do this!
=IIf([PMtype]="3" Or "4" Or "5",2,1)
Thanks
DBL
I am trying to do something really simple, but my lack of experience in Access has bitten me.
I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then
total = Bill_Time * 1
Else
total = Bill_Time * 2
Any suggestions?
Hellow :(
Question.....
i have a report in access on which grades of student are shown, like the following example:
Lifescience 6
Brainstorming 7
learningdev. 9
communication 5
My goal is to, next to the grades, also to put the grades as words...
Lifescience 6 six
Brainstorming 7 seven
Learningve. 9 nine
Communication 5 vife
I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field.
(that is: i think i have to use the field where the grade comes from?)
Thanks for this, i think, relatively simple question........
Tj
Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.
If Me.cboFat = 1 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 2 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 3 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If Me.cboFat = 4 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If
If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _
Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!"
Cancel = True
End If
Hi,
I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:
IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)
It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?
Thanks
On the following code I want to attribute the value of the sum([price]) to an variable called txtFullPrice:
Is there anybody that could help me please.
Dim dbs As Database, rst As Recordset
Dim strcriteria As String
'return reference to current database
Set dbs = CurrentDb
'Define serach criteria
strcriteria = "SELECT SUM([Price]) FROM tblRBS WHERE [USERID]='" & FOSUsername & "'"
'Create a dynadet-type Recordset object based on tblUser table
Set rst = dbs.OpenRecordset(strcriteria)
rst.Movelast
*******
'close connection on tblUser table
rst.Close
Set dbs = Nothing
This Forum have been helping me to improve my skills a lot. Thanks to everyone.
Jony Bravo
I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.
I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.
Here is my IIF statement:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,1,IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.
This is an example of what I'm after:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
I hope I was clear in my explanations. Thanks in advance for your help.
What i am trying to do is:
IIf([Me].[Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")
have tried:
(IIf([Me].[Custom1Checkbox]=True,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))
and
(IIf([Me].[Custom1Checkbox]=-1,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))
but with no luck
what i have done to get over it for now is:
Link to post (http://www.access-programmers.co.uk/forums/showthread.php?t=92802)
Hi there
I am having problems with an IIF statement and I was wondering how I can get the query that I need to work.
I have the following columns
application_type: this has three values in it - Major, Minor and Other
numberofdaystodecide: this is the number of days taken to decide a file
what i want to do is an iif statement that works as follows
iif the application type = major and the numberofdaystodecide >92 then over 13 weeks
iif the application_type = major and the numberofdaystodecide <=92 then less than 13 weeks
iif the application_type = minor and the numberofdaystodecide >56 then over 8 weeks
iif the application_type = minor and the numberofdaystodecide <=56 then less than 8 weeks
iif the application_type = other and the numberofdaystodecide >56 then over 8 weeks
iif the application_type = other and the numberofdaystodecide <=56 then less than 8 weeks
Any ideas how I put this in to a query to get the information that I need?
Tried a couple of things to no avail:(
Thanks for any help :D
Jools
Hello Guys,
I have a question.
I have a query that looks like this:
Category Customer_ID
1 123444
1 233732
1 87667
1 3343
1 99876
2 123
2 775446
2 332334
2 22311
2 1121
Now, from Category 2 I want to see the top 3 and from Category 1 I want to see all.
I know I can't use Select TOP because it cuts random.
I just want to cut from Category 2.
Thank you in advance guys
I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?
View 6 Replies View RelatedI am trying to display Shift information in a query by using the following
IIf(Time([CALL IN]) Between (07:00) and (15:30),"1st Shift",IIF(Time([CALL IN]) Between (15:31) and (23:30) ,"2nd Shift",IIF(Time([CALL IN])Between (23:31) and (06:59) ,"3rd Shift"," ")))
and I am getting a message "The expression you entered has too many closing Parentheses"
Can anyone spot my error
Thanks
Should this statement work?
IIf([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2))
Some of the fields brought in from the CSV file have random "?" in front of some of the names, so I made this to remove the first character of the field if there was a "?" there. Maybe there is an easier way, but I would still like to understand why this doesnt, it just returns no data as if there are no fields matching, there are some with a "?" e.g.
?JoeBloggs
Hi,
I'm running a query and pulling in a status field, with the following selections:
25% to sell
50% to sell
Sold
I've added an IIF statement (see below) to change the sold to 100%, however, it will only return 100% and does not pull in any of the other data.
IIF([Salesboard4]![Status]="Closed",100%).
How do I change my statement to pull in all data and change the sold to 100% at the same time.
Any help will be much appreciated.
Thanks