Crystal Reports include two books

Why can't I do this to look for people who should be under 2 books.  "CARDIOPW"  is one book and I want to see if anyone else is in the other books.  I did test so I know I should get at least a test person but I get nothing.

{BOOK.ABBREV} = "DIOPW" and
{BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00)
trinisunsetAsked:
Who is Participating?
 
mlmccCommented:
Try this

You will have to build a view in the database or use a Crystal COMMAND for the source

SELECT FIELDS YOU NEED
FROM
BOOK  INNER JOIN APPT ON appropriate join
WHERE Book.Name  IN (SELECT Book1.Name FROM Book Book1 WHERE {BOOK.ABBREV} = "DIOPW" )
AND
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )

mlmcc
0
 
Éric MoreauSenior .Net ConsultantCommented:
it can't be a AND because the abbrev can't be DIOPW and something else at the same time!

can't you include DIOPW inside the IN clause?

or maybe something like (depending on how the date should be treated):
{BOOK.ABBREV} = "DIOPW" OR
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )
0
 
trinisunsetAuthor Commented:
The people have to be in the DIOPW book so don't think that the "OR" would work or including it in the "IN" list.  
They have to belong to the mandatory book of DIOPW and I would like to find out if any of them also belong to this list ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
 
I added in a test person so I know, at least, I should pull that test person up but I don't get anything.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Éric MoreauSenior .Net ConsultantCommented:
I don't get it. Somebody can't be it 2 list at the same time (and)! It has to be DIOPW OR something else!

can you show sample data and the expected results?
0
 
James0628Commented:
I'm guessing that the 2 books that you're talking about are in 2 different records.  For example, you've got one record in BOOK where ABBREV is "DIOPW", and a second record in BOOK where ABBREV is "DIO".

 If so, then, as emoreau said, a record selection simply isn't going to work, because it's only seeing one record at a time, so ABBREV can only have one value at a time.

 You could use a query like the one that mlmcc posted (that would be the new datasource for your report).  It uses a sub-query to get a list of the DIOPW books, and then checks to see if the current person is in that list.

 You could probably also do this in a report, in one way or another, but doing it in the db would probably be much more efficient.


 Also, FWIW, I'm a little confused by your first post.  You said ' "CARDIOPW"  is one book and I want to see if anyone else is in the other books', but then you have {BOOK.ABBREV} = "DIOPW".

 Are you looking for "CARDIOPW" or "DIOPW"?

 If "DIOPW" is supposed to match "CARDIOPW", you would need to use Like, instead of = (eg. {BOOK.ABBREV} like "*DIOPW").

 James

 PS: mlmcc, I guess this should be moved to the CR zone?
0
 
James0628Commented:
I got a "neglected question" email.  I almost missed the zone too.

 James
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you show sample data and the expected results?
0
 
trinisunsetAuthor Commented:
James, Sorry "CARDIOPW" is another name for  "DIOPW" sorry for the mix up.  I will try what mlmcc suggested and let you know if it works.  Thank you for the suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.