blonde to write query or design fault

Dear All,

Im wondering if its the design that needs to be changed or I simply cant put this together.

I have 3 tables.

1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)

Codes represent different skills of people, example the sort of job functions theyve held in their employment. Like:

t-CEO,
t-CFO
t-Founder
etc.

people, clearly holds data about people.

CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO

What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.

However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.

I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."

Is that wrong? No person is t-CEO AND t-CFO in your example.

Please tell us what you want the result to be.|||Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:

Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!

Blobs not displayed when printed programatically

Hey folks,

I have a report that I have created. It contains some graphical BLOB fields that are in a database. These blobs were simply dragged and dropped onto the report.

When I preview the report in Crystal Reports the BLOB fields are displayed. When I print the report programatically in VB .net the BLOBs are blank...

Any help would be much apreciated.
ThanksWhat type of image did you use?
Goto support section of this site and see if you find answer
www.BusinessObjects.com