Querying from one table

[austinh]

I have a table with a list of different jobs and specializations. I want to make a report where it pulls all the people that have the same job but have different specifications. For example, everyone who is an artist and is either a painter, a sculptor, a photographer etc. Is there a formula that I can write to pull that? I seem to be having trouble retrieving that from the DB.

 

[synapsevampire]

Assuming jobs and specializations are two different fields in the table, try:

(
{table.job} = "artist"
)
and
(
{table.specialization} in ["painter", "sculptor", "photographer"]
)

The requirement here is vague though, and you may want to consider using parameters to select these, perhaps even hierarchical parameters, depending upon your Crystal version (CRXI required).

Rather than trying to describe requirements, you'll be better served to supply example data and the required output.

-k

 

[austinh]

what if they are in the same field? For example there is the field, ID, in the member table and that is where all the jobs AND specializations are. So using your example, I wrote the same thing you had written but instead of {table.specialization} I had {table.job} for both.

 

[synapsevampire]

I think that you should post example data and the expected output.

You can self join tables, but I'm not sure if you want a signle person and all of the jobs for that person, or you want a job listed and all of the people with that job, and then a list of all the other jobs they have.

Example data and expected output should clarify.

-k

 

[austinh]

Ill try and clarify. If I have a table called members and inside that table I have a field called ID. In that field is a list of jobs and specifications. Jobs such as artist, editor, critic etc. Specifications such as film, paint, photography.

I want to create a report that will list all the people that are artist but with different specifications. So I may have a column with names and a column with email addresses BUT not listing the job or specifications. For example it may list John Doe, Mary Smith, Joe Johnson, and Mark Jackson. All of these people ARE artists but they may have different specifications. John might be a painter, and Mary might be a photographer etc. Does that help?

 

[hilfy]

What does the data in the ID field look like? Are there multiple values in the field or just a single value? Please send us a sample of the actual data.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[austinh]

The data is the list of jobs and specifications. Like J1,J2,J3,J4,S1,S2,S3 etc. There is too much to list here.


I tried to do what "synapsevampire" did in the second post

(
{table.job} = "artist"
)
and
(
{table.specialization} in ["painter", "sculptor", "photographer"]
)


but the jobs and specializations are in the same field and when I tried it it didnt work. Can you query from the same fields? For example can you write:

(
{table.ID} = "artist"
)
and
(
{table.ID} in ["painter", "sculptor", "photographer"]
)