Re: "Where" column limits records regardless of row

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jon5001 (Jon5001_at_discussions.microsoft.com)
Date: 03/13/05 

Date: Sat, 12 Mar 2005 17:03:01 -0800

Thanks for your help.

I don't believe either of those situations apply. The two tables have an
inner join and none of the four fields used have any null values.

Here is the SQL statement:

SELECT Sum([2004 Cost By Class].[Invoice Amount]) AS [SumOfInvoice Amount],
[2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT DATABASE].OM

FROM [qry SAME STORES IN IPT DATABASE] INNER JOIN [2004 Cost By Class] ON
[qry SAME STORES IN IPT DATABASE].[SITE ID] = [2004 Cost By Class].[Store
Number]

WHERE ((([2004 Cost By Class].[Problem Description]) Like "hand*"))
GROUP BY [2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM
HAVING ((([qry SAME STORES IN IPT DATABASE].OM)=710)) OR ((([qry SAME STORES
IN IPT DATABASE].OM)=711));

"Allen Browne" wrote:

> Do any of these columns involve nulls?
>
> Or are they from different tables with outer joins, so that a Where on the
> related table limits the query to records where there is some value in the
> related table?
>
> If neither of those issues apply, post the SQL statement (SQL View on View
> menu, from query design window).
>
> --
> 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.
>
> "Jon5001" <Jon5001@discussions.microsoft.com> wrote in message
> news:0C2578DD-47AA-4F16-AA8B-51D6D524487F@microsoft.com...
> > I've noticed that when I create a totals query and have "Where" in the
> > totals
> > cell, Access will use "AND" to combine the criteria in the "Where" column
> > with the criteria in other columns - even when they are in different rows!
> > Everything I've read seems to say that if criteria are on different rows,
> > the
> > criteria will be combined with an "Or" instead.
> >
> > Could someone please explain what is going on? This is driving me insane!
> >
> > Thanks!
>
>
>

Relevant Pages

  • Re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
    ... Sub Tester() ... Dim r As Recordset ... FROM tblBatches INNER JOIN (tblClusters INNER JOIN ((tblAdmin INNER ... you have a where statement that looks the same as the where statement in your SQL statement. ...
    (comp.databases.ms-access)
  • Re: Slow GROUP BY
    ... ' 'create primary key ... PunchList INNER JOIN Lot ON PunchList.LotId = Lot.Id INNER JOIN PunchItem ON ... the exact schema of the parent and child table ... your exact SQL statement that includes the GROUP BY ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Delete Query
    ... Susan ... >> A Delete Query needs to have the table specified. ... >> FROM ALMOST INNER JOIN 2_073105 ... >>> Below is the sql statement. ...
    (microsoft.public.access.queries)
  • SQL and recordset.Open (still having problems)
    ... SELECT ACCOUNT_LIST.COMPLEMENTOR FROM REP_LIST INNER JOIN ... I have the SQL statement variable declared as: ... >> strsearch variable. ... The error is telling you that the SQL command is ...
    (microsoft.public.access.modulesdaovba)
  • Re: Inner join example
    ... (stores the master info) ... Create this query and call it step1 ... SELECT tblMaster.*, tbl1.naam1 ... FROM tbl3 INNER JOIN (tbl2 INNER JOIN (tbl1 INNER JOIN tblMaster ON ...
    (comp.databases.ms-access)