I unsuccessfully tried to upload some screen shot

What's your Query?

I have a report that shows each person and their life event but i only need: Birth, Death, Wedding.  You can tweak it to show the other event types.

SQL Code for query:

INSERT INTO tblLifeEventsPerson2 ( PersonT_FirstName, PersonT_LastName, LifeEventTypeID, Event, LifeEventDate, PersonT_1_FirstName, PersonT_1_LastName )
SELECT PersonT.FirstName, PersonT.LastName, LifeEventT.LifeEventTypeID, IIf([LifeEventT]![LifeEventTypeID]=1,"Birth",IIf([LifeEventT]![LifeEventTypeID]=2,"Death",IIf([LifeEventT]![LifeEventTypeID]=3,"Wed",IIf([LifeEventT]![LifeEventTypeID]=8,"Divorce","none")))) AS Event, LifeEventT.LifeEventDate, PersonT_1.FirstName, PersonT_1.LastName
FROM (LifeEventT LEFT JOIN PersonT ON LifeEventT.PersonID = PersonT.PersonID) LEFT JOIN PersonT AS PersonT_1 ON LifeEventT.Person2ID = PersonT_1.PersonID;

Alex Hey Alex I tried to upload some screen shots of the query and report but was unable. will try again.

Did you try pasting my SQL into the SQL view of query?

Bj

Do not use an "Insert Into...." Query... that will add more records.

You need to determine if the problem is in the Query or the Report.  If you run the query by itself, does it show all the records you expect?

-Raymond

Bj, if you're still having trouble after trying the suggestions here, feel free to post the SQL for your current query as text (not a screenshot) so we can take a closer look and offer more specific guidance.

Richard SELECT SpecialDateQ.*, DateDiff("yyyy",[AnniversaryDate],Date())+(DateSerial(Year(Date()),Month([AnniversaryDate]),Day([AnniversaryDate]))>Date()) AS YearsMarried, Format([AnniversaryDate],"mmm") AS DateFormat, Year([AnniversaryDate]) AS Y, Month([AnniversaryDate]) AS M, Day([AnniversaryDate]) AS D, DLookUp("Person2ID","LifeEventT","PersonID=" & [PersonID] & " AND " & "LifeEventTypeID=3") AS Spouse
FROM SpecialDateQ

Bj, I'm pretty sure the issue is that you're using DLookUp to pull the spouse. DLookUp can only ever return a single value, so if someone has multiple marriage records, it will just grab the first match it finds and ignore the rest. That's why you're only seeing first marriages.

Instead of looking up the spouse with DLookUp, the report should be based directly on the marriage records themselves so you get one row per marriage. That way second and third marriages will naturally show up.

Before changing the report, try running the underlying query by itself and make sure it's returning all the marriage records you expect. If the query only shows one per person, that's where the fix needs to happen.

Richard Thanks Richard

Thanks to all for your quick responses. Richard, it was a DLookUp in the query.
I changed the query to:
SELECT PersonT.PersonID, PersonT.FirstName, PersonT.MiddleName, PersonT.LastName, PersonT.NickName, PersonT.OtherLastName, PersonT.Gender, PersonT.SuffixID, LifeEventT.LifeEventDate AS AnniversaryDate, DateDiff("yyyy",[AnniversaryDate],Date())+(DateSerial(Year(Date()),Month([AnniversaryDate]),Day([AnniversaryDate]))>Date()) AS YearsMarried, Format([AnniversaryDate],"mmm") AS DateFormat, LifeEventT.Notes, Year([AnniversaryDate]) AS Y, Month([AnniversaryDate]) AS M, Day([AnniversaryDate]) AS D, [LastName] & ", " & [FirstName] & " " & [SuffixAbb] AS LF, SuffixT.SuffixAbb
FROM (PersonT INNER JOIN LifeEventT ON PersonT.PersonID = LifeEventT.PersonID) LEFT JOIN SuffixT ON PersonT.SuffixID = SuffixT.SuffixID
WHERE (((LifeEventT.LifeEventDate) Is Not Null) AND ((LifeEventT.LifeEventTypeID)=3))
ORDER BY PersonT.LastName, PersonT.FirstName;

I am working on incorporating DOD or Divorce dates to get an accurate [YearsMarried].