Query format ???

[ppetree]

I have a database I have a table where one of the fields (afl_url) contains a URL.

In another database I have a table wherein the records MAY contain a field which may have a matching URL (I only need up to the .com part)

I am trying to do MTD and YTD queries from the 2nd database where the data range is appropriate and the url contains a matching string (up to the .com part, ignoring the remainder of the url).

My date fields are defined as a 'General Date'.

What I have is this:

Select * from users Where usr_date > #str_StartDate# and usr_date < #Now()# and usr_referrer = afl_url

#str_StartDate# is defined as either 01/01/2007 or 04/01/2007 and is generated with str_StartDate = RTrim(Now()).

Not being a db guru I am just certain that this query string will not work so I thought I'd ask for a little help before I start coding this!

And any help would be greatly appreciated!

Phil

 

[jedraw]

Phil,

Let's say the first table is Table1, and it contains the field (afl_url). Your second table is Users and has fields usr_date and usr_referrer and others....

Select * from users, Table1
Where
usr_date > #str_StartDate#
and usr_date < #Now()#
and usr_referrer = Table1.afl_url

This query will select all fields from table1 and Users for those records WHERE
the usr_date is greater than the startDate and less than
todays Date AND the usr_referrer = afl_url. This query excludes the startDate and Now().

This query does nothing with MTD or YTD. The inclusion of the .com in the URL depends on what you are trying to match.

You may want to look at the BETWEEN operator that includes the end points if you wish to include StartDate and NOw().

 

[ppetree]

Thanks for that!

I keep uncovering more and more SQL commands.

I played with this today and it gets worse...

database1 = table1 which has afl_id, afl_name, afl_company, afl_addr, afl_phone, afl_url etc. * 20 companies

database2 = table1 which has user_id, user_name, user_company, user_addr, user_phone, user_referrer etc. and there could be 1000's of these

database2 = table2 which has purch_item1 purch_amount1 etc.

afl_id will log in and then run a report (mtd/ytd). Since afl_id is logged in I have the afl_url set as a session variable afl_id's log in process; So the actual query string I am working on is NOW:

Select * from users, Table1 Where usr_date > #str_StartDate# AND usr_date < #Now()#
and usr_referrer = Session("afl_url") and Table2.purch_date > #str_StartDate# AND purch_date < #Now()# purch_id = table1.user_id

Does that sound about right?

Thanks again!

Phil

afl_url

 

[jedraw]

Phil,

Can't say that I fully understand what you're trying to do, but there is an error in fundamentals.

If you have 3 tables (table1, users and table2), then you must refer to them in a FROM clause. You can't just introduce table2 in a reference to a column/field eg Table2.purch_date .

When you speak of database1 and database2 are these really separate .mdb files or are these different tables in the same .mdb?
Can you describe what you're trying to do in english ... not SQL?

For example:

I have these companies (affiliates?), and users and purchases? and I'm trying to match users and companies and find their purchase for a speific timeperiod..... or whatever...

 

[ppetree]

Yes, there are two different .mdb files:

The first one is affiliate.mdb and contains three tables table1 has all the data on our affiliate program (name, userid, pword, addr, url etc.) while table2 called 'track' has all the times an affiliate has displayed one of our banners or links and table3 is payments made to the affiliate (amounts, dates etc).

The second mdb is call users.mdb and contains two tables: table1 has all the data on our users (name, userid, pword, addr, and referrer which may = url in the affiliate.mdb).

Once the affiliate logs in I have their userid etc. stored as a session variable.

I have to write a series of reports:
1) MTD on banner displays - this could contain 100 different banner file names displayed 100 times each on the same day or one banner file name displayed 10 times each day.
2) YTD as above
3) MTD sales for the affiliate - query user purchases where referer = affiliate_url
4) YTD sales for the affiliate
5) MTD payments for the affiliate (easy to do)
6) YTD payments for the affiliate (also easy enough)

A representation of the track table is this

1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban2a.gif	3/29/2007  
1  sasyban2a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
1  sasy_banner_large.gif	4/3/2007  
1  sasy_banner_square.gif	4/4/2007  
1  sasy_banner_square.gif	4/4/2007  
2  sasy_banner_large.gif	4/4/2007  
1  sasy_banner_square.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
Grand Total (32 Detail Records)

 

[ppetree]

I should also add that I have been trying this query as a result of a post in another thread:

strSQL = "SELECT COUNT(ID), afl_name, datevalue(afl_date) FROM track WHERE datevalue(afl_date)=datevalue(date()) AND ID ='" &uid &"' GROUP BY afl_name, datevalue(afl_date)"

I keep getting a 'data type mismatch' error on the query and I'm not sure it fully gets me what I want.

for ID count all unique file_names for each date in date_range

So if I ran this report for ID=1, date range 04/01/2007 thru 04/15/2007 (based on the table above) I should get a RS containing 4 entries:

afl_name                 afl_date  total_displays
sasy_banner_large.gif    4/3/2007       1
sasy_banner_square.gif   4/4/2007       2 
sasy_banner_square.gif   4/6/2007       1

 

[jedraw]

Phil,
I set up a test table (Track) using your sample data.

Here's the SQL for a query to give the result you're looking for. I set up the afl_date to be Date datatype, so that's why the values are surrounded with #. This is required syntax for Date datatype.
You could adjust this SQL to use your uid as in your example SQL.

SELECT Track.ID
, Track.afl_name
, Count(Track.afl_name) AS total_displays
, Track.afl_date
FROM Track
GROUP BY Track.ID, Track.afl_name, Track.afl_date
HAVING (Track.ID=1 AND 
Track.afl_date Between #4/1/2007# And #4/15/2007#);

 

[lespaul]

Just as an FYI, Access uses the HAVING clause whenever an aggregate function is used, but that's not the way it is supposed to be used.

In this case you would use the WHERE clause:

SELECT Track.ID
, Track.afl_name
, Count(Track.afl_name) AS total_displays
, Track.afl_date
FROM Track
[b]WHERE (Track.ID=1 AND
Track.afl_date Between #4/1/2007# And #4/15/2007#)[/b]
GROUP BY Track.ID, Track.afl_name, Track.afl_date;

The HAVING clause is used when you want to filter on the aggregate. Let's say you only wanted to see records where the number of Total_Displays is more than 10...that would be a situation to use the HAVING clause:

SELECT Track.ID
, Track.afl_name
, Count(Track.afl_name) AS total_displays
, Track.afl_date
FROM Track
WHERE (Track.ID=1 AND
Track.afl_date Between #4/1/2007# And #4/15/2007#)
GROUP BY Track.ID, Track.afl_name, Track.afl_date
[b]HAVING Count(Track.afl_name) > 10[/b];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ppetree]

Thanks All!

This is what I ended up with:

strSQL = "SELECT afl_name, Int(afl_date), Count(*) AS total_displays FROM track WHERE Int(afl_date) Between #" &start_date &"# And #" &end_date &"# AND ID=" &uid &" GROUP BY afl_name, Int(afl_date)"

Phil