Hi All!
I have 1 database with 2 tables (users, purch). I need to query table 'users' and match on field users.referer and in purch I need match when total_amount > 0 and purch_date is between start_date and end_date and from there I need the sum total of 4 columns from purch for each day within date range. WHEW!
Table Users Schema:
Table Users Content:
Table Purch Schema:
Table Purch Contains:
My current query:
What I am currently getting back (and this is sooooo SLOW!)
What my report needs to contain if url=google.com AND purch_date is between #2007-04-01# And #2007-04-10#
So my questions are:
1) Can I/How do I tell Access to not return any data from table users?
2) How can I get Access to automatically total columns purch_amnt1 + purch_amt2 + purch_amt3 + purch_amt4?
3) How can I speed up this query
Thanks,
Phil
I have 1 database with 2 tables (users, purch). I need to query table 'users' and match on field users.referer and in purch I need match when total_amount > 0 and purch_date is between start_date and end_date and from there I need the sum total of 4 columns from purch for each day within date range. WHEW!
Table Users Schema:
Code:
usr_id, usr_name, usr_company, usr_addr, usr_referer
Table Users Content:
Code:
1, ttester, MakeOneUp, 123 Main St., google.com
2, jjester, DummyUp, 69 Main St., domain.com
Table Purch Schema:
Code:
usr_id, pur_date, pur_item1, pur_qnty1, pur_amnt1, pur_item2, pur_qnty2, pur_amnt2, pur_item3, pur_qnty3, pur_amnt3, pur_item4, pur_qnty4, pur_amnt4, pur_vat, pur_ship
Table Purch Contains:
Code:
1 3/15/2007 SCHD01 1 495 0 0 0 0 0 0 0 0 0...
1 4/2/2007 SCHD02 1 1995 0 0 0 0 0 0 0 0 0...
1 4/2/2007 SCHD02 1 495 0 0 0 0 0 0 0 0 0...
1 4/5/2007 SCHD01 1 495 0 0 0 0 0 0 0 0 0...
1 4/7/2007 SCHD02 1 1995 SCHD04 1 143.03 0 0 0 0 0 0...
2 3/21/2007 SCHD01 1 495 0 0 0 0 0 0 0 0 0...
2 4/6/2007 SCHD01 1 495 0 0 0 0 0 0 0 0 0...
My current query:
Code:
strSQL = "SELECT * FROM purch, users WHERE usr_referer='" &Session("A_domain") &"' AND purch_total > 0 AND Int(purch_date) Between #" &start_date &"# AND #" &end_date &"# GROUP BY purch_date"
What I am currently getting back (and this is sooooo SLOW!)
Code:
1 4/2/2007 SCHD02 1 1995 0 0 0 0 0 0 0 0 0...,1, ttester, MakeOneUp, 123 Main St., google.com
1 4/2/2007 SCHD02 1 495 0 0 0 0 0 0 0 0 0...,1, ttester, MakeOneUp, 123 Main St., google.com
1 4/5/2007 SCHD01 1 495 0 0 0 0 0 0 0 0 0...,1, ttester, MakeOneUp, 123 Main St., domain.com
1 4/7/2007 SCHD02 1 1995 SCHD04 1 143.03 0 0 0 0 0 0...,1, ttester, MakeOneUp, 123 Main St., google.com
What my report needs to contain if url=google.com AND purch_date is between #2007-04-01# And #2007-04-10#
Code:
Date # purchases Total Sale
-------- ----------- ----------
4/2/2007 2 $2490
4/5/2007 1 495
4/7/2007 1 $2138.03
So my questions are:
1) Can I/How do I tell Access to not return any data from table users?
2) How can I get Access to automatically total columns purch_amnt1 + purch_amt2 + purch_amt3 + purch_amt4?
3) How can I speed up this query
Thanks,
Phil