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
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.
Quote from: bus_pass_man on 05 Mar 2020 03:42:17 PM
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
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
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.
QuoteReport 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
Quote(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 diag85200 87455 - Lab
Z2345 Z090 - Office
This can't be the cast as you have Z2345 in a separate column in your report snippet.
QuoteReport 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?"
QuoteTable 1: content all general Code - tell you it in Lab, office, Dental....
That's not quite as clear as one would like unfortunately.
QuoteFIRST [table1][Main Code] = [Table2][CPT],
SECOND [table1][Main Code] <> [Table2][CPT] then looking for Diag.
Nor this. Is this your join expression?
In addition,
Quotethen looking for Diag
is especially not as clear as one would like.