Aggregates, Joins, I am totally stuck on this issue

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: Data Changing Issue
    ... In the query, on the field line, I added the above expression. ... *if* you are in VBA. ... the data of your table from a recordset. ... I am running some queries using a form I created and for some ...
    (microsoft.public.access.queries)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)