Trouble with queries

[Guest]

I am trying to create a query that compares trial balance data from four
different companies by account in the below format:

Account Description company1balance company2balance company3balance .....

I have one table for each company that I have imported the trial balances
to...the problem is, the Trial balance files I'm importing do not include any
accounts for which the balance for the account is 0, so when I try to compare
the account balances, some of them don't show up in my query because one of
the four tables doesn't have an entry for that account.

I've created a query that gives me a complete list of all the accounts I
need to compare, but how can I set a query up so that if a field doesn't
exist, it shows a 0 instead of removing the account completely from my
results? I've tried the below syntax, but it didn't help. (TLJ is one of my
Trial Balance files) Any ideas?

TJL: IIf(IsNull(CompleteTLJTB!F3),0,CompleteTLJTB!F3)

 

[Michel Walsh]

Hi,


You probably use inner joins while you need outer joins:


SELECT ...
FROM tableWithAllAccounts AS a LEFT JOIN tableWithMissingAccounts AS b
ON a.accountID = b.accountID
....



The missing records, in the table aliased AS b, will be present, with NULL
values (since absent) while having their original value with fields from
table aliased AS a. You can use Nz(b.somefield, 0) to replace the null by
a 0, or Nz(b.accountID, a.accountID) to replace the Null by some "b"
missing field with a value picked up from the "a" table



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you! the NZ function is great! I'd never used that before.