GROUP BY???
in [Queries]
|
From: Faraz A. Qureshi on 10 Feb 2010 02:55 Upon creating a simple query requiring a table with following fileds: Field1, Field2, Field3 To be grouped as follows: SELECT Table1.Field1, Table1.Field2, Sum(Table1.Field3) AS SumOfField3 FROM Table1 GROUP BY Table1.Field1, Table1.Field2; I have found that still duplicates are occurring and Field1 & Field2, although grouped, appear more than once. What might be the reason? and the way out??? -- Thanx in advance, Best Regards, Faraz
From: Allen Browne on 10 Feb 2010 04:35 What data types are field1 and Field2? Text fields may have differences that are not immediately obvious (e.g. a leading space, a second line in the field, or a zero-length-string as distinct from Null.) Floating point fields (Double, Single, or even Date) may have fractional values that *display* the same, but are slightly different. If there is anything in the Format property, that could also mask different values so they appear to be the same. If the query's source is actually another multi-table query with an outer join, grouping on yes/no fields can fail as discussed here: http://allenbrowne.com/bug-14.html Also, if Field1 or Field2 contains a Null, results may be inconsistent across different data engines. If none of those apply, tell us your version of Access, and whether these are local tables (in the database), or attached tables (from another database -- if so, Access or what kind?) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Faraz A. Qureshi" <FarazAQureshi(a)discussions.microsoft.com> wrote in message news:1C22C84A-19DF-49EC-82A9-6BFD5BF0BEAF(a)microsoft.com... > Upon creating a simple query requiring a table with following fileds: > Field1, Field2, Field3 > > To be grouped as follows: > > SELECT Table1.Field1, Table1.Field2, Sum(Table1.Field3) AS SumOfField3 > FROM Table1 > GROUP BY Table1.Field1, Table1.Field2; > > I have found that still duplicates are occurring and Field1 & Field2, > although grouped, appear more than once. > > What might be the reason? and the way out??? > > -- > Thanx in advance, > Best Regards, > > Faraz
|
Pages: 1 Prev: Copying records from one (sub)form to another subform using Append Next: SPECIAL QUERY |