On Fri, 04 Jun 2010 16:58:49 -0700, Wm Voss <bellafortuni@...>
wrote:

>I am feeling altogether dim today -- I surely appreciate your assistance.

No problem.

You need to enter "=PrettyDate(<cellref>)"

So if you are in cell F2, enter "=PrettyDate(D2)".
If you are in cell F3, enter "=PrettyDate(D3)".
etc.

I know that you are saying that you don't want to have to enter 1700+
lines like this. But if you are in F2, hit Crtl-Ins. Then move down one
row and hit Shft-Ins. Excel will automatically adjust the cellref.

You are probably still saying to yourself that this will still be a lot
of keystrokes. Well, you can make it easy by recording a macro that
copies and pastes 10 cells at a time.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
     ActiveCell.Range("A1:A10").Select
     Selection.Copy
     ActiveCell.Offset(10, 0).Range("A1").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
End Sub

Just manually do the 1st 10 cells. Then position yourself in the 1st
cell and hit Ctrl-q. It will copy the first 10 cells to the next 10
cells bellow. All you need to do is hit Ctrl-q 170+ times. Should take
about a minute.

Are we having fun yet?

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Fri, 04 Jun 2010 20:34:07 -0400, Dennis Kowallek <kowallek@...>
wrote:

>Sub Macro1()
>'
>' Macro1 Macro
>'
>' Keyboard Shortcut: Ctrl+z
>'
>    ActiveCell.Range("A1:A10").Select
>    Selection.Copy
>    ActiveCell.Offset(10, 0).Range("A1").Select
>    ActiveSheet.Paste
>    Application.CutCopyMode = False
>End Sub
>
>Just manually do the 1st 10 cells. Then position yourself in the 1st
>cell and hit Ctrl-q. It will copy the first 10 cells to the next 10
>cells bellow. All you need to do is hit Ctrl-q 170+ times. Should take
>about a minute.

Make that Ctrl-z.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

From the LUG...

>It would be great to have a built-in search which could find all marriage
>records where there is a blank spouse and there are no children and no data is
>in the marriage record.  These are usually marriage records that were added by
>accident and the user will likely want to remove them.

I have added the following:

	 SelectQueries.csv - "Marriages - Find Unknown Marriages w/o Children"

	 UpdateQueries.csv - "Marriages - Tag Unknown Marriages w/o Children"

To add these new queries to the context menus for Run Raw SQL and View
Legacy Tables, go to:

     Options|Refresh SelectQueries.csv

     Options|Refresh UpdateQueries.csv

They found about a dozen "phantom" marriages in my database.

Enjoy.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 12:34:04 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>Just tried the new scripts...worked great...found 225 phantom marriages in my
"clean" database.  Now it will be much cleaner.

Just to clarify. I would describe these as "potential phantom
marriages". You should look at each one carefully before deleting. For
example, I found one with a citation attached, so it really wasn't
"phantom".

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 14:34:35 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>I know the logic would be more complex, but could you tweak it detect true
"blank" marriage records...no citations, no multimedia, etc.?

That could get very complex (not a tweak) for a single SQL statement.

I'll leave that as an exercise for the user. ;-)

NOTE: I think if you use the UPDATE query, then cycle thru the tagged
marriages in Legacy, the ones with citations, etc. should be obvious
(the associated icon on the Family View will appear in color).

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 16:36:54 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>Now I am finding some that have a marriage date and/or place.  That should be
an easy tweak to the query or tagging update.  What do you think?

Piece of cake...

UPDATE tblMR LEFT JOIN tblCR ON tblMR.IDMR = tblCR.IDMR SET
tblMR.Tag{TagNumber} = 1 WHERE ((tblMR.IDMR<>0) AND (tblMR.IDIRHusb=0)
AND (tblCR.IDMR Is Null) AND (tblMR.IDLRMar=1) AND (tblMR.MarD='')) OR
((tblMR.IDMR<>0) AND (tblCR.IDMR Is Null) AND (tblMR.IDIRWife=0) AND
(tblMR.IDLRMar=1) AND (tblMR.MarD=''))

