Search results

Try this query. SELECT [ProjectID], Sum([InvoiceAmount]) AS TotalInvoiceAmount, Sum(IIf([Status]="Paid",[InvoiceAmount],0)) AS Paid FROM [Invoice] GROUP BY [ProjectID] .

Try this Totals Query. SELECT [ID #], [Name], Max([Date]) AS MaxDate, Min([Date]) AS MinDate, MaxDate-MinDate AS Diff FROM [Test Table] GROUP BY [ID #], Name; Note: Date is a function name and # is a special character. It's better not to use them in field names. .

I think your Time_In field contains only the time value while your Time_Out field contains the date 27/12/2007 as well as the time. That is why your expression returns the two large numbers. You can use the TimeValue() function to extract the time value from the Time_Out field for calculation...

Try this. DELETE DISTINCTROW Table1.* FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name; .

It's a select query. You can switch the query to SQL view and type the SQL statement there, using the correct table name and field name. SELECT * FROM [TableName] WHERE [AutoNumberFieldName] Mod 4 =0; .

Since date/time fields are internally numbers, you can calculate the elapsed time like this: (fldenddate+fldendtime) - (fldstartdate+fldstarttime) The integer of the above expression is the number of days and you can use the Format() function to extract the hours, minutes and seconds from the...

SampleID2: IIf(Left([SampleID],1) In (1,2,4),"RED","BLUE") .

In the SQL statement of the crosstab query, you can put an Nz(...)+0 in the Transform clause and an IN list of column heads after the Pivot clause like this:- TRANSFORM Nz(Count(AggType))+0 AS CountOfAggType SELECT IncType FROM [TableName] GROUP BY IncType PIVOT AggType IN ("Verbal"...

You can first build a query to get the Max Age of each GroupID. Then link the query back to the table in a second query. qryOne:- SELECT Members.GroupID, Max(Members.Age) AS MaxOfAge FROM Members GROUP BY Members.GroupID; qryTwo:- SELECT Members.MemberID, Members.MemberName, Members.GroupID...

You can use the Val() function instead of Int(). .

I have a sample database, which includes the calculation of Mode. You can open the form, enter a start date and an end date, and click on the Mode command button to run the query Mode3. The Mode is calculated using a series of three queries: Mode1, 2 and 3. Hope the example helps. .

See the attached database. It assumes the other two Yes/No fields are called ABCproc and XYZproc and the combo box contains the three options SAL, ABC and XYZ for user-selection. Hope it's what you are after. .

Assuming your table "tblEventMarks" looks like this EntryNo EvenNo Name EventMark 1 1 John Doe 55 2 2 John Doe 66 3 3 John Doe 77 4 4 John Doe 88 5 5 John Doe 99 6 6 John Doe 76 7 2 Jane Doe 82 8 2 John...

Yes, Between Date()-Weekday(Date(),2)+1 And Date()-Weekday(Date(),2)+14 when put in the criteria for a date field should return records between 12-25 Nov if the query is run this week. .

This will return records from Monday to Sunday of the week. Between Date()-Weekday(Date(),2)+1 And Date()-Weekday(Date(),2)+7 .

Try this in the Criteria for the date field: Between Date() And Date()-Weekday(Date(),2)+7 .

It is easier to demonstrate with an example. See the two queries in the database for the 'count of events entered' and 'the best three marks' of each individual. Note: When there is a tie in the marks of an individual, more than three records will be returned for that individual. .

Try this query. SELECT Quotes.* FROM Quotes WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker order by qDate Desc) Note: Running a subquery in a correlated way is inefficient and will take time if the table is large. .

I have attached a sample database. You can open the form frmSearch, enter some criteria and click on the command button to open another form frmSearchResults to view/edit the search results. frmSearchResults is a bound form with the query qrySearch as its record source. The list box on...

What's the data type of the CaseID field in your table? Text or Number? As explained in my post, the SQL in Query1 assumes that CaseID is a text field. If CaseID is actually a numeric field, you need to delete the two single-quotes surrounding [CaseID]. .