Counting Fields from Different Tables 1

[krispi]

Hi all - I have a composite database - all it contains are 3 linked tables from other databases. What I need to do is do a total count of duplicates form one particular field in each of the tables. Then display instances only where the total accross the tables is >5 So, for example,

If "John Smith" appears twice in Table1!Field7
and 4 times in Table2!Field3
and once in Table3!Field5

The the query or report should display

John Smith
Table1: 7
Table2: 3
Table3: 5
Total 15

Any ideas where to start? I thought about creating a table specially to import the different fields into one field ,having the user run a delete query and an append query each time to refresh the info in the table, then running a find duplicates query based on the table. Doesn't seem very elegant though...

Thanks in advance for any help!

 

[traingamer]

One way is to create four queries:

qFileOne:
Select [Name], Count[Name] as f1Count from file1 group by [Name]

Repeat for the other two files. Combine (not tested):

select qFileOne.[Name], qFileOne.[f1Count], qFileTwo.[f2Count], qFileThree.[f3Count], (qFileOne.[f1Count]   + qFileTwo.[f2Count] + qFileThree.[f3Count]) as TotalCount from qFileOne inner Join qFileTwo on qFileOne.[Name] = qFileTwo.[Name] inner Join qFileThree on qFileOne.[Name] = qFileThree.[Name] 
where (qFileOne.[f1Count]   + qFileTwo.[f2Count] + qFileThree.[f3Count]) > 15

(This does require that [Name] be present in qfileOne). There are other ways as well.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Or > 5

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[krispi]

Thanks for responding Greg. I can see the logic. Only trouble is - I don't just want to query one name, I want the results to show ALL the names that appear more than 5 times over the 3 tables!

 

[RoyVidar]

I think traingamer's suggestion would do that, here's a couple of other alternatives.

[tt]select m.fName, count(m.fName) from
(select f.fName from tblfirst f union all
select s.fName from tblsecond s union all
select t.fName from tblthird) as m
group by m.fName
having count(m.fName)>5;[/tt]

Should give the total count, or

[tt]select f.fName, "tblFirst", count(f.fName)
from tblfirst f
group by f.fName
union
select s.fName, "tblSecond", count(s.fName)
from tblsecond s
group by s.fName
union
select t.fName, "tblThird", count(t.fName)
from tblthird group by t.fName
order by 1,2[/tt]

Then create the total in the report, not sure how to get the >5 thingie on the last one, though. There is a query forum here, too forum701

Roy-Vidar

 

[krispi]

Thanks both for responding. I went with Roy's first suggestion in the end, and although I couldn't get the group by bit to work I used the basic Union Query (which I've never used before) then requeried it with a find dups query from the query builder with criteria >5. Works like a charm!

Thanks again, I've learned something today, which means getting up wasn't a complete waste of time...