Getting a list of Anniversaries

[Ocean West]

In my People table I have these fields:

 

First Name, Last Name, Anniversary, and ID

 

In addition I have aDD and aMM and aYY which is 3 fields that split up the anniversary as we don't always

know the year, but we at least have the month/day.

 

From my interface table I have a global field that i use to key in "theMonth" such as 2 for February.

 

My goal is to get the following list, day / name / ID

 

3  John & Jane Doe     102

3  Jack & Jill Smith      92

13 Terry & Dave Carlson  145

 

The tricky part here is each record is a individual person the only thing that tie them together is their ID and anniversary date

(in this case the ID is the parent accountID not their individual ID )

 

The above lists represents SIX individual records.

 

Any suggestions?

 

[dansmith65]

Is there any reason you have to use SQL for this? I have been finding that using complex SQL statements are generally slower than FileMaker native methods.

Is this what the data looks like? I don't understand what the Anniversary field is - is it a calculated field based on aDD, aMM, and aYY?

aDD first last accountID individualID

--- ------ -------- ---------- -------------

3 John Doe 102 1

3 Jane Doe 102 2

3 Jack Smith 92 3

3 Jill Smith 92 4

13 Terry Carlson 145 5

13 Dave Carlson 145 6

Edited February 3, 2013 by dansmith65

[Ocean West]

Dan yes essentially that is my basic list - the Anniversary is just a flag if they are to be included in the set. In this example all are in the set - in some

instances some couples wish not to receive an anniversary card - but we still have the data.

 

i was able to do this when searching for birthdays but since they are "individual" and anniversaries are two people - trying to group them by

account ID and only showing one Surname for the couple. (in rare cases they have different last names but I can manually deal with these )

 

currently i am doing this thru a relationship but it's a bit cumbersome

 

account ---< people >----spouse  ( acctID=  aMM=  aDD=  ID≠ )   

 

This relationship from people table determines the spouse. 

In the people table i have a calculation that populates as:

aDD & Char (9) & FirstName & " " &  spouse::FirstName & " " LastName & Char (9) & acctID

[dansmith65]

I think you can basically re-create the method you are currently using in SQL, but I bet the SQL version will be slower. Here is my stab at it...   I haven't used Group By before, so I'm not very confident that it will get you what you want.

 

SELECT p.aDD, p.FirstName, spouse.FirstName, p.LastName, p.acctID
FROM people AS p
INNER JOIN people AS spouse
ON p.acctID = spouse.acctID
AND p.aMM = spouse.aMM
AND p.aDD = spouse.aDD
AND p.ID <> spouse.ID
WHERE aMM = global::theMonth
GROUP BY p.acctID