Bob,
Here's what I think you need to get the report you want. Just plug the SQL for each query in the Access's SQL window and you should be good to go. Put the Union query in last since it depends on the other three. This was a quick & dirty, but it works with my data. The only trouble I had was with dates which included a range; i.e., "Died Between 1830 and 1840". I located the first date in the range, but I'll be darned if I can find the end date, either fixed or calculated. I even created a test database with one person whose birthdate was "Between 1830 and 1840". Oh well.
Once you put these queries in access, you can create reports bound to mmfBMD and filter as you see fit.
Margaret
Query mmfBirths:
SELECT tblIR.BirthSD, [Surname] & ", " & [GivenName] AS Expr2, "Born: " & Switch(Left$([BirthD],1)="0","",Left$([BirthD],1)="1","Abt ",Left$([BirthD],1)="2","Cir ",Left$([BirthD],1)="3","Bef ",Left$([BirthD],1)="4","Aft ",Left$([BirthD],1)="5","Bet ") & Switch(Mid$([BirthD],5,2)<>"00",Mid$([BirthD],5,2) & "/") & Switch(Mid$([BirthD],3,2)<>"00",Mid$([BirthD],3,2) & "/") & Mid$([BirthD],7,4) AS Expr1
FROM tblIR
WHERE (((tblIR.BirthSD)<>-99999999 And (tblIR.BirthSD)>1231))
ORDER BY tblIR.BirthSD;
Query mmfMarriages:
SELECT tblMR.MarSD, [HusbSurname] & ", " & [HusbGivenName] & " & " & [WifeSurname] & ", " & [WifeGivenName] AS Expr2, "Married: " & Switch(Left$([MarD],1)="0","",Left$([MarD],1)="1","Abt ",Left$([MarD],1)="2","Cir ",Left$([MarD],1)="3","Bef ",Left$([MarD],1)="4","Aft ",Left$([MarD],1)="5","Bet ") & Switch(Mid$([MarD],5,2)<>"00",Mid$([MarD],5,2) & "/") & Switch(Mid$([MarD],3,2)<>"00",Mid$([MarD],3,2) & "/") & Mid$([MarD],7,4) AS Expr1
FROM tblMR
WHERE (((tblMR.MarSD)<>-99999999 And (tblMR.MarSD)>1231))
ORDER BY tblMR.MarSD;
Query mmfDeaths:
SELECT tblIR.DeathSD, [Surname] & ", " & [GivenName] AS Expr2, "Died: " & Switch(Left$([DeathD],1)="0","",Left$([DeathD],1)="1","Abt ",Left$([DeathD],1)="2","Cir ",Left$([DeathD],1)="3","Bef ",Left$([DeathD],1)="4","Aft ",Left$([DeathD],1)="5","Bet ") & Switch(Mid$([DeathD],5,2)<>"00",Mid$([DeathD],5,2) & "/") & Switch(Mid$([DeathD],3,2)<>"00",Mid$([DeathD],3,2) & "/") & Mid$([DeathD],7,4) AS Expr1
FROM tblIR
WHERE (((tblIR.DeathSD)<>-99999999 And (tblIR.DeathSD)>1231))
ORDER BY tblIR.DeathSD;
Query mmfBMD:
Select [mmfBirths].BirthSD as [SortDate], [mmfBirths].Expr2 as [Individuals], [mmfBirths].Expr1 as [Event Date]
from [mmfBirths]
UNION Select [mmfDeaths].DeathSD as [SortDate], [mmfDeaths].Expr2 as [Individuals], [mmfDeaths].Expr1 as [EventDate]
from [mmfDeaths]
UNION Select [mmfMarriages].MarSD as [SortDate], [mmfMarriages].Expr2 as [Individuals], [mmfMarriages].Expr1 as [EventDate]
from [mmfMarriages]
ORDER BY [SortDate];
----- Original Message ----- From: "Bob Bashford" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, March 04, 2005 5:18 PM
Subject: Re: [LegacyUG] New report needed ... very easy to program ...
Valerie ...
Although I'm not sure what "3rd level technical support' is, your suggestion will not give me the report I want. It will produce a report with three contiguous lines for each person ... my request is for three seperate lines sorted by the date of the event.
1) The Name Report will only produce a report for the entire file (I believe). I asked for selection criteria as in other reports.
2) The Name List can produce the three lines I requested, but they will follow each other in the report and not be sorted by date as I asked. The report I want shows each event in the order it happened interspersed with intervening events from other people's records. The three lines for a person may be 20 or 30 or even 50 lines apart ... depending only on how densely populated the file is during that time period. (I also believe this suggestion would produce a line even if the date was blank ... which I don't want.)
3) The Name List (it seems) can only be sorted by RIN, by given name, or by surname.
I was in the computer business from 1967 until 1996 ... see an earlier post if you're interested in details ... and I did take the time to look at the possibilities ... both within Legacy and in other programs. That's why I asked for a NEW report.
Thank you for your advance apology ... my comment (which I hope isn't too biting) is that many help desks try to push users into accepting what the program can already do ... even if it doesn't meet the user's needs. They should be giving feedback on user suggestions and dissatisfactions to the the development staff.
Regards,
Bob
P.S If the Legacy programming staff is reading this exchange, I'd add one further request ... the ability to extract and print the birth, marriage, and death dates for a period of time ... say 01/01/1880 to 12/31/1910. That'd be even more helpful ... and would be super for sending out to the family, etc. If you'd like, I'd be happy to spec this report more fully. I still think it could be done in an afternoon.
At 07:57 AM 03/04/05 -0600, you wrote:To Bob and Everyone else:
After doing 3rd level technical support for almost 20 years, I still find
it amazing
how many people's frustrations come from them not taking time to read the
screen or the instructions! A lot of time and money are spent by software
companies
developing the help text and manuals. I apologize now if I offended anyone.
(quote from Legacy Help File deleted)
Valerie Laskowski [EMAIL PROTECTED]
Legacy User Group Etiquette guidelines can be found at: http://www.LegacyFamilyTree.com/Etiquette.asp
To find past messages, please go to our searchable archives at: http://www.mail-archive.com/legacyusergroup%40mail.millenniacorp.com/
To unsubscribe please visit: http://www.legacyfamilytree.com/LegacyLists.asp
-- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.6.0 - Release Date: 3/2/2005
Legacy User Group Etiquette guidelines can be found at: http://www.LegacyFamilyTree.com/Etiquette.asp
To find past messages, please go to our searchable archives at: http://www.mail-archive.com/legacyusergroup%40mail.millenniacorp.com/
To unsubscribe please visit: http://www.legacyfamilytree.com/LegacyLists.asp
