Access Question?

Question

I have a query merging two different tables. All the records in table one are not in table two, so they come up as null values. How can I state in the query to make null values from table one to be zeros or else use the number from table two that's provided.

Answer 1

Sample data:

Table1:
IDNO, AMOUNT
F001, NULL
F002, NULL
F003, NULL

Table2:
IDNO, AMOUNT
F003, 5
F005, NULL
F006, NULL

1. As your statement is not VERY clear to me, I have two solutions to your question.
2. My first solution (If I’m right), Table1 is your MASTER table, and Table2 is JUST a lookup table. Now, if there’s SAME data from Table2, it will get the information there.
3. Solution:

Query1:
SELECT Table1.idno,
iif(isnull(Table2.AMOUNT),0,
Table2.AMOUNT)
AS AMOUNT FROM Table2 RIGHT JOIN Table1
ON Table2.idno=Table1.idno;

Result (from Query1):
IDNO,AMOUNT
F001,0
F002,0
F003,5

3. Second solution. Merge two tables. If same ID but null values in Table1, we will get values from Table2.
4. Solution: Make 4 queries!

Query3:
SELECT ALL *
FROM TABLE1
UNION SELECT ALL*
FROM Table2;

Query4:
SELECT IDNO, AMOUNT
FROM Query3
WHERE IDNO IN (SELECT IDNO FROM Query3 as Tmp GROUP BY IDNO HAVING COUNT(*)>1)
GROUP BY IDNO, AMOUNT
HAVING AMOUNT>0;

Query5:
SELECT a.*
FROM Query3 AS a
WHERE NOT a.IDNO IN
(SELECT b.IDNO FROM Query4 b
WHERE a.IDNO=b.IDNO);

Query6:
SELECT IDNO, IIF(ISNULL(Query4.AMOUNT),0,
Query4.AMOUNT) AS AMOUNT
FROM Query4
UNION SELECT IDNO,
IIF(ISNULL(Query5.AMOUNT),0,
Query5.AMOUNT) AS AMOUNT
FROM Query5;

Result (from Query6):
IDNO,AMOUNT
F001,0
F002,0
F003,5
F005,0
F006,0

Answer 2

Like this....

IIf([tblA.Field1] <> [tblB.Field1], "0", "1")

What this expression does is... It compares a column value (Field1) from tblA with a column in tblB and if it doesn't match, then, put a 0, otherwise, put 1...

Note: If you can't see the entire expression, hover your mouse overe it...

Answer 3

just set the default value of the fields to the value that you want.

Answer 4

ye4s but you will have to us something like VBA(visual basic for aplications)

IF you need help