SQL in MS Access DB

Spuds

Spuds

Spuds

Associate
Joined
7 Jan 2007
Posts
640
Location
Cambridge
Its been a long time since I last used SQL and I'm tearing my hair out trying to figure out how to do this so any help would be appreciated.

I have two tables, Updates and Alerts, which both contain a field called Site (which just contains the address of a web site and will contain duplicate values in both tables). I need to output three columns, the address of every site in both tables, the number of times it appears in the Updates table and the number of times it appears in the Alerts table.

So if I had the following

Updates
---------------
Site1
Site1
Site1
Site2
Site4
Site5

Alerts
--------------
Site1
Site2
Site2
Site3
Site4
Site4

The query would output

Site | Updates | Alerts
----------------------------------
Site1 | 3 | 1
Site2 | 1 | 2
Site3 | 0 | 1
Site4 | 1 | 2
Site5 | 1 | 0

Any help would be very much appreciated
 
Code: 
SELECT DISTINCT Updates.Site, COUNT(Updates.Site),COUNT(Alerts.Site)
FROM Updates
INNER JOIN ALERTS
ON Updates.Site=Alerts.Site

So it selects each unique website, and counts the number of occurances of the site in Updates, along with a count of the site in table Alerts where the site is the same.

Been a while since i've written SQL but should work
 
You must log in or register to reply here.
Back
Top Bottom