Micosoft Query Help

[austin22]

Hello, I am new at this, so bare with me. I am trying to retrieve data from my Microsoft Query set up. I have the following SQL statement:

SELECT Case_party_PHYSICAL_TABLE.JEboA, Case_party_PHYSICAL_TABLE.PARTY_TYPE_CODE, Case_party_PHYSICAL_TABLE.CP_NAME_LAST_BUSINESS, Case_party_PHYSICAL_TABLE.CP_NAME_FIRST, Case_party_PHYSICAL_TABLE.CP_NAME_MIDDLE, ((CASE_PARTY_ACTION_PHYT.CTmsA='AD') AND (Case_party_PHYSICAL_TABLE.PARTY_TYPE_CODE='DEF') AND 
ORDER BY Case_party_PHYSICAL_TABLE.CP_NAME_LAST_BUSINESS

What I want to do is retrieve cases that have a case party action of 'AD' and if not 'AD' then 'OI'...not both on the same case. How do I create my criteria to help retrieve the data that I am requesting? Also, I do not want all information that comes along with the case...I just want to see only the cases that have the case party action of 'AD' or 'OI' and that is all on the case...no further info.

I hope this makes sense. I hope that I am in the right forum for my question. Any suggestions will help.

Thanks!

 

[austin22]

Can someone help me with this? Your help will kindly be appreciated.

I started Microsoft Excel and clicked the 'Data' to import external data or start a new query. I chose the tables and fields I want to use from the database and saved the query and returned the data in Excel. Here are the tables:

SELECT Case_party_PHYSICAL_TABLE.JEboA, Case_party_PHYSICAL_TABLE.PARTY_TYPE_CODE, Case_party_PHYSICAL_TABLE.CP_NAME_LAST_BUSINESS, Case_party_PHYSICAL_TABLE.CP_NAME_FIRST, Case_party_PHYSICAL_TABLE.CP_NAME_MIDDLE, ((CASE_PARTY_ACTION_PHYT.CTmsA='AD') AND (Case_party_PHYSICAL_TABLE.PARTY_TYPE_CODE='DEF') AND 
ORDER BY Case_party_PHYSICAL_TABLE.CP_NAME_LAST_BUSINESS

Here is a sample of what I retrieved in Excel:

Definition of Fields:
JEboA [i](this is a case number field)[/i]
Name Last Business [i](last name of defendant)[/i]
Name First [i](first name of defendant)[/i]
CTmsA [i](case action type)[/i]

[i]These are test cases and in this case the defendant has 3 cases[/i]

CaseNumber  LastName   FirstName  CaseActionType
12342        Doe       Jane          AD
12343        Doe       Jane          AD

My dilemma is this:
Case number 12341 is missing because its action type is not on the case as follows:

CaseNumber   LastName  FirstName  CaseActionType
12341        Doe       Jane          OI

I want to group by case number and then say if case number ???? does not have actiontype 'AD' then 'OI'. Also I want to limit how many line items are retrieved. Example:

CaseNumber  LastName   FirstName  CaseActionType
12342        Doe       Jane          AD
12343        Doe       Jane          AD

789          Doe       Jane          AD
789          Doe       Jane          AD
789          Doe       Jane          AD
789          Doe       Jane          AD

I want to retrieve cases >4 line items. I guess I need to know how to 'group' and 'have'.

Any help, please.

 

[ca8msm]

I'd start by reading the help fies on group by and where clauses. Once you've made an attempt at integrating them into your query, if you then can't get the right results post back here with what you tried.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]