Give it a test drive.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 20:17:34 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>The "not duplicates" table is not cleaned up after a deletion or merge and so
it develops entries that are no longer linked.

What are the consequences of not cleaning up this table?

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 20:17:34 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>One other thing I have wondered is whether Legacy renumbers all the tables when
it does a RIN or MRIN renumbering such as this not duplicates table.  If they do
not, it could be a big problem waiting to happen.

Legacy V6 appears to renumber the RINs in this table.

Legacy does change the foreign keys in other tables when renumbering
RINs/MRINs. If it didn't the database would get hosed in no time. That
is why RIN/MRIN renumbering is no simple task. I have been asked several
times to create an LTool that renumbers RINs in a specific order (for
example, starting person = 1, father = 2, mother = 3, etc.). I have
always declined ... and will continue to decline. The chance of hosing
up the database would be too great.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Wed, 9 Jun 2010 09:47:01 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>have always wondered if Access has those capabilities or if it is just the
result of what the programmer has been able to do.

In Access (Jet) you need to define the "Relationships" between the
tables. As far as I know, Legacy does not define any. It would be
difficult (probably impossible) to do because many of the tables rely on
a "type" column that tells the program which table the foreign key
refers to. For example, tblBR uses IDType to identify if IDIR is in
tblIR, tblER, tblMR, etc. So my guess is that this is entirely hand
coded.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Tue, 8 Jun 2010 20:17:34 -0700 (PDT), Ron Taylor <doit4ron@...>
wrote:

>One other thing I have wondered is whether Legacy renumbers all the tables when
it does a RIN or MRIN renumbering such as this not duplicates table.  If they do
not, it could be a big problem waiting to happen.

I did find a bug. Sherry ... are you out there?

When you have "Reuse abandoned RINs" turned on, if you delete a person
who is listed in tblDM, and then add a person, the newly added person
will get the abandoned RIN. And that new individual is listed in tblDM
when he/she shouldn't be!

At least this is true in V6.

If Sherry doesn't reply, I will send it to her privately as a potential
bug for V7.

NOTE: I always keep "Reuse abandoned RINs" turned off. Ever since my
earliest days with Legacy, this feature seems to have caused problems.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

----- Original Message -----
From: Dennis Kowallek
To: ltools@yahoogroups.com
Sent: 09 June 2010 20:25
Subject: Re: [ltools] unlinked not duplicates



NOTE: I always keep "Reuse abandoned RINs" turned off. Ever since my
earliest days with Legacy, this feature seems to have caused problems.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

I agree, Dennis, I have done the same since very shortly after I started
using Legacy. Whilst I do have other reasons, I also suspected it was
causing me problems which didn't seem to make sense.

Ron Ferguson
_____________________________________________________

*New* Tutorial: Add Location Pins to Google Earth
http://www.fergys.co.uk
Includes the family tree for Alan J Grimshaw
And the Fergusons of N.W. England
____________________________________________________

From an offlist email...

>This morning I've been tearing my hair out as I needed to change the
>print flags on source detail text and comments for just one source.
>Fortunately the source only applied to 10 people. Is there a way of
>automating this for a tagged group of sources? In Legacy itself it's
>all or nothing in changing the print flags.

Sure. To turn the 2 print flags on...

UPDATE tblSR INNER JOIN tblSX ON tblSR.IDSR = tblSX.IDSR SET
tblSX.SrcPrintText = 1, tblSX.SrcPrintNote = 1
WHERE (((tblSR.SrcTag)=1))

To turn the 2 print flags off...

UPDATE tblSR INNER JOIN tblSX ON tblSR.IDSR = tblSX.IDSR SET
tblSX.SrcPrintText = 0, tblSX.SrcPrintNote = 0
WHERE (((tblSR.SrcTag)=1))

