Count Times Address has been Used 1

[uncleG]

Hi All,

I have an address list "tblOrgLocate" pk "LOID" (2066 records)that has many duplicate and unused addresses in it. I would like to count the number of times each entry [LOID] has been used in 10 different tables. Once identified I can remove those which have not been used.

Table.field Names which may contain matching LOID
tlbSo.DeLoc
tlbPO.DeLoc
tlbQuotes.DeLoc
tblRFQ.Deloc
etc
Thanks UncleG

 

[Golom]

Select LOID, Count(*) As [Number of Uses]

From 

(Select LOID From tblOrgLocate
UNION ALL
Select DeLoc From tblSo
UNION ALL
Select DeLoc From tblPO
[blue]etc.[/blue]
) As X

Group By LOID

 

[dinger2121]

run a summary query on each table that has the column which holds the address in it grouped, and a second column that is set to count on that same field. that will give you a count of how many times each address is used.
keep in mind that this will not take into account punctuation marks.

 

[uncleG]

Thank you Golum,
Right on the money.
Of 2066 address records 1249 were never used, Original developer had given them a duplicate this record button and they used it often.
This will help shorten up the Combo boxes a bit.
Thanks Again,
UncleG