4 tables, accounts, names, shares, cards

[mikefight]

Cr 11
sql data base

I have a 4 tables, accounts, names, shares, cards

I only want accounts without a certain card type, with a certain share, without name suffix I am getting the account that match the the account statements in the select, such as close date is null on account, but it is also pulling accounts that do not match the other criteria. If the name has a suffix I do not want it to pull the account at all. I am getting the account number with a blank name. I am getting accounts with cards in the not statement.



here is my select:
isnull({ACCOUNT.CLOSEDATE}) and
isnull({SAVINGS.CLOSEDATE}) and
{SAVINGS.TYPE} = 0 and
{SAVINGS.BALANCE} >= $5.00 and
not ({ACCOUNT.TYPE} in [1, 10, 11, 12, 13, 14, 16, 17, 21, 23, 24, 8, 9]) and
datediff("yyyy", {NAME.BIRTHDATE},currentdate) >=18 and
not ({CARD.TYPE} in [20,21,22,23,24,25,41]) and
{NAME.SUFFIX} = "" and

 

[Kendel]

Use Len({NAME.SUFFIX}) > 0 instead of {NAME.SUFFIX} =

 

[Kendel]

I'm sorry, I misunderstood the question.

 

[lbass]

Try:

(
isnull({name.suffix}) or
trim({name.suffix}) = ""
) and
isnull({ACCOUNT.CLOSEDATE}) and
isnull({SAVINGS.CLOSEDATE}) and
{SAVINGS.TYPE} = 0 and
{SAVINGS.BALANCE} >= $5.00 and
not ({ACCOUNT.TYPE} in [1, 10, 11, 12, 13, 14, 16, 17, 21, 23, 24, 8, 9]) and
datediff("yyyy", {NAME.BIRTHDATE},currentdate) >=18 and
not ({CARD.TYPE} in [20,21,22,23,24,25,41])

...but it appears that you have additional criteria that you didn't show, and I'm wondering if you have an "or" in there that you haven't set off in parens.

-LB

 

[mikefight]

I do not have any or statements. I left it off because that part is working and would just add confusion. The suffix part is working like it designed, I just have the design wrong.

It is removing the name record, i do not want it to show the account at all. I have the formula in the group to show the proper address. I just get the account number and then a blank address for accounts with a suffix. I want it to not show the account at all if the suffix is not blank.

Also it shows the account regard of the card.

 

[lbass]

Is this an issue where the account number appears multiple times sometimes with an address and sometimes without? Your post is confusing to me. Please remove the suffix criterion from your record seletion selection formula and show what you then get with some sample data. Then show us what you want to see.

-LB

 

[mikefight]

I have
account:number in the as the group.

The I have name:street in the group header.

account 100 123 main street
account 200
account 300 345 some street

 

[lbass]

I meant for you to show use detail level data, so we could see whether each account has both some records with null and some with not null street names. Please clarify which accounts you would want to see if the detail records looked like this:

account 100
account 100 123 main street
account 200
account 300 345 some street

Please also clarify whether your detail data might look like this with the account shown with and without the address.

-LB

 

[mikefight]

The detail data shows all the names that do not have a suffix and mutiple times

group: 100 123 main st
details:
100 123 main st
100 123 main st
100 456 some st
100 456 some st

if there is only a name with a suffix then I get
group: 100
details:
100
100

 

[lbass]

You didn't answer my question about what you would want to see based on my sample data.

I cannot follow what you are trying to do here--whether you want to show only those groups without a complete address, or only those with a complete address, or those where any record is missing the address.

I guess I'm unclear on what a suffix is, too. I don't think I can help unless you are more specific.

-LB

 

[mikefight]

Sorry about that I did not understand want you wanted.
out of your sample data i would want:

account 100 123 main street
account 300 345 some street

These are in the group section, there is nothing in the detail of my report. This is for a mailing list. So I imported in 4 tables. Linked them by account number.

If the name on the account has a suffix I do not want that account to show. This is not working, the name is blank but account number is still on report

If the account has one of the cards in the not statement I do not want it to show. This is not working.

If the account does not have a share type 0 with at least $5, I do not want it to show. This is not working

I want to exclude closed accounts. This part is working.

 

[lbass]

I think you need to put the relevant fields in the detail section of a blank report with no record selection criteria and observe how the data appears. This will allow you to see why removing records that meet your criteria is insufficient for removing the account as a whole--there must be some records that still meet your criteria for the specific account for it to continue to show up. It would be great if you could show a sample here.

You could then try applying your criteria and see what records remain--to identify what field you might have to add to your selection criteria.

How are you joining the tables? In other words are each of the three linked to one master table? Or are they linked in a chain? What kind of joins?

-LB

 

[mikefight]

The relevant field is the account. Since the account meets the criteria of not closed it shows.

The account is the master table linked to the other three. The tables are linked with inner joins.

When I add in details, then alter the select, it removes the details, like the cards, names, and shares. The account remains.

 

[lbass]

So with no groups, if you place the account field and the other fields in the detail section and then add back your record selection formula, only the account field appears? This makes no sense to me, but I guess you could try to use your record selection formula a second time to suppress the account field or the section as a whole.

-LB