Search results

Try this:- SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [sumofJettyNumber]/[countofunit] AS Result, [result]=CInt([result]) AS Expr1 FROM Qry_All_Ops GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit...

In the SQL statement of the crosstab query, you can list the column headings with the IN operator after the PIVOT clause e.g. TRANSFORM ............ SELECT ............ FROM ............ GROUP BY ............ PIVOT [District] IN ("Eastern Market","North Point","Central","Southern End"); .

In queries sometimes we need to explicitly declare the data types of parameters. http://www.access-programmers.co.uk/forums/showthread.php?t=87102 http://www.access-programmers.co.uk/forums/showthread.php?t=103467 http://www.access-programmers.co.uk/forums/showthread.php?t=126516 .

Try this Where Clause: WHERE IIf(IsNull(Forms!FrmReports!LoanOfficer), True, LoanOfficers.LoanOfficerID = Forms!FrmReports!LoanOfficer); The word True in the expression will return every record when LoanOfficer on the form is null. .

To list the counties, you can use the IN operator in the expression:- ------------------------ Field: IIf([Forms]![Main Menu]![cboCountySelecter]="All Wales", [yourFieldName] IN ("Anglesey","Blaenau Gwent","Cardiff","....","...."), [yourFieldName] = [Forms]![Main Menu]![cboCountySelecter])...

In the query, you can format the date field into "quarters" to match q1, q2 etc in the combobox on the form. Format([yourDateField], "\qq") Or to match 2007 q1, 2007 q2 etc with Format([yourDateField], "yyyy \qq") See the form and queries in the attached database. .

Try this Totals Query, replacing with the correct table name:- SELECT MyDate, FirstName, LastName, SSN, Sum(InAmount) AS SumOfInAmount, Sum(OutAmount) AS SumOfOutAmount, SumOfInAmount+SumOfOutAmount AS Total FROM [TableName] GROUP BY MyDate, FirstName, LastName, SSN HAVING...

You can put the criteria in a new column in the query grid like the following (replacing with the correct county field name):- --------------------------------- Field: IIf([Forms]![Main Menu]![cboCountySelecter]="All Wales", True, [CountyFieldName] = [Forms]![Main Menu]![cboCountySelecter])...

See the Conc() function and the query in the attached database. ADO code is used in the function. Since you haven't specified whether the ID field is numeric or text, extra code has been used in the function to test for its data type. Removing the test can make the query run faster. Note...

In the query, put >0 in the Criteria for TestField_Result. .

If the relationship between the two tables is one-to-many, try this SQL:- SELECT tblProjects.ProjectID, tblProjects.ProjectName, First(tblProjects.ProjectValue) AS ProjectValue, Sum(tblCosts.CostValue) AS CostValue, [ProjectValue] - [CostValue] AS Benefit FROM tblProjects INNER JOIN tblCosts...

Assuming you want to delete the records from table tblRosterwoNHires where the Reps exist in table tblNewHRoster, try this query:- DELETE * FROM tblRosterwoNHires WHERE Rep_Name IN (SELECT Rep_Name FROM tblNewHRoster); Note: When running a new delete query for the first time, it is advisable...

A sample database http://www.access-programmers.co.uk/forums/showthread.php?t=103312 .

Oh, I overlooked the sample data! Index the other fields and try splitting the SQL statement into two queries and combining them in a third one. Run the third query. Sometimes splitting a query into smaller ones helps. .

I can see only two combo boxes in the SQL statement. Try indexing the fields [Voting History Combined] and [Precinct Combined] in table Sosx, the field [OFFICE] in table grpoffic, and all the fields used in the JOIN and changing the Like operator to = as Like cannot take advantage of the...

Assuming the two tables are named tblDescription and tblCategory, try this non-equi join query. SELECT [Description], [Term], [Category] FROM [tblDescription] INNER JOIN [tblCategory] ON InStr([tblDescription].[Description], [tblCategory].[Term]); To see if there are also descriptions that...

You can put the criteria for each field in a new column in the query grid like this:- -------------------------------- Field: [FirstName] Like "*" & [forms]![Search]![txt_FirstName] & "*" Or [forms]![Search]![txt_FirstName] Is Null Show: uncheck Criteria: True --------------------------------...

Remove the embedded spaces surrounding [Project Code]:- DSum("[Amount Aus]","Costs","[Project Code]='" & [Project Code] & "'") .

Are they text fields? Date/time fields in Access do not accept decimal points in seconds. If they are text fields, you can change them into date/time fields in table design. For text fields, you have to use functions to get rid of the decimal points and then use the CDate() function to...

You can use the CStr() function. SELECT tblExtensions.Extension AS PIN, CStr(tblExtensions.Budget) AS LIMIT .