*** Outline ***

I have an Access 2000 database, which contains information about
positions achieved for clients on search engines. Using ASP I plan to
display this database so each client can view his or her reports over
the web.

*** Required ***

I need an SQL query, which specifying a client (through a parameter –
a client number) will return a record set containing a summary of all
the reports for that client. The summary must contain for each report:

·     The unique report number
·     Date the report was run
·     The number of search engines featured
·     The number of positions achieved
·     The number of 1st positions
·     The number of top five positions
·     The number of top ten positions
·     The number of top twenty positions 

*** Database Configuration ***

Listed below is a partial list of the tables and columns in the
database that should be required for this task:

Table: TblReports
Description: This table holds summary information for every report
Columns:
    Name: RNo
    Type: LongInteger
    Description: Primary Key – Unique report number

    Name: ClientNo
    Type: LongInteger
    Description:  Foreign Key – Unique client number

    Name: DateRun
    Type: Date/Time
    Description: Date the report was run on
Relationships:
    TblReports.RNo – TblSearchPositions.RNo
    One-To-Many

Table: TblSearchEngines
Description: This table holds information about search engines
Columns:
    Name: SENo
    Type: Long Integer
    Description: Primary Key – Unique search engine number
    
    Name: SearchEngineURL
    Type: Text
    Description: The search engine URL 
Relationships:
TblSearchEngines.SENo – TblSearchPositions.SENo
One-To-Many

Table: TblSearchPhrases
Description: List of all search phrases in the reports
Columns:
    Name: SPNo
    Type: Long Integer 
    Description: Primary Key - Unique search phrase number

    Name: SearchPhrase
    Type: Text
    Description: Search-Phrase, the phrase entered into the search
engine
Relationships:
    TblSearchPhrases.SPNo – TblSearchPositions.SPNo
    One-To-Many

Table: TblSearchPositions
Description: All the report positions
Columns:
    Name: PNo
    Type: Long Integer
    Description: Primary Key - Unique position number

    Name: RNo
    Type: Long Integer
    Description: Foreign Key – Report the position belong to

    Name: SENo
    Type: Long Integer
    Description: Foreign Key - Search engine the position appears in

    Name: SPNo
    Type: Long Integer
    Description: Foreign key – Search phrase the position is for

    Name: Overall
    Type: Long Integer
    Description: The overall position achieved
    Name: Page
    Type: Long Integer
    Description: Page the result appears on

    Name: PagePosition
    Type: Long Integer
    Description: Position the result appears on the page
Relationships:
    TblReports.RNo – TblSearchPositions.RNo
    One-To-Many

    TblSearchEngines.SENo – TblSearchPositions.SENo
    One-To-Many

    TblSearchPhrases.SPNo – TblSearchPositons.SPNo
    One-To-Many

Many Thanks for any help.

---

Request for Question Clarification by hammer-ga on 06 Jan 2003 05:10 PST

Is it possible for you to upload your database, with some sample data,
to someplace where we could download it?

- Hammer

---

Clarification of Question by dink_buddy-ga on 06 Jan 2003 08:11 PST

I have upload a copy of the database with sample data and this can be
download from:
    http://www.sesmembers.co.uk/DBDownload/searchResults.mdb   (212
KB)

Thanks

Dink_buddy,

In order to follow along with my answer, please download your database
from
http://www.hammerdata.com/Google/

I have built the queries you requested into your database, so you can
try them on your sample data.

In order to make this more understandable and maintainable, I did this
as a query and subquery, rather than using a single SQL statement. You
still only need to run a single query to get your results.

If you look at the list of queries in your database, you will find two
new ones:
qryHDSMain - This is the one you run to get your results
qryHDSCountSE - The workhorse. Does some presummarizing to feed
qryHDSMain

Query Details
---------------

**** qryHDSCountSE ****

Here is the SQL broken out and commented. It is available in a
pastable form in the sample database.

' Use DISTINCT to get an accurate count of the number of search
engines. This combines the SE/SP pairs so that each SE only appears
once per report, regardless of how many phrases you tested with.

SELECT DISTINCT 

----------------------------------------

' Include the Report No so we can link to qryHDSMain

TblSearchPositions.RNo, 

------------------------------------------

' Include the SENo so we know which engines hit

TblSearchPositions.SENo, 

--------------------------------------------

' Get the number of positions per engine by counting how many phrases
hit

Count(TblSearchPositions.SPNo) AS CountOfSPNo, 

---------------------------------------------

' This section figures out how many hits landed in the specified
range. We're using a trick here. In the first line, which checks for
number 1 hits, the boolean expression [Overall]=1 returns True if
Overall=1 and False if it doesn't. True = -1 and False = 0. We apply
Abs to change it to True = 1 and False = 0. Now, each record where
Overall = 1 will place a 1 in the field. Sum adds them up and we know
how many there were. To get the other counts, we just change the
range!

Sum(Abs([Overall]=1)) AS Top1, 
Sum(Abs([Overall]<=5)) AS Top5, 
Sum(Abs([Overall]<=10)) AS Top10, 
Sum(Abs([Overall]<=20)) AS Top20

-----------------------------------------
' Specify which table we want

FROM TblSearchPositions

-------------------------------------------
' The fields we GROUP BY determine what is considered DISTINCT

GROUP BY TblSearchPositions.RNo, TblSearchPositions.SENo

----------------------------------------------
' Sort the records

ORDER BY TblSearchPositions.RNo, TblSearchPositions.SENo;

-----------------------------------------------

So far, so good? Okay, the other query...

**** qryHDSMain ****

' Select the regular fields

SELECT TblReports.ClientNo, TblReports.RNo, TblReports.DateRun, 

------------------------------------------------
' Select fields from qryHDSCountSE Counting and Summing as needed

Count(qryHDSCountSE.SENo) AS SECount, 
Sum(qryHDSCountSE.CountOfSPNo) AS NumOfPositions, 
Sum(qryHDSCountSE.Top1) AS SumOfTop1, 
Sum(qryHDSCountSE.Top5) AS SumOfTop5, 
Sum(qryHDSCountSE.Top10) AS SumOfTop10, 
Sum(qryHDSCountSE.Top20) AS SumOfTop20

---------------------------------------------------
' Joint qryHDSCountSE to this query by matching the Report Number

FROM TblReports INNER JOIN qryHDSCountSE ON TblReports.RNo =
qryHDSCountSE.RNo

----------------------------------------------------
' GROUP BY determines which records get counted and summed

GROUP BY TblReports.ClientNo, TblReports.RNo, TblReports.DateRun

-----------------------------------------------------
' Ask for the Client Number parameter

HAVING (((TblReports.ClientNo)=[Enter Client Number]))

-------------------------------------------------------
' Sort by report number

ORDER BY TblReports.RNo;

---------------------------------------------------------

This will make a lot more sense when you see it in Design and
Datasheet View!


Search strategy: None. Used Access 2000.

Additional Resources:
There is an excellent Access FAQ by MVPS.org
http://www.mvps.org/access/

You may want to take a look at the page about Lookups. You've used
them extensively and you may want to reconsider.
The Evils of Lookup Fields - MVPS.org
http://www.mvps.org/access/lookupfields.htm

Please ask for clarification if you need anything explained further.

Good luck with your Access project!

- Hammer

---

Clarification of Answer by hammer-ga on 06 Jan 2003 15:59 PST

Another resource:
W3Schools SQL Tutorial:
http://www.w3schools.com/sql/default.asp

You can get some more info there on using DISTINCT and other SQL keywords.

- Hammer

---

Request for Answer Clarification by dink_buddy-ga on 07 Jan 2003 07:23 PST

I have been using the query you have provided and it works great -
except when there are no positions for a report. When this happens the
query does not return the empty report in the recordset, is there any
way to remedy this?

---

Clarification of Answer by hammer-ga on 07 Jan 2003 07:57 PST

Yes. You can modify the Join type. In the SQL, find where it says 

INNER JOIN

and change it to say 

LEFT JOIN

This will cause records in tblReport to appear, even if there are no
records in TblSearchPositions.

- Hammer

Thanks Hammer - The query worked perfectly

this reminds me of one old usenet SQL puzzle -
http://www.luckyautos.com/sql_excercise/ here's copy of it.