Hi Allison,
I think the following should work for you (provided I
didn't make any typos, but hopefully if I did you will
get the idea of what it is trying to do). The only
records that wouldn't be returned (other than those that
match) would be if a field is 0 in one table and null in
the other (because the Nz() function will cause nulls to
be treated as 0's). Post back if these records would be
important to you and we could revise the query or put
together a second query to append those cases.
SELECT tblMPACT.FY, tblMPACT.[GRANT ID],
tblMPACT.DIVISION, tblMPACT.APPL_ID,
tblMPACT.ACTIVITY_CODE, tblMPACT.CAN_ICD,
tblMPACT.SOURCE_CODE_DC, tblMPACT.TOTAL_AWARDED_AMT,
tblMPACT.FUTURE_YEAR_1_AMT, tblMPACT.FUTURE_YEAR_2_AMT,
tblMPACT.FUTURE_YEAR_3_AMT, tblMPACT.FUTURE_YEAR_4_AMT,
tblNEWNONCOM.FY, tblNEWNONCOM.[GRANT ID],
tblNEWNONCOM.DIVISION, tblNEWNONCOM.APPLID,
tblNEWNONCOM.PAID, tblNEWNONCOM.ACT,
tblNEWNONCOM.OBLIGATED, tblNEWNONCOM.FUT1,
tblNEWNONCOM.FUT2, tblNEWNONCOM.FUT3, tblNEWNONCOM.FUT4,
nz([FUTURE_YEAR_1_AMT],0)-nz([FUT1],0) AS DIFF1, nz
([FUTURE_YEAR_2_AMT],0)- nz([FUT2],0) AS DIFF2, nz
([FUTURE_YEAR_3_AMT],0)-nz([FUT3],0) AS DIFF3,
nz([FUTURE_YEAR_4_AMT],0)-nz([FUT4],0) AS DIFF4 INTO ACT
FROM tblMPACT INNER JOIN tblNEWNONCOM ON tblMPACT.[GRANT
ID] = tblNEWNONCOM.[GRANT ID] WHERE (nz
([FUTURE_YEAR_1_AMT],0)-nz([FUT1],0))<>0 OR (nz
([FUTURE_YEAR_2_AMT],0)-nz([FUT2],0))<>0 OR (nz
([FUTURE_YEAR_3_AMT],0)-nz([FUT3],0))<>0 OR (nz
([FUTURE_YEAR_4_AMT],0)-nz([FUT4],0))<>0;
Hope that helps. Post back if it doesn't work.
-Ted Allen
-----Original Message-----
Ted,
I've been ask to prepare a query that will compute the
differences between the Future Years of two different
tables. If one of the differences fields has a zero, null
value I do not want those to appear when I run the query
but if the difference is a negative number other than
zero "0", I would like it to show up. In this exercise we
are trying to determine which future year records we need
to correct. If both tables future years don't match then
we need to make a change be it positive or negative.
Thanks,
Alice
SELECT tblMPACT.FY, tblMPACT.[GRANT ID],
tblMPACT.DIVISION, tblMPACT.APPL_ID,
tblMPACT.ACTIVITY_CODE, tblMPACT.CAN_ICD,
tblMPACT.SOURCE_CODE_DC, tblMPACT.TOTAL_AWARDED_AMT,
tblMPACT.FUTURE_YEAR_1_AMT, tblMPACT.FUTURE_YEAR_2_AMT,
tblMPACT.FUTURE_YEAR_3_AMT, tblMPACT.FUTURE_YEAR_4_AMT,
tblNEWNONCOM.FY, tblNEWNONCOM.[GRANT ID],
tblNEWNONCOM.DIVISION, tblNEWNONCOM.APPLID,
tblNEWNONCOM.PAID, tblNEWNONCOM.ACT,
tblNEWNONCOM.OBLIGATED, tblNEWNONCOM.FUT1,
tblNEWNONCOM.FUT2, tblNEWNONCOM.FUT3, tblNEWNONCOM.FUT4,
[FUTURE_YEAR_1_AMT]-[FUT1] AS DIFF1, [FUTURE_YEAR_2_AMT]-
[FUT2] AS DIFF2, [FUTURE_YEAR_3_AMT]-[FUT3] AS DIFF3,
[FUTURE_YEAR_4_AMT]-[FUT4] AS DIFF4 INTO ACT
FROM tblMPACT INNER JOIN tblNEWNONCOM ON tblMPACT.[GRANT
ID] = tblNEWNONCOM.[GRANT ID];
-----Original Message-----
Hi Allison,
Could you post the sql text for your query (If you
haven't done this before you just switch the query to SQL
view and copy the text, then paste into a post). I'll
take a look at it and see if I can spot the problem.
-Ted Allen
-----Original Message-----
I would like to use the Nz()function but your suggestion
below is not working??? Here are the names of each field
in my make table query.
DIFF1: [FUTURE_YEAR_1_AMT]-[FUT1]
tblIMPACT tblNEWNONCOM
DIFF2: [FUTURE_YEAR_2_AMT]-[FUT2]
tblIMPACT tblNEWNONCOM
DIFF3: [FUTURE_YEAR_3_AMT]-[FUT3]
tblIMPACTII tblNEWNONCOM
DIFF4: [FUTURE_YEAR_4_AMT]-[FUT4]
tblIMPACTII tblNEWNONCOM
-----Original Message-----
Hi Allison,
It seems like you should be able to get what you want by
entering the criteria that the fields should be greater
than 0 (just type a greater than symbol and a 0). You
would enter this on separate rows beneath each calculated
field if you want all records that have a difference in
any of them. Or, you would enter them all on one row if
you want an AND condition (only the rows that have a
difference in all 4 fields).
But, you may want to modify your difference calculations
to use the Nz() function to treat nulls as 0's. So, your
Diff1 expression would be:
Diff1: Nz([Impac table].[FutYear1],0)- Nz([Noncom table].
[Fut Year1],0)
HTH
-Ted Allen
-----Original Message-----
IIF(0 or IsNull, "")
Can I use this above criteria to filter out null or zero
values
in the fields below?? I'm trying capture only those
fields that I have to change in either table or in this
case system.
I have 4 fields labeled differences if the
difference
is
zero or a null then I don't want it to show:
Diff 1 [FutYear1](Impac table)-[Fut Year1](Noncom
table)
Diff 2
Diff 3
Diff 4
.
.
.
.
.
