level filter for data item

[peewan]

Table 1 - Main Code contain [Main Code] (CPT and Diag): 85200, 87455 - Lab;  Z2345, Z090 - Office
Table 2- Members with Visits Code have 2 items [CPT] and [Diag]

I join 2 table.

i would like THE member base on CPT first then Diag.

Example: FIRST [table1][Main Code] = [Table2][CPT] then count,
              SECOND table1][Main Code] <> [Table2][CPT] then looking for Diag.



My member has been show up twice:

Member1:  Should be Line with 'Lab' only
85200      Z2345    Lab
85200      Z2345    Office

I try to add maximum([] for [member]), but it stills not work.
Any helps?
Thank you

[bus_pass_man]

Yeah well, let's start with the perential favourites:

What do you mean by 'it's not working right' and 'stills (sic) not work'?

Do you get this with your weird join?   
85200      Z2345    Lab
85200      Z2345    Office

Does the fact you get multiple rows for each member tell you....something?

What is the nature of the data? 

What is pattern of the data?

What is the data?

What is the relationship between the tables -- not just what's there in the data base, but what's the relationship between the objects in the tables in real life?

Look at the tables.  That should help you.   

Why are you doing this in reporting and not in a modelling tool?

You will need to do a better job of showing the data that you are showing us in a form which tells us slightly more about the nature of the data.

Describe the business problem that you are trying solve. 

[peewan]

Yeah well, let's start with the perential favourites:

What do you mean by 'it's not working right' and 'stills (sic) not work'?

Do you get this with your weird join?   
85200      Z2345    Lab
85200      Z2345    Office

Does the fact you get multiple rows for each member tell you....something?

What is the nature of the data? 

What is pattern of the data?

What is the data?

What is the relationship between the tables -- not just what's there in the data base, but what's the relationship between the objects in the tables in real life?

Look at the tables.  That should help you.   

Why are you doing this in reporting and not in a modelling tool?

You will need to do a better job of showing the data that you are showing us in a form which tells us slightly more about the nature of the data.

Describe the business problem that you are trying solve.

Sorry for confusing :D,

Table 1: content all general Code - tell you it in Lab, office, Dental....
Table 2: Member details, base on visits.

Two tables join on

Code Select Expand



CPT code: 85200, 87455 - Lab

Diag code:  Z2345, Z090 - Office

I would like to get line of THE member base on CPT first then Diag.

FIRST [table1][Main Code] = [Table2][CPT],
SECOND [table1][Main Code] <> [Table2][CPT] then looking for Diag.

Report showed Member1:
85200      Z2345    Lab
85200      Z2345    Office
87455      z29        Lab
23456      Z090      Office

Should be:
85200      Z2345    Office           (this one have CPT: 85200 in Lab, Diag: Z2345 in Office, Should return Lab only)
87455      z29        Lab               
23456      Z090      Office


[bus_pass_man]

You haven't done much to add additional information.   

Among other things I still don't know where your columns which you are using for your report come from, I don't know much about the data in these columns.   

For example,  you've got 3 columns in your report that you've displayed.

Report showed Member1:
85200      Z2345    Lab
85200      Z2345    Office
87455      z29        Lab
23456      Z090      Office

but I don't know the column names or their source tables.   I'm guessing that the 85200 column is members but I shouldn't have to guess as I'm the one trying to do you a favour and you're the supplicant. 

I should not need to guess what this

(CPT and Diag): 85200, 87455 - Lab;  Z2345, Z090 - Office

actually means.   Show the data in the form of a table.

I was initially guessing that the table looks like this
cpt         diag
85200   87455 - Lab
Z2345   Z090 - Office

This can't be the cast as you have Z2345 in a separate column in your report snippet.

Report showed Member1:
85200      Z2345    Lab
85200      Z2345    Office
87455      z29        Lab
23456      Z090      Office

Go back to my response and try to answer the questions.  The key one would be "Does the fact you get multiple rows for each member tell you....something?"

Table 1: content all general Code - tell you it in Lab, office, Dental....

That's not quite as clear as one would like unfortunately.

FIRST [table1][Main Code] = [Table2][CPT],
SECOND [table1][Main Code] <> [Table2][CPT] then looking for Diag.

Nor this.  Is this your join expression? 

In addition,

then looking for Diag

is especially not as clear as one would like.