A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query for 'confirmation'



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2005, 01:52 PM
rogge
external usenet poster
 
Posts: n/a
Default 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  
Old April 14th, 2005, 09:01 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----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  
Old April 15th, 2005, 09:17 AM
rogge
external usenet poster
 
Posts: n/a
Default

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  
Old April 15th, 2005, 07:42 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----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  
Old April 16th, 2005, 06:44 AM
MGFoster
external usenet poster
 
Posts: n/a
Default

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  
Old April 18th, 2005, 08:13 AM
rogge
external usenet poster
 
Posts: n/a
Default

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  
Old April 18th, 2005, 06:44 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----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  
Old April 19th, 2005, 10:28 AM
rogge
external usenet poster
 
Posts: n/a
Default

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  
Old April 19th, 2005, 03:26 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested in-line Query laura Running & Setting Up Queries 0 February 11th, 2005 01:17 AM
AVG Function in a Query JohnL Running & Setting Up Queries 5 December 18th, 2004 06: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


All times are GMT +1. The time now is 01:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.