Aggregates, Joins, I am totally stuck on this issue

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Don't get me wrong here.
You can use criteria tests on the field that is part of the join, as in...

SELECT tblIdentity.*
FROM tblMember LEFT JOIN tblMember ON tblIdentity.ID = tblMember.ID
WHERE tblMember.ID Is Null;

This will correctly return all Identities who are not members
But if for example you try

SELECT tblIdentity.*
FROM tblIdentity LEFT JOIN tblMember ON tblIdentity.ID = tblMember.ID
WHERE tblMember.StartDate <#Jan 01 2004#;

This will treat the left join as an inner join unless you add
WHERE tblMember.StartDate <#Jan 01 2004# or tblMember.StartDate is null;

If you have multiple criteria it quickly gets very messy, and it might pay
to create a "prefiltered" query to use in place of the table.

Sorry for the confusion