Search results

When used alone, both syntaxes will convert a null value to zero. But the data types returned are different. The first syntax returns a numeric 0 (you can see that the whole column is right-aligned.) The second syntax returns text (the whole column is left-aligned.) When used on two fields...

Why didn't I think of Left Joins! I tried Inner Joins, failed and changed to subqueries. To get rid of zero balances: SELECT TblCustInfo.CID, TblCustInfo.Name, nz([SumOfChargeamt],0) AS Charges, nz([SumOfCreditamt],0) AS Payments, nz([SumOfChargeamt],0)-nz([SumOfCreditamt],0) AS RunBalance...

I thought you had learned the multiplying effect when you put them in your original one query. .

You can use the Val() function to strip the leading zeros from the numbers for comparison. But then you can't use the keyword DistinctRow with Val(). You have to use a subquery with IN, but a subquery can return only one field. DELETE * FROM Table1 WHERE Val(column1) IN (SELECT...

Maybe this can speed up processing a little:- Change the two totals queries into make-table queries to create two temporary tables. In the 3rd query, reference the temporary tables instead of the two totals queries. Run the first two queries before running the 3rd query. The one-query...

You can specify the joining condition in the On Clause. DELETE DistinctRow Table1.* FROM Table1 INNER JOIN Table2 ON table1.column1 = table2.column1 and table1.column2 = table2.column2 .

Try these three queries. qryTotalCharges:- SELECT TblCharges.CID, Sum(TblCharges.Chargeamt) AS SumOfChargeamt FROM TblCharges GROUP BY TblCharges.CID; qryTotalPayments:- SELECT TblPayments.CID, Sum(TblPayments.Creditamt) AS SumOfCreditamt FROM TblPayments GROUP BY TblPayments.CID...

You can use a query to find the employees who have been assigned within or overlapping the required date/time range of the task. Then, based on that query, use another query to list the employees who have not been assigned and therefore are available for the task. I have attached a sample...

See the query in the attached database. Normally we don't store calculated values in a table (in your case Total_Obtained and Remarks.) We just do the calculations whenever the values are needed. Your table looks more like an Excel spreadsheet than a database table. If you find it difficult to...

See the database. The subform is based on Query1. .

Try using the keyword DistinctRow in the query. (There is no need to use a Where Clause.) DELETE DistinctRow Table1.* FROM Table1 INNER JOIN Table2 ON Table1.ArtID=Table2.ArtID; .

Phil I'm glad you find the sample helpful. Thanks for your kind donation offer. It's really not much effort that I put into the sample. Your feedback that it worked is already good enough. Many of our suggestions/solutions just went without feedback or a word of thanks in return. Jon .

To use multi select list boxes as query criteria, you have to modify the QueryDef on the fly with code similar to that in your link. See the attached database. .

Maybe the following example sentence from the Oxford Advanced Learner's Dictionary can show you what the word percentile means. "Overall these students rank in the 21st percentile on the tests - that is, they did worse than 79 per cent of all children taking the test." .

Pat has pointed you in the right direction. Just do it in the update query Design View. You don't need to type any field names. All you need to type is the word False in the Update To: row for each field. If you need the SQL statement, after creating the update query in Design View, you can...

You don't need to Sum the field in order to run the percentile function. See the query in the attached database (and a new percentile function which returns the same percentile values as in Excel.) The percentile values returned by your function are different. Maybe the underlying formulas...

You can build another Top 3 query based on the existing one. .

I thought you were looking for a technique. .

You can run an Append Query with the Values key word: INSERT INTO [TableName] ([Description]) VALUES ([Forms]![FormName]![ComboBoxName]); When the query is run, the value in the combo box's Bound Column will be appended to the Description field of the table. .

Would like to modify the InStr() expression in the query a little so that the user can type the parameter values 1,2,33 etc. without the need of typing the leading and ending commas. SELECT tbl_Placeview.ID, tbl_Placeview.CategoryID FROM tbl_Placeview WHERE InStr("," &...