RE: Aggregates, Joins, I am totally stuck on this issue

That is weird.. you mean if you reference the field from the "right" table in
a left outer join it makes it an inner join? That would make access'
solution for finding non-matching records between two table not work. Ya
know, you tell it to find the records in one table that arent in another, so
it does a join and checks that the id field from the "right" table equals
null...

Thanks for the hint

k

"ChrisJ" wrote:

> One thing you should check.
> If you are testing a field in a table on the "outer" side of a left join
> (for example, in a where clause) access will do an inner join instead of a
> left join. This could explain your missing records
>
> "ken" wrote:
>
> > I have got a weird problem, quite possibly the answer is "anything that
> > complicated should be done in SQL Server, not access," but I will ask anyway
> > because I am stuck working in access for now.
> >
> > I am trying to create a data mart (or data warehouse) with a central summary
> > table. The table has a 8 part unique key and about 10 currency fields. I
> > actually made a seperate autonumber primary key, but the 8 part key is the
> > logical key of the table although 3 of the fields can be null.
> >
> > For speed reasons I am trying to fill most of the information using append
> > queries rather than looping through the recordset using VBA code and doing
> > each insert/update. So, I have two aggregate queries which are grouped on
> > the 8 part key and I want to join these two together into a single select
> > query and then create an append query which puts the results of this master
> > query into the table. One of the queries will have 3 of the fields null all
> > the time, the other will have some rows with all 8 filled in, and some with 3
> > null and 5 with values. So, I join the queries together in a new query with
> > a LEFT OUTER JOIN and run it and what do it get? NOT what i should, there are
> > a bunch of records missing. So, I figure it's the null values so i use the
> > nz(field,default) function to turn the possibly null fields into 0's or empty
> > strings depending on data type and try it again. I am still getting back
> > only records where the 3 optional fields are empty (string or 0).
> >
> > It is like access is refusing to do a left join and forcing it to be an
> > inner join..??
> > I am a pretty decent developer so I am pretty sure it is not something
> > totally obvious.. I am just seeing some really weird behavior from access on
> > this.
> >
> > I do not know if it is a problem with joining queries that are themselves
> > aggregate queries.. or if access has some kind of SERIOUS problem with doing
> > joins on fields that can be null, even if you use nz() to alter the value to
> > a non-null substitute.
> >
> > I am quite stuck on this and looping through to insert/update each record
> > makes the process take 90 minutes instead of 20 minutes so if you can help I
> > would really appreciate it. If you have done anything like this in access
> > before, feel free to offer any advice you might have.
> >
> > Thanks
.

Relevant Pages

  • Re: Weekly crosstab query
    ... queries for the other information and then linking the queries together by ... ensure that the queries return data for the same set of employees. ... Query Two uses query one in place of the OvertimeTracking table ... INNER JOIN qPrior ON ...
    (microsoft.public.access.queries)
  • Re: how can i make this query run faster
    ... your query is very hard to read, ... that this LEFT JOIN can be changed to INNER JOIN. ... An outer join usually means that the optimizer has to use a ... specific access path, ...
    (microsoft.public.sqlserver.programming)
  • Re: Relational question
    ... left outer join ... >) as AB inner join ( ... >>Let's look at the first LEFT JOIN query: ... >>which we'll refer to collectively as and the constituent queries ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Error Message
    ... Are you building a UNION query? ... You can simplify it by removing the ORDER BY in each of the three queries ... > Single_Insured.) INNER JOIN (Single_Commissions INNER JOIN ...
    (microsoft.public.access.queries)
  • Re: Getting the High and Low Tides from a Series of Data
    ... queries, sub queries and virtual table) will extract all occurrences ... of an extremum. ... I prefer method 3, a total query, because it ... FROM myTable INNER JOIN query1 ...
    (microsoft.public.access.queries)