Run this in "Run Raw SQL".

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

Here's a few queries that will list individuals who don't have a death
or burial date and do have a marriage date before a specified year...

For men:

SELECT tblIR.IDIR AS RIN, tblIR.DeathD, tblIR.BuriedD, tblMR.MarD,
Mid([tblMR].[MarD],7,4) AS MarYear, tblMR.MarSD
FROM tblIR INNER JOIN tblMR ON tblIR.IDIR = tblMR.IDIRHusb
WHERE ((tblIR.DeathD='') AND (tblIR.BuriedD='') AND (tblMR.MarD Like
'0*') AND (Mid([tblMR].[MarD],7,4)<>'0000') AND
(tblMR.MarSD<{befYear}0000)) ORDER BY tblMR.MarSD

For women:

SELECT tblIR.IDIR AS RIN, tblIR.DeathD, tblIR.BuriedD, tblMR.MarD,
Mid([tblMR].[MarD],7,4) AS MarYear, tblMR.MarSD
FROM tblIR INNER JOIN tblMR ON tblIR.IDIR = tblMR.IDIRWife
WHERE ((tblIR.DeathD='') AND (tblIR.BuriedD='') AND (tblMR.MarD Like
'0*') AND (Mid([tblMR].[MarD],7,4)<>'0000') AND
(tblMR.MarSD<{befYear}0000)) ORDER BY tblMR.MarSD

I wrote these queries because I like to add a death date to individuals
if all I have to go with is a marriage date.

For example, if I have Joe Blow without a death date who married in
1898, I will add "aft 1898" as a death date. These queries help me
identify individuals I may have missed.

If you want to turn these into UPDATE queries that will tag the
individuals in question...

For men:

UPDATE tblIR INNER JOIN tblMR ON tblIR.IDIR = tblMR.IDIRHusb SET
tblIR.Tag{TagNo}=1 WHERE ((tblIR.DeathD='') AND (tblIR.BuriedD='') AND
(tblMR.MarD Like '0*') AND (Mid([tblMR].[MarD],7,4)<>'0000') AND
(tblMR.MarSD<{befYear}0000))

For women:

UPDATE tblIR INNER JOIN tblMR ON tblIR.IDIR = tblMR.IDIRWife SET
tblIR.Tag{TagNo}=1 WHERE ((tblIR.DeathD='') AND (tblIR.BuriedD='') AND
(tblMR.MarD Like '0*') AND (Mid([tblMR].[MarD],7,4)<>'0000') AND
(tblMR.MarSD<{befYear}0000))

You may be able to do something similar within Legacy. I haven't tried.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

>I was looking for a way to print out the to-do's by surname. If it can
>be done, let me know, else a LTool Suggestion Feature ?

Try this in View Legacy Tables...

SELECT tblIR.IDIR AS RIN, tblIR.Surname, tblIR.GivenName,
tblTD.ToDoName, tblTD.Desc FROM tblIR INNER JOIN tblTD ON tblIR.IDIR =
tblTD.IDIR WHERE tblIR.IDIR<>0 ORDER BY tblIR.Surname

You can add any additional tblTD columns as desired.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

Can I use ltools to strip out «tab» from events eg. when I've copied census info
from ancestry.com?

Thanks

:)

margaret

On Wed, 14 Jul 2010 06:39:59 -0000, "mturner92" <mgrogan92@...>
wrote:

>Can I use ltools to strip out «tab» from events eg. when I've copied census
info from ancestry.com?

in Run Raw SQL, something like...

UPDATE tblER SET tblER.[Desc] =
Left(tblER.[Desc],InStr(1,tblER.[Desc],"«tab»")-1) & " " &
Mid(tblER.[Desc],InStr(1,tblER.[Desc],"«tab»")+5)
WHERE (((tblER.[Desc]) Like "*«tab»*"))

... will replace one "«tab»" with a space in the Event Notes field. The
Event Notes field is called Desc in the database.

You will need to keep running it until it returns "0 rows affected",
because for each row processed it replaces only one occurrence of
"«tab»". So if you have an Event Note with 10 "«tab»"s, it won't remove
them all until the 10th pass. Also keep in mind that you may end up with
extra spaces where you don't want them.

Make a backup before trying this.

Be careful!

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

On Wed, 14 Jul 2010 06:39:59 -0000, "mturner92" <mgrogan92@...>
wrote:

>Can I use ltools to strip out «tab» from events eg. when I've copied census
info from ancestry.com?

And you should be able to do this in Legacy with Search & Replace.

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

Ah, thanks for so long in Legacy you could not use control characters, that I
forget the ovious.

Thanks

:)

Margaret

--- In ltools@yahoogroups.com, Dennis Kowallek <kowallek@...> wrote:
>
> On Wed, 14 Jul 2010 06:39:59 -0000, "mturner92" <mgrogan92@...>
> wrote:
>
> >Can I use ltools to strip out «tab» from events eg. when I've copied census
info from ancestry.com?
>
> And you should be able to do this in Legacy with Search & Replace.
>
> --
>
> Dennis Kowallek (LTools)
> dennis@...
> http://zippersoftware.com/ltools/index.htm
> http://groups.yahoo.com/group/ltools
>

This topic is moved over from LUG.

The background is that pictures linked to Source Details do not show up in
Legacy reports or web pages, but pictures linked to Events do. A work-around
would be to change the picture record pointer from the Source Detail to the
Event that the source detail supports.

Dennis was just about to share some details on how the update query might look
when he suggested we move the topic over to this forum.

Russ McGillivray

On Fri, 23 Jul 2010 21:35:23 -0000, "Russ McGillivray"
<russ.mcgillivray@...> wrote:

>The background is that pictures linked to Source Details do not show up in
Legacy reports or web pages, but pictures linked to Events do. A work-around
would be to change the picture record pointer from the Source Detail to the
Event that the source detail supports.
>
>Dennis was just about to share some details on how the update query might look
when he suggested we move the topic over to this forum.

Here are some random thoughts:

tblBR (multimedia) contains a column called IDIR. It is a pointer to a
row in another table. The name IDIR is misleading because it contains
more than just a pointer to an individual (tblIR).

tblBR.IDType essentially tells you what table IDIR points to:

	 0-4 point to tblIR (the 5 values point to specific fields in tblIR)
	 20  points to tblMR (marriage)
	 30  points to tblER (event)
	 40  points to tblSR (master source)
	 41  points to tblSX (source detail)
	 70  points to tblAR (address)
	 71  points to tblLR (location)

tblSX.Type value tells you which table tblSX.IDIME points to. They are
described here...

	 http://zippersoftware.com/ltools/help/v1/tblSX_Type.htm

***

So, to replace an image that points to a source detail record that
points to an individual event record ...

UPDATE (tblBR INNER JOIN tblSX ON tblBR.IDIR = tblSX.IDSX) INNER JOIN
tblER ON tblSX.IDIME = tblER.IDER SET tblBR.IDIR = [tblER].[IDER],
tblBR.IDType = 30
WHERE ((tblBR.IDType=41) AND (tblSX.Type=30))

I just performed a cursory test on this so it is not guaranteed to be
bulletproof. It REPLACES the pointer to tblSX in tblBR with a pointer to
tblER (with appropriate change to IDType). If you wanted to INSERT a new
pointer to tblER instead, you would have to create an SQL INSERT query.
This would be much more complicated. An INSERT query would have to take
into account the PicOrder column amongst other things. This is why I
said a REPLACE would be much easier.

Keep in mind that the above UPDATE query only covers one of the 26
values that tblSX.Type can take on. So you would have to write 25 more
(with appropriate changes) to cover the rest.

If you want me to create an LTool to do all this, I am for hire. ;-)

Good luck!

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools

Dennis,
Thanks for this. I did not appreciate the subleties caused by the different
tables that IDIR points to.

I am only using 6 SX types so that makes the problem smaller!

I don't want to give up the idea of doing an append query yet.

Are you positive I need the reference to tblER in your example? In the case of
tblSX.Type = 30 it seemed to me that I would update tblBR.IDIR with tblSX.IDIME.
It seemed to work when I changed one record by hand.

Russ

--- In ltools@yahoogroups.com, Dennis Kowallek <kowallek@...> wrote:
>
> On Fri, 23 Jul 2010 21:35:23 -0000, "Russ McGillivray"
> <russ.mcgillivray@...> wrote:
>
> >The background is that pictures linked to Source Details do not show up in
Legacy reports or web pages, but pictures linked to Events do. A work-around
would be to change the picture record pointer from the Source Detail to the
Event that the source detail supports.
> >
> >Dennis was just about to share some details on how the update query might
look when he suggested we move the topic over to this forum.
>
> Here are some random thoughts:
>
> tblBR (multimedia) contains a column called IDIR. It is a pointer to a
> row in another table. The name IDIR is misleading because it contains
> more than just a pointer to an individual (tblIR).
>
> tblBR.IDType essentially tells you what table IDIR points to:
>
>  0-4 point to tblIR (the 5 values point to specific fields in tblIR)
>  20  points to tblMR (marriage)
>  30  points to tblER (event)
>  40  points to tblSR (master source)
>  41  points to tblSX (source detail)
>  70  points to tblAR (address)
>  71  points to tblLR (location)
>
> tblSX.Type value tells you which table tblSX.IDIME points to. They are
> described here...
>
>  http://zippersoftware.com/ltools/help/v1/tblSX_Type.htm
>
> ***
>
> So, to replace an image that points to a source detail record that
> points to an individual event record ...
>
> UPDATE (tblBR INNER JOIN tblSX ON tblBR.IDIR = tblSX.IDSX) INNER JOIN
> tblER ON tblSX.IDIME = tblER.IDER SET tblBR.IDIR = [tblER].[IDER],
> tblBR.IDType = 30
> WHERE ((tblBR.IDType=41) AND (tblSX.Type=30))
>
> I just performed a cursory test on this so it is not guaranteed to be
> bulletproof. It REPLACES the pointer to tblSX in tblBR with a pointer to
> tblER (with appropriate change to IDType). If you wanted to INSERT a new
> pointer to tblER instead, you would have to create an SQL INSERT query.
> This would be much more complicated. An INSERT query would have to take
> into account the PicOrder column amongst other things. This is why I
> said a REPLACE would be much easier.
>
> Keep in mind that the above UPDATE query only covers one of the 26
> values that tblSX.Type can take on. So you would have to write 25 more
> (with appropriate changes) to cover the rest.
>
> If you want me to create an LTool to do all this, I am for hire. ;-)
>
> Good luck!
>
> --
>
> Dennis Kowallek (LTools)
> dennis@...
> http://zippersoftware.com/ltools/index.htm
> http://groups.yahoo.com/group/ltools
>

On Sat, 24 Jul 2010 01:06:29 -0000, "Russ McGillivray"
<russ.mcgillivray@...> wrote:

>Are you positive I need the reference to tblER in your example? In the case of
tblSX.Type = 30 it seemed to me that I would update tblBR.IDIR with tblSX.IDIME.
It seemed to work when I changed one record by hand.

You are correct, you can write the query that way also since tblSX.IDIME
= tblER.IDER in that particular case. Saves you a join on tblER but I
don't think you will notice much of a performance difference.

Good luck!

--

Dennis Kowallek (LTools)
dennis@...
http://zippersoftware.com/ltools/index.htm
http://groups.yahoo.com/group/ltools