Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Formula Question 1

Status
Not open for further replies.

MikeVac

Programmer
Oct 27, 2004
35
CA
I am looking for a way to select records from 1 table only when a specific value exists.
For example. Table 1 and Table 2 are joined. I want to bring back values A,B,C,D from Table 2 only when there is an A value.
When A exists then bring back A,B,C,D (if B,C or D exist) If there is no A then don't bring back anything.
Thanks.
 
Are the values ABCD in the same field or are they different columns in table 2

If they are different columns then a simple equal join on table 1 to table 2 on the column containing A.

in select expert

table1.col1 = 'A'

Ian
 
I will explain with better details.
Table 1 and Table 2 are linked by id.
There can be 1 or multiple rows in table 2.
I need to bring back rows in table 2 where value A exists in column X.
If A exists I want to bring back rows where that column X contains
A and other rows that start with "A-*" (A-1, A-2, A-3, etc)
If there is no A in column X then don't bring back any rows.
Thank You
 
still not getting results I need.
To explain further - one idea I had was to have a new table created (3) based on a join between tables 1 and 2 where column X in table 2 = A
Then I could query that table for rows that = A or startswith A-. There are also values AB, AC, etc that I want to exclude.
Trying to figure a way to do this without creating another table.
Thanks
 
I thought tables 1 and 2 where related by a different ID field and you are just trying to filter results based on table 2 col x

in select expert you can use either

left({table2.x}, 2) = 'A-'

or

{table2.x} startswith 'A-'


Ian


 
Yes Tables 1 and 2 are linked on another field. I was thinking to have new table created to be the same as 2 but only have the rows where column x = A

I have to bring back rows where A must exist in column X and if it exists bring back rows that contain A and starts with A-. I don't want to bring back rows that contain AB, AC, etc.

If A does not exit in column X then don't bring back any rows.
 
What I should have said is have another table 1 created based on the matching id from table 2 where column x = A.
Thanks
 
Try in select expert

(left({table2.x}, 2) = 'A-'
or {table2.x} = 'A')

This will exclude AB, AC etc. Wrapped in () so you can add in other clauses if required.

Ian
 
It can only bring back rows containing A- if there is a row = A.
I want it to bring back 'A' and 'A-*' only if there is a row = 'A'
or only 'A' if no 'A-* exists.
Thanks
 
You will have to give examples of data in the two tables Showing data as it exist and how you want it to appear.

I have no idea what you are trying to do.

Ian
 
I will add an attachment showing more details - I may not be able to add it today.
Thanks
 
If I'm following this correctly, and assuminng you want to return groups of IDs that have "A" in table2, then I think you can create a formula {@hasA} like this:

if {table2.col} = "A" then 1

Then go into report->selection formula->GROUP and enter:

sum({@hasA},{table1.ID}) > 0

-LB

 
We do not have access to your C:\ drive. Did you try my suggestion?

-LB
 
Trying to upload attachment throught media fire.
I am trying to bring back rows in table 2 that = A.
If no rows contain A then nothing comes back, if 1 or more rows = A then bring back those rows and also bring back rows that start with A-
If I can upload this attachment it will be more clear.
Thanks
 
The first time I tried to upload file, I thought it would take it from my C drive and upload it. Now media fire is at 0% (file is 66K)
I did try your suggestion but getting error message -
"there must be a group that matches this field"

Thanks
 
I was assuming you wanted the records per the ID field that you were linking the tables on. If you only want records that start with A- if there is an A record present, then change the group selection formula to:

sum({@hasA},{table1.ID}) > 0 and
{table2.col} startswith "A"

You have to replace {table1.ID} is the field you are grouping on.

-LB

 
I have attached a file which should help explain further.
I am going to be grouping by table2.col.
I am using a couple fields from table1 and the rest will be from table2.
Please see attachment.
Thanks for you help
 
 http://www.mediafire.com/?pjyl7w65vv8u3lw
Okay, change the group selection formula to:

sum({@hasA},{table1.ID}) > 0 and
(
{table2.col} = "A" or
{table2.col} startswith "A-"
)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top