Query to count several records

[Makk00700]

Hi All
I'd like to thank any one who responds in advance, since I've been making my self go nuts trying to get this problem to work.

The outline of the problem:
I’m attempting to gather several records together in by using the count function in a microsoft access query.

The goal is to work out the number of enquires made through several websites, according to the date the enquiry was sent, and the promotion code from the site. Then compare these to the statcounter generated for the date, and work out several statistics such as the average enquires per day.

eg count([enquiry date]=[stat Date] & [stat promotion code]=[enquiry promotion code])

The problem I’m having is to have the query display the statcounter details, (eg pageloads, unique visitors) and then count the number of enquiries made on that date, according to the promotion code. However on some days no enquiries are sent, however their are still statcounter details generated which i would like to display in a report, to show quite days, and accurately generate the averages.

To be honest I’m not 100% sure which approach to use, eg entering an IIF statement in the query criteria, create an SQL query in the query field, or manually making relationships between the "enquiries sent" and "statcounter" tables.

Anyways sorry for the waffle, but i didn’t want to skip anything. If theirs any areas you'd like more detail please let me know.

Mark

 

[Remou]

It is nearly always best to post at least a section of your SQL, if not all.

 

[Makk00700]

Thanks for the quick response, and as requested the whole script from the query:
---------------

SELECT tbl_Promotion_Codes.[Promotion Name], tbl_Promotion_stats.[Date of Stat Details], tbl_Promotion_stats.[Number of Page Loads], tbl_Promotion_stats.[Unique Visitors], tbl_Promotion_stats.[First Time Visitors], tbl_Promotion_stats.[Returning Visitors], Format([Date of Stat Details],"dddd") AS [Day], Format([Date of Stat Details],"dd mmm yyyy") AS Datetext, Enquiries_Sent.Date, Count(Enquiries_Sent.Date) AS CountOfDate
FROM (tbl_Promotion_Codes INNER JOIN Enquiries_Sent ON (tbl_Promotion_Codes.[Promotion Name] = Enquiries_Sent.Promotion_Code) AND (tbl_Promotion_Codes.[Promotion Name] = Enquiries_Sent.Promotion_Code) AND (tbl_Promotion_Codes.[Promotion Name] = Enquiries_Sent.Promotion_Code)) INNER JOIN tbl_Promotion_stats ON (tbl_Promotion_Codes.[Promotion Name] = tbl_Promotion_stats.[Promotion Code]) AND (tbl_Promotion_Codes.[Promotion Name] = tbl_Promotion_stats.[Promotion Code]) AND (tbl_Promotion_Codes.[Promotion Name] = tbl_Promotion_stats.[Promotion Code])
GROUP BY tbl_Promotion_Codes.[Promotion Name], tbl_Promotion_stats.[Date of Stat Details], tbl_Promotion_stats.[Number of Page Loads], tbl_Promotion_stats.[Unique Visitors], tbl_Promotion_stats.[First Time Visitors], tbl_Promotion_stats.[Returning Visitors], Format([Date of Stat Details],"dddd"), Format([Date of Stat Details],"dd mmm yyyy"), Enquiries_Sent.Date
HAVING (((Enquiries_Sent.Date)=[Date of Stat Details]));

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

 

[Remou]

I suspect you want something like this, that is LEFT JOINs instead of INNER JOINs.

SELECT tbl_Promotion_Codes.[Promotion Name], tbl_Promotion_stats.[Date of Stat Details], tbl_Promotion_stats.[Number of Page Loads], tbl_Promotion_stats.[Unique Visitors], tbl_Promotion_stats.[First Time Visitors], tbl_Promotion_stats.[Returning Visitors], Format([Date of Stat Details],"dddd") AS [Day], Format([Date of Stat Details],"dd  mmm yyyy") AS Datetext, Enquiries_Sent.Date, Count(Enquiries_Sent.Date) AS CountOfDate
FROM (tbl_Promotion_stats LEFT JOIN Enquiries_Sent ON tbl_Promotion_stats.[Promotion code] = Enquiries_Sent.Promotion_code) LEFT JOIN tbl_Promotion_Codes ON tbl_Promotion_stats.[Promotion code] = tbl_Promotion_Codes.[Promotion Name]
GROUP BY tbl_Promotion_Codes.[Promotion Name], tbl_Promotion_stats.[Date of Stat Details], tbl_Promotion_stats.[Number of Page Loads], tbl_Promotion_stats.[Unique Visitors], tbl_Promotion_stats.[First Time Visitors], tbl_Promotion_stats.[Returning Visitors], Format([Date of Stat Details],"dddd"), Format([Date of Stat Details],"dd  mmm yyyy"), Enquiries_Sent.Date
HAVING (((Enquiries_Sent.Date)=[Date of Stat Details]))

You may wish to read

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

 

[Makk00700]

Hey Remou

Thanks for the reply, and the info, from actually seeing what you've changed, I wasn’t too far of the solution, many thanks for your help.

Mark

 

[jmstarbuck]

Is there a way to create a row id field in a query so that each row selected is numbered?

Something like "select ? as rowid, a, b c from tableF"

1 a b c
2 a b c
3 a b c

 

[genomon]

Hey jmstarbuck, might wanna start a new thread (and post some more info relevant to your problem).

All I ask is a chance to prove that money can't make me happy.