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
|
|||
|
|||
Query for 'confirmation'
Please help. I am trying to make updating a 'confirmation' routine quick and
easily updateable by some one else. I have 2 tables, one of which I would like to use to confirm data. tblTitle: tblState: RecordID RecordID Title ParentID State State Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State' using 'tblState'. 'tblTitle' is imported from a text file. What I would like to do, preferably using only SQL, is to program a field similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not included in tblState.State; "«Unrecognized State»"; tblTitle.State)' So other users can update 'tblState' as necessary to account for changes in the exported text file, I wish to avoid: nested IIf without using tblState additinal VB code to loop through tblState (A little background, this frontend does, hopefully, every thing but the data collection for project tracking. Why on earth anyone would make a 'Nice' project tracking software with out reporting more than lists of records is beyond my comprehension.) thanks, Rogge |
#2
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 If the table tblTitle is imported, then it IS an Access table and relationships can be created between other tables. But, you really don't need that. If I understand your problem statement correctly, you want to substitute "«Unrecognized State»" for the state when the tblTitle State is not in tblState. You could write a query like this: SELECT T.State As Title_State, Nz(S.State,"«Unrecognized State»") As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL Since you need to know which state is missing from tblState the above query shows only which states are missing. If you want to see all states, including missing states, remove the WHERE clause. The Nz() function returns the 2nd parameter when the 1st parameter is null. The LEFT JOIN returns all rows from tblTitle and NULL rows from tblState when there isn't a equivalent State in tblState. If the tblState.State column is NULL the Nz() function will return "«Unrecognized State»." -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE HImLsgHGenZNqYm0hiw9kRL0 =66PA -----END PGP SIGNATURE----- rogge wrote: Please help. I am trying to make updating a 'confirmation' routine quick and easily updateable by some one else. I have 2 tables, one of which I would like to use to confirm data. tblTitle: tblState: RecordID RecordID Title ParentID State State Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State' using 'tblState'. 'tblTitle' is imported from a text file. What I would like to do, preferably using only SQL, is to program a field similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not included in tblState.State; "«Unrecognized State»"; tblTitle.State)' So other users can update 'tblState' as necessary to account for changes in the exported text file, I wish to avoid: nested IIf without using tblState additinal VB code to loop through tblState (A little background, this frontend does, hopefully, every thing but the data collection for project tracking. Why on earth anyone would make a 'Nice' project tracking software with out reporting more than lists of records is beyond my comprehension.) thanks, Rogge |
#3
|
|||
|
|||
I do use Nz(); I find it very usefull
But my problem lies in a 'non-recognized' value not a null value. Lets say 'tblTitle.State' has these values: 10. Initiation 20. In Progress 30. Waiting for Feedback 31. Waiting for Feedback - Check 40. Completed and 'tblState.State' has, as these 'official options' for the 'State' field: 10. Initiation 20. In Progress 30. Waiting for Feedback 40. Completed I would like "31. Waiting for Feedback - Check" to appear as "«Unrecognized State» (touch the data again - I'll send Luca Brasi.)" Well maybe I'll leave my personal comments out of the database.... LOL Have a great weekend! -r "MGFoster" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 If the table tblTitle is imported, then it IS an Access table and relationships can be created between other tables. But, you really don't need that. If I understand your problem statement correctly, you want to substitute "«Unrecognized State»" for the state when the tblTitle State is not in tblState. You could write a query like this: SELECT T.State As Title_State, Nz(S.State,"«Unrecognized State»") As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL Since you need to know which state is missing from tblState the above query shows only which states are missing. If you want to see all states, including missing states, remove the WHERE clause. The Nz() function returns the 2nd parameter when the 1st parameter is null. The LEFT JOIN returns all rows from tblTitle and NULL rows from tblState when there isn't a equivalent State in tblState. If the tblState.State column is NULL the Nz() function will return "«Unrecognized State»." -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE HImLsgHGenZNqYm0hiw9kRL0 =66PA -----END PGP SIGNATURE----- rogge wrote: Please help. I am trying to make updating a 'confirmation' routine quick and easily updateable by some one else. I have 2 tables, one of which I would like to use to confirm data. tblTitle: tblState: RecordID RecordID Title ParentID State State Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State' using 'tblState'. 'tblTitle' is imported from a text file. What I would like to do, preferably using only SQL, is to program a field similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not included in tblState.State; "«Unrecognized State»"; tblTitle.State)' So other users can update 'tblState' as necessary to account for changes in the exported text file, I wish to avoid: nested IIf without using tblState additinal VB code to loop through tblState (A little background, this frontend does, hopefully, every thing but the data collection for project tracking. Why on earth anyone would make a 'Nice' project tracking software with out reporting more than lists of records is beyond my comprehension.) thanks, Rogge |
#4
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmALLoechKqOuFEgEQKjpQCeMs1Xk/x90OzKocN/55EZiQFyP/UAnir/ cAEuhwANbWZ745LoAub0CZaD =LOmm -----END PGP SIGNATURE----- rogge wrote: I do use Nz(); I find it very usefull But my problem lies in a 'non-recognized' value not a null value. Lets say 'tblTitle.State' has these values: 10. Initiation 20. In Progress 30. Waiting for Feedback 31. Waiting for Feedback - Check 40. Completed and 'tblState.State' has, as these 'official options' for the 'State' field: 10. Initiation 20. In Progress 30. Waiting for Feedback 40. Completed I would like "31. Waiting for Feedback - Check" to appear as "«Unrecognized State» (touch the data again - I'll send Luca Brasi.)" Well maybe I'll leave my personal comments out of the database.... LOL Have a great weekend! -r "MGFoster" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 If the table tblTitle is imported, then it IS an Access table and relationships can be created between other tables. But, you really don't need that. If I understand your problem statement correctly, you want to substitute "«Unrecognized State»" for the state when the tblTitle State is not in tblState. You could write a query like this: SELECT T.State As Title_State, Nz(S.State,"«Unrecognized State»") As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL Since you need to know which state is missing from tblState the above query shows only which states are missing. If you want to see all states, including missing states, remove the WHERE clause. The Nz() function returns the 2nd parameter when the 1st parameter is null. The LEFT JOIN returns all rows from tblTitle and NULL rows from tblState when there isn't a equivalent State in tblState. If the tblState.State column is NULL the Nz() function will return "«Unrecognized State»." -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7L8YechKqOuFEgEQIsyACeLXYawn5cnF4hjk5VrH6ijf tXT9sAoJvE HImLsgHGenZNqYm0hiw9kRL0 =66PA -----END PGP SIGNATURE----- rogge wrote: Please help. I am trying to make updating a 'confirmation' routine quick and easily updateable by some one else. I have 2 tables, one of which I would like to use to confirm data. tblTitle: tblState: RecordID RecordID Title ParentID State State Unfortunately, I cannot use a 'real' relationship to define 'tblTitle.State' using 'tblState'. 'tblTitle' is imported from a text file. What I would like to do, preferably using only SQL, is to program a field similar to this expression (using pseudo code) 'St: IIf(tblTitle.State not included in tblState.State; "«Unrecognized State»"; tblTitle.State)' So other users can update 'tblState' as necessary to account for changes in the exported text file, I wish to avoid: nested IIf without using tblState additinal VB code to loop through tblState (A little background, this frontend does, hopefully, every thing but the data collection for project tracking. Why on earth anyone would make a 'Nice' project tracking software with out reporting more than lists of records is beyond my comprehension.) thanks, Rogge |
#5
|
|||
|
|||
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On further thought you could run a query like the following. It finds all States in tblTitle that are not in tblState. Same thing I had before just w/o the Nz() function.: SELECT T.State As Title_State, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ lXY8gjObMp07a3gsiEr+Mmzu =nwV8 -----END PGP SIGNATURE----- |
#6
|
|||
|
|||
The entire record needs to be returned where unrecognized values are shown as
«Unrecognized State» with out replacing the data. for instance this is the data in the table Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil 31. Waiting for Feedback - Check what i want to return is... Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil «Unrecognized State» Thank you for your time and effort. "MGFoster" wrote: MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On further thought you could run a query like the following. It finds all States in tblTitle that are not in tblState. Same thing I had before just w/o the Nz() function.: SELECT T.State As Title_State, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ lXY8gjObMp07a3gsiEr+Mmzu =nwV8 -----END PGP SIGNATURE----- |
#7
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 That's simple enough: SELECT T.*, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL If this doesn't satisfy your needs, please post the complete definition of the tables (column names & their data types) and some sample data from both tables with column names over each data column. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmPyD4echKqOuFEgEQIPbACgt9ZmEr2zenlWPjPmvBZqe5 dL1JUAn3tN L0TzRl0bgH3a8iPZ2Kx7mU5H =mKnk -----END PGP SIGNATURE----- rogge wrote: The entire record needs to be returned where unrecognized values are shown as «Unrecognized State» with out replacing the data. for instance this is the data in the table Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil 31. Waiting for Feedback - Check what i want to return is... Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil «Unrecognized State» Thank you for your time and effort. "MGFoster" wrote: MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On further thought you could run a query like the following. It finds all States in tblTitle that are not in tblState. Same thing I had before just w/o the Nz() function.: SELECT T.State As Title_State, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ lXY8gjObMp07a3gsiEr+Mmzu =nwV8 -----END PGP SIGNATURE----- |
#8
|
|||
|
|||
that returns records where title.State is unrecognized, but the entire data
set needs to be returned. Hope this helps... RFS table Col1="Accepted Assessment Charge" Text Width 50 Col2="Actual Assessement Charge" Text Width 50 Col3="Agreement Reference" Text Width 255 Col4="Agreement Reference Details" Text Width 255 Col5=Area Text Width 255 Col6=BTT Text Width 255 Col7="Billing Completed" Date Col8="CUST CPM" Bit Col9="Canceled the RFS" Date Col10=Category Text Width 255 Col11=Condition Text Width 255 Col12="Created By" Text Width 255 Col13="Cust Account Code" Text Width 255 Col14="Customer Approver" Text Width 255 Col15="Customer Contact" Text Width 255 Col16="Customer Reference" Text Width 255 Col17="Data Classification" Text Width 255 Col18="Date Cust Accepted Charge" Date Col19=Delete Bit Col20="Delivery Authorized" Date Col21=Dependency Text Width 255 Col22="Send Notification" Bit Col23="Estimated Assessement Charge" Text Width 255 Col24="First Name" Text Width 255 Col25="IBM Account Code" Text Width 255 Col26="IBM CPM" Bit Col27="Implementation Completed" Date Col28="Implementation Planned" Date Col29="Implementation Requested" Date Col30="Implementation Revised" Date Col31="In Scope" Bit Col32="Key Date" Bit Col33="Level of Approval" Text Width 255 Col34="Local Price" Text Width 50 Col35=Location Text Width 255 Col36="Long Description" LongChar Col37=MPS Bit Col38="Opportunity Owner" Text Width 255 Col39="Opportunity Type" Text Width 255 Col40="Record ID" Text Width 255 Col41="Originating Organization" Text Width 255 Col42=Originator Text Width 255 Col43="Assigned To Position" Text Width 255 Col44=Price Text Width 50 Col45=Priority Text Width 255 Col46="Program Date 1" Date Col47="Program Date 2" Date Col48="Program Date 3" Date Col49="Program Date 4" Date Col50="Program Defined 1" Text Width 255 Col51="Program Defined 2" Text Width 255 Col52="Program Defined 3" Text Width 255 Col53="Program Defined 4" Text Width 255 Col54="Program Defined 5" Text Width 255 Col55="Program Defined 6" Text Width 255 Col56="Proposal Completed" Date Col57="Proposal Planned" Date Col58="Proposal Planned Revision" Date Col59="Proposal Requested" Date Col60="Proposal Response Accepted" Date Col61="Proposal Response Planned" Date Col62="Proposal Response Rejected" Date Col63="Status Reason" Text Width 255 Col64="Rejected the RFS" Date Col65=Remarks Text Width 255 Col66="Request Accepted" Date Col67="Request Submitted" Date Col68="Requirements Agreed" Date Col69="Service Provider Org" Text Width 255 Col70="Solution Accepted" Date Col71="Solution Completed" Date Col72="Solution Planned" Date Col73=Status Text Width 255 Col74=System Text Width 255 Col75=Title Text Width 255 Col76="v1 ID" Text Width 255 Col77=Country Text Width 255 Col78="Program Keyword" Text Width 255 Col79=Region Text Width 255 Col80="Related ID" Text Width 255 Col81="Related Title" Text Width 255 Col82="Related Type" Text Width 255 Col83=State Text Width 255 Col84="Brief Description" LongChar Col85="Assigned To" Text Width 255 Col86="Date Created" Date Col87="Assigned Organization" Text Width 255 11 records from RFS table: ID Accepted Assessment Charge Actual Assessement Charge Agreement Reference Agreement Reference Details Area BTT Billing Completed CUST CPM Canceled the RFS Category Condition Created By Cust Account Code Customer Approver Customer Contact Customer Reference Data Classification Date Cust Accepted Charge Delete Delivery Authorized Dependency Send Notification Estimated Assessement Charge First Name IBM Account Code IBM CPM Implementation Completed Implementation Planned Implementation Requested Implementation Revised In Scope Key Date Level of Approval Local Price Location Long Description MPS Opportunity Owner Opportunity Type Record ID Originating Organization Originator Assigned To Position Price Priority Program Date 1 Program Date 2 Program Date 3 Program Date 4 Program Defined 1 Program Defined 2 Program Defined 3 Program Defined 4 Program Defined 5 Program Defined 6 Proposal Completed Proposal Planned Proposal Planned Revision Proposal Requested Proposal Response Accepted Proposal Response Planned Proposal Response Rejected Status Reason Rejected the RFS Remarks Request Accepted Request Submitted Requirements Agreed Service Provider Org Solution Accepted Solution Completed Solution Planned Status System Title v1 ID Country Program Keyword Region Related ID Related Title Related Type State Brief Description Assigned To Date Created Assigned Organization 4972 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20041223-002 No Yes Sylvie No No No No Epinat 1-93FU9 Epinat ABB IBM PL 14 Medium No Reason 23.12.2004 20. Ready for IBM Review ABB04FR0243 - Nouveau serveur CAO Chassieu 10. Initiation RFS 243 Epinat 14.01.2005 09:59:57 ABB France IBM 5124 Master Agreement No Green Epinat Philippe mahistre Philippe Mahistre FRENT RFS20050209-002 No Yes Sylvie No No No No Epinat 1-9Q819 Epinat ABB IBM PL 14 Medium No Reason 09.02.2005 20. Ready for IBM Review ABB05FR0257 - Mise Ã* jour de la base Germain Moreau 14. Initiation RFS 257 Epinat 14.02.2005 15:07:34 ABB France IBM 5123 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20050209-001 No Yes Sylvie No No No No Epinat 1-9Q80X Epinat ABB IBM PL 14 Medium No Reason 09.02.2005 20. Ready for IBM Review ABB05FR0256 - Mise en place de fichier FTP sur le serveur de Villeurbanne 15. Initiation RFS 256 Epinat 14.02.2005 15:05:11 ABB France IBM 5217 Master Agreement No Network Green Romberg V810 Lennart G Hansson Benny Jonsson ATVA/NO No Yes Marie No No No Bnr:358 No Marie Carlsson 1-A8OJF AT Sven Miller ABB IBM PL 74 Medium Insc - bill - BTT = D1 No Reason 07.03.2005 20. Ready for IBM Review ABB05SE2342 16. Initiation AQ, flyt av uttag och PC Carlsson 07.03.2005 12:27:57 ABB Sweden IBM 5197 Master Agreement No Green Linton Ellingwood, Steve Samodell, Ryan No No Michael J. (Mike) No 01.04.2005 No No Newark move office location No Linton 1-A5QI1 Linton ABB IBM IG 118 Medium 28.02.2005 No Reason 28.02.2005 28.02.2005 20. Ready for IBM Review ABB04US284 Newark Delaware Office move Northeast 20. Solution Design Kerkau 02.03.2005 22:22:54 ABB USA IBM 4749 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20041115-002 No Yes Sylvie No No No €2,964 No Epinat 1-89EQD Epinat ABB IBM PL 14 Medium NM 12.01.2005 No Reason 15.11.2004 50. Ready for Customer Review ABB04FR0209 - Installer des postes clients Winchill 30. Proposal Development RFS 209 Epinat 22.11.2004 16:25:37 ABB France IBM 4504 Master Agreement No Green Katwa John Ellis-Braithwaite Chris Wakefield JEBE-65SKHW No Yes A (Amit) No No No £3,300 No Katwa 1-7K79W ABB UK IBM Katwa ABB IBM PL 63 5,115 Medium 07.01.2005 No Reason This has now been released to the customer by the Business Office 11.11.2004 15.10.2004 11.11.2004 50. Ready for Customer Review ABB04UK0173 - Support for Mayrise PCs 40. Proposal Review A meeting was held on 11 August with participants from ABB / IBM and Mayrise. Katwa 18.10.2004 11:52:34 ABB UK IBM 4856 Master Agreement No Midrange Green Carlsson SI 140 Stefan Gustafsson Rolf Missing PTTR/TR No Yes Susan No 31.03.2005 No No kr0 VästerÃ¥s No Susan Gustafsson 1-8P1J8 PT Tommy Jakobsen ABB IBM BOA 6 Medium NM Insc - bill - BTT = D1 24.01.2005 24.01.2005 26.01.2005 No Reason Begäran om att sätta denna ON Hold är skickad till ABB 2004-12-15 /Marie . Ej längre on hold from 2005-01-10 15.12.2004 13.12.2004 15.12.2004 18.01.2005 30. Assigned for Work ABB04SE2197 50. Implementation Decommissioning of servers Gustafsson 13.12.2004 21:51:28 ABB Sweden IBM 3632 Master Agreement 18.06.2004 No Green Harris John Ellis-Braithwaite PS PSWN-5XTBDP No No Derek F No 10.05.2004 No No £471 The current reception desk in Stone is being replaced by the desk out of Orion House. This is a slightly smaller desk and of a different shape. The current voice and PC equipment on & around the current desk needs disconnecting / moving and re-installing on the new desk. No Harris 1-3EBQL ABB UK IBM Philip Swain ABB IBM BOA 14 731 Medium 27.04.2004 30.04.2004 No Reason "Invoice Date 18/06/2004 Invoice Number 58060864 Resource £471.64 Total £471.64" 07.04.2004 07.04.2004 07.04.2004 23.04.2004 70. Closed ABB04UK0122 - Voice & LAN changes for New Stone Reception Desk 60. Complete Invoice Number 58060864 Smith 08.04.2004 12:02:25 ABB UK IBM 3755 Master Agreement No Green Massaro P Bo Mahler Stefan Ekman PTHV/HC No Yes No No No Karlskrona No Camilla Massaro 1-46KYM PT Stefan Ekman Deleted ABB IBM PL 77 Medium Insc - bill Not an RFS 04.05.2004 30.04.2004 70. Closed ABB04SE1768 70. Rejected EX30 Process-PC 03.05.2004 09:50:39 ABB Sweden IBM 2732 Schedule H No Green Duddek Zdenek Fanta Drossmann No No Doris No No No Solution completed, an Proposal gegangen; Kunde erhielt vorläufige Preisinfo mdl. 18.12.03 von G. Schröter; Angebot ist raus, Preisdiskussion mit ABB; ABB überlegt Reduzierung der Anforderungen. Zurückgezogn wg. Neuem Konzept No Baumann 1-19QO8 DE-IS Ocklenburg ABB IBM PL 32 Medium 22.01.2004 21.01.2004 10.12.2003 15.04.2004 Withdrawn by ABB see long description 10.11.2003 10.11.2003 Baumann / Schröter 10.12.2003 12.12.2003 70. Closed ABB03DE0057 CMS Lizenzserver BA PTMV 80. Canceled Hillmer 02.12.2003 11:21:37 ABB Germany IBM tblpDartState: Col1=RecordID Long Col2=State Text Width 50 Complete Data Set... RecordID State 1 10. Initiation 2 20. Solution Design 3 30. Proposal Development 4 40. Proposal Review 5 50. Implementation 6 60. Complete 7 70. Rejected 8 80. Canceled "MGFoster" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 That's simple enough: SELECT T.*, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL If this doesn't satisfy your needs, please post the complete definition of the tables (column names & their data types) and some sample data from both tables with column names over each data column. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmPyD4echKqOuFEgEQIPbACgt9ZmEr2zenlWPjPmvBZqe5 dL1JUAn3tN L0TzRl0bgH3a8iPZ2Kx7mU5H =mKnk -----END PGP SIGNATURE----- rogge wrote: The entire record needs to be returned where unrecognized values are shown as «Unrecognized State» with out replacing the data. for instance this is the data in the table Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil 31. Waiting for Feedback - Check what i want to return is... Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil «Unrecognized State» Thank you for your time and effort. "MGFoster" wrote: MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On further thought you could run a query like the following. It finds all States in tblTitle that are not in tblState. Same thing I had before just w/o the Nz() function.: SELECT T.State As Title_State, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ lXY8gjObMp07a3gsiEr+Mmzu =nwV8 -----END PGP SIGNATURE----- |
#9
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 Oye... Well, I asked for it. To get all the data just remove the WHERE clause: SELECT Nz(S.State,"«Unrecognized State»") As Dart_State, T.*, FROM tblRFS As T LEFT JOIN tblpDartState As S ON T.State = S.State If this doesn't satisfy your specs, then I don't understand what you're trying to do. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmUVCoechKqOuFEgEQLz6ACg/j2hzmSyLoTZ/Bh4w0v1zwIcUVIAoMds 1rn8JDCpuBsiUo5as3+mYieu =zquW -----END PGP SIGNATURE----- rogge wrote: that returns records where title.State is unrecognized, but the entire data set needs to be returned. Hope this helps... RFS table Col1="Accepted Assessment Charge" Text Width 50 Col2="Actual Assessement Charge" Text Width 50 Col3="Agreement Reference" Text Width 255 Col4="Agreement Reference Details" Text Width 255 Col5=Area Text Width 255 Col6=BTT Text Width 255 Col7="Billing Completed" Date Col8="CUST CPM" Bit Col9="Canceled the RFS" Date Col10=Category Text Width 255 Col11=Condition Text Width 255 Col12="Created By" Text Width 255 Col13="Cust Account Code" Text Width 255 Col14="Customer Approver" Text Width 255 Col15="Customer Contact" Text Width 255 Col16="Customer Reference" Text Width 255 Col17="Data Classification" Text Width 255 Col18="Date Cust Accepted Charge" Date Col19=Delete Bit Col20="Delivery Authorized" Date Col21=Dependency Text Width 255 Col22="Send Notification" Bit Col23="Estimated Assessement Charge" Text Width 255 Col24="First Name" Text Width 255 Col25="IBM Account Code" Text Width 255 Col26="IBM CPM" Bit Col27="Implementation Completed" Date Col28="Implementation Planned" Date Col29="Implementation Requested" Date Col30="Implementation Revised" Date Col31="In Scope" Bit Col32="Key Date" Bit Col33="Level of Approval" Text Width 255 Col34="Local Price" Text Width 50 Col35=Location Text Width 255 Col36="Long Description" LongChar Col37=MPS Bit Col38="Opportunity Owner" Text Width 255 Col39="Opportunity Type" Text Width 255 Col40="Record ID" Text Width 255 Col41="Originating Organization" Text Width 255 Col42=Originator Text Width 255 Col43="Assigned To Position" Text Width 255 Col44=Price Text Width 50 Col45=Priority Text Width 255 Col46="Program Date 1" Date Col47="Program Date 2" Date Col48="Program Date 3" Date Col49="Program Date 4" Date Col50="Program Defined 1" Text Width 255 Col51="Program Defined 2" Text Width 255 Col52="Program Defined 3" Text Width 255 Col53="Program Defined 4" Text Width 255 Col54="Program Defined 5" Text Width 255 Col55="Program Defined 6" Text Width 255 Col56="Proposal Completed" Date Col57="Proposal Planned" Date Col58="Proposal Planned Revision" Date Col59="Proposal Requested" Date Col60="Proposal Response Accepted" Date Col61="Proposal Response Planned" Date Col62="Proposal Response Rejected" Date Col63="Status Reason" Text Width 255 Col64="Rejected the RFS" Date Col65=Remarks Text Width 255 Col66="Request Accepted" Date Col67="Request Submitted" Date Col68="Requirements Agreed" Date Col69="Service Provider Org" Text Width 255 Col70="Solution Accepted" Date Col71="Solution Completed" Date Col72="Solution Planned" Date Col73=Status Text Width 255 Col74=System Text Width 255 Col75=Title Text Width 255 Col76="v1 ID" Text Width 255 Col77=Country Text Width 255 Col78="Program Keyword" Text Width 255 Col79=Region Text Width 255 Col80="Related ID" Text Width 255 Col81="Related Title" Text Width 255 Col82="Related Type" Text Width 255 Col83=State Text Width 255 Col84="Brief Description" LongChar Col85="Assigned To" Text Width 255 Col86="Date Created" Date Col87="Assigned Organization" Text Width 255 11 records from RFS table: ID Accepted Assessment Charge Actual Assessement Charge Agreement Reference Agreement Reference Details Area BTT Billing Completed CUST CPM Canceled the RFS Category Condition Created By Cust Account Code Customer Approver Customer Contact Customer Reference Data Classification Date Cust Accepted Charge Delete Delivery Authorized Dependency Send Notification Estimated Assessement Charge First Name IBM Account Code IBM CPM Implementation Completed Implementation Planned Implementation Requested Implementation Revised In Scope Key Date Level of Approval Local Price Location Long Description MPS Opportunity Owner Opportunity Type Record ID Originating Organization Originator Assigned To Position Price Priority Program Date 1 Program Date 2 Program Date 3 Program Date 4 Program Defined 1 Program Defined 2 Program Defined 3 Program Defined 4 Program Defined 5 Program Defined 6 Proposal Completed Proposal Planned Proposal Planned Revision Proposal Requested Proposal Response Accepted Proposal Response Planned Proposal Response Rejected Status Reason Rejected the RFS Remarks Request Accepted Request Submitted Requirements Agreed Service Provider Org Solution Accepted Solution Completed Solution Planned Status System Title v1 ID Country Program Keyword Region Related ID Related Title Related Type State Brief Description Assigned To Date Created Assigned Organization 4972 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20041223-002 No Yes Sylvie No No No No Epinat 1-93FU9 Epinat ABB IBM PL 14 Medium No Reason 23.12.2004 20. Ready for IBM Review ABB04FR0243 - Nouveau serveur CAO Chassieu 10. Initiation RFS 243 Epinat 14.01.2005 09:59:57 ABB France IBM 5124 Master Agreement No Green Epinat Philippe mahistre Philippe Mahistre FRENT RFS20050209-002 No Yes Sylvie No No No No Epinat 1-9Q819 Epinat ABB IBM PL 14 Medium No Reason 09.02.2005 20. Ready for IBM Review ABB05FR0257 - Mise Ã* jour de la base Germain Moreau 14. Initiation RFS 257 Epinat 14.02.2005 15:07:34 ABB France IBM 5123 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20050209-001 No Yes Sylvie No No No No Epinat 1-9Q80X Epinat ABB IBM PL 14 Medium No Reason 09.02.2005 20. Ready for IBM Review ABB05FR0256 - Mise en place de fichier FTP sur le serveur de Villeurbanne 15. Initiation RFS 256 Epinat 14.02.2005 15:05:11 ABB France IBM 5217 Master Agreement No Network Green Romberg V810 Lennart G Hansson Benny Jonsson ATVA/NO No Yes Marie No No No Bnr:358 No Marie Carlsson 1-A8OJF AT Sven Miller ABB IBM PL 74 Medium Insc - bill - BTT = D1 No Reason 07.03.2005 20. Ready for IBM Review ABB05SE2342 16. Initiation AQ, flyt av uttag och PC Carlsson 07.03.2005 12:27:57 ABB Sweden IBM 5197 Master Agreement No Green Linton Ellingwood, Steve Samodell, Ryan No No Michael J. (Mike) No 01.04.2005 No No Newark move office location No Linton 1-A5QI1 Linton ABB IBM IG 118 Medium 28.02.2005 No Reason 28.02.2005 28.02.2005 20. Ready for IBM Review ABB04US284 Newark Delaware Office move Northeast 20. Solution Design Kerkau 02.03.2005 22:22:54 ABB USA IBM 4749 Master Agreement No Green Epinat Philippe Mahistre Philippe Mahistre FRENT RFS20041115-002 No Yes Sylvie No No No €2,964 No Epinat 1-89EQD Epinat ABB IBM PL 14 Medium NM 12.01.2005 No Reason 15.11.2004 50. Ready for Customer Review ABB04FR0209 - Installer des postes clients Winchill 30. Proposal Development RFS 209 Epinat 22.11.2004 16:25:37 ABB France IBM 4504 Master Agreement No Green Katwa John Ellis-Braithwaite Chris Wakefield JEBE-65SKHW No Yes A (Amit) No No No £3,300 No Katwa 1-7K79W ABB UK IBM Katwa ABB IBM PL 63 5,115 Medium 07.01.2005 No Reason This has now been released to the customer by the Business Office 11.11.2004 15.10.2004 11.11.2004 50. Ready for Customer Review ABB04UK0173 - Support for Mayrise PCs 40. Proposal Review A meeting was held on 11 August with participants from ABB / IBM and Mayrise. Katwa 18.10.2004 11:52:34 ABB UK IBM 4856 Master Agreement No Midrange Green Carlsson SI 140 Stefan Gustafsson Rolf Missing PTTR/TR No Yes Susan No 31.03.2005 No No kr0 VästerÃ¥s No Susan Gustafsson 1-8P1J8 PT Tommy Jakobsen ABB IBM BOA 6 Medium NM Insc - bill - BTT = D1 24.01.2005 24.01.2005 26.01.2005 No Reason Begäran om att sätta denna ON Hold är skickad till ABB 2004-12-15 /Marie . Ej längre on hold from 2005-01-10 15.12.2004 13.12.2004 15.12.2004 18.01.2005 30. Assigned for Work ABB04SE2197 50. Implementation Decommissioning of servers Gustafsson 13.12.2004 21:51:28 ABB Sweden IBM 3632 Master Agreement 18.06.2004 No Green Harris John Ellis-Braithwaite PS PSWN-5XTBDP No No Derek F No 10.05.2004 No No £471 The current reception desk in Stone is being replaced by the desk out of Orion House. This is a slightly smaller desk and of a different shape. The current voice and PC equipment on & around the current desk needs disconnecting / moving and re-installing on the new desk. No Harris 1-3EBQL ABB UK IBM Philip Swain ABB IBM BOA 14 731 Medium 27.04.2004 30.04.2004 No Reason "Invoice Date 18/06/2004 Invoice Number 58060864 Resource £471.64 Total £471.64" 07.04.2004 07.04.2004 07.04.2004 23.04.2004 70. Closed ABB04UK0122 - Voice & LAN changes for New Stone Reception Desk 60. Complete Invoice Number 58060864 Smith 08.04.2004 12:02:25 ABB UK IBM 3755 Master Agreement No Green Massaro P Bo Mahler Stefan Ekman PTHV/HC No Yes No No No Karlskrona No Camilla Massaro 1-46KYM PT Stefan Ekman Deleted ABB IBM PL 77 Medium Insc - bill Not an RFS 04.05.2004 30.04.2004 70. Closed ABB04SE1768 70. Rejected EX30 Process-PC 03.05.2004 09:50:39 ABB Sweden IBM 2732 Schedule H No Green Duddek Zdenek Fanta Drossmann No No Doris No No No Solution completed, an Proposal gegangen; Kunde erhielt vorläufige Preisinfo mdl. 18.12.03 von G. Schröter; Angebot ist raus, Preisdiskussion mit ABB; ABB überlegt Reduzierung der Anforderungen. Zurückgezogn wg. Neuem Konzept No Baumann 1-19QO8 DE-IS Ocklenburg ABB IBM PL 32 Medium 22.01.2004 21.01.2004 10.12.2003 15.04.2004 Withdrawn by ABB see long description 10.11.2003 10.11.2003 Baumann / Schröter 10.12.2003 12.12.2003 70. Closed ABB03DE0057 CMS Lizenzserver BA PTMV 80. Canceled Hillmer 02.12.2003 11:21:37 ABB Germany IBM tblpDartState: Col1=RecordID Long Col2=State Text Width 50 Complete Data Set... RecordID State 1 10. Initiation 2 20. Solution Design 3 30. Proposal Development 4 40. Proposal Review 5 50. Implementation 6 60. Complete 7 70. Rejected 8 80. Canceled "MGFoster" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 That's simple enough: SELECT T.*, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL If this doesn't satisfy your needs, please post the complete definition of the tables (column names & their data types) and some sample data from both tables with column names over each data column. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmPyD4echKqOuFEgEQIPbACgt9ZmEr2zenlWPjPmvBZqe5 dL1JUAn3tN L0TzRl0bgH3a8iPZ2Kx7mU5H =mKnk -----END PGP SIGNATURE----- rogge wrote: The entire record needs to be returned where unrecognized values are shown as «Unrecognized State» with out replacing the data. for instance this is the data in the table Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil 31. Waiting for Feedback - Check what i want to return is... Title: State check oil 10. initiation replace oil filter 30. Waiting for Feedback replace oil «Unrecognized State» Thank you for your time and effort. "MGFoster" wrote: MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Run my query. You'll find that it does what you want. Read up on LEFT JOIN in the Access Help articles on Microsoft Jet SQL Reference Data Manipulation Language LEFT JOIN, RIGHT JOIN Operations. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On further thought you could run a query like the following. It finds all States in tblTitle that are not in tblState. Same thing I had before just w/o the Nz() function.: SELECT T.State As Title_State, "«Unrecognized State»" As State_State FROM tblTitle As T LEFT JOIN tblState As S ON T.State = S.State WHERE S.State IS NULL -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmCmMoechKqOuFEgEQLXdgCfTgCzi506w70m/r/fxgmc7OclDB0AnjGZ lXY8gjObMp07a3gsiEr+Mmzu =nwV8 -----END PGP SIGNATURE----- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
AVG Function in a Query | JohnL | Running & Setting Up Queries | 5 | December 18th, 2004 05:52 AM |
Return repeats info in "8s" | Joy Rose | Running & Setting Up Queries | 14 | October 13th, 2004 10:07 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |