|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 24th, 2004, 08:05 PM
|
|||
|
|||
Criteria for make table Query
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 . . 
|
|
#2
May 26th, 2004, 04:21 PM
|
|||
|
|||
|
Criteria for make table Query
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 . . .
|
|
#3
May 26th, 2004, 07:44 PM
|
|||
|
|||
|
Criteria for make table Query
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 . . . .
|
|
#4
May 26th, 2004, 11:59 PM
|
|||
|
|||
|
Criteria for make table Query
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 . . . . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
