Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complex QUERY help please! 3

Status
Not open for further replies.

ppetree

Programmer
Mar 3, 2007
60
US
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:
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
 
Can I/How do I tell Access to not return any data from table users?
Code:
Select purch.* ... from
How can I speed up this query
Code:
SELECT * FROM purch [b]inner join[/b] users  [b]on users.usr_id = purch.usr_id[/b]...
...automatically total columns purch_amnt1 + purch_amt2 + purch_amt3 + purch_amt4
Code:
select purch_date, count(purch.usr_id), sum(purch_amnt1) + sum(purch_amt2) + sum(purch_amt3) + sum(purch_amt4) as totalsale
FROM purch [b]inner join[/b] users  [b]on users.usr_id = purch.usr_id [/b]
GROUP BY purch_date
(Include your existing where clause)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
A staring point:
strSQL = "SELECT purch_date,Count(*) As purchases,Sum(purch_amnt1+purch_amt2+purch_amt3+purch_amt4) AS Total FROM purch P INNER JOIN users U ON P.usr_id=U.usr_id WHERE usr_referer='" & Session("A_domain") & "' AND Int(purch_date) Between #" & start_date & "# And #" & end_date & "# GROUP BY purch_date HAVING Sum(purch_amnt1+purch_amt2+purch_amt3+purch_amt4)>0"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
You were both dead-on!

PH, you are a champ! That's twice you have helped me!

Greg, thanks for breaking it down like that, it helps me to learn the different syntaxs.

I had tried most the elements but had them wrong. I used sum as SUM(purch_amt1, purch_amt2, purch_amt3, purch_amt4) and that OBVIOUSLY did work.

I also tried the inner join and didn't know to use the ON so now I understand that... slowly but surely I am acquiring enough knowledge to shoot myself in the foot! LOL

Here's what I ended up with:
Code:
strSQL = "SELECT purch_date, Count(*) As purchases, purch_txnum, Sum(purch_amount1+purch_amount2+purch_amount3+purch_amount4) AS Total FROM purch P INNER JOIN users U ON P.usr_num=U.usr_num WHERE usr_referer='" &Session("A_domain") & "' AND purch_total > 0 AND Int(purch_date) Between #" &start_date &"# And #" &end_date &"# GROUP BY purch_date, purch_txnum"

Thanks,

Phil
 
I have a similar issue ...


I have two .mdb, each has a pass through query.
one has the header and the other the details.

i have a make query from each so I have a Table in MS Access to work with. This seemed to make it a little faster to run queries.

Not to mention each file (table) is 1.4gb so I can't combine the two in one.

I have a 3rd .mdb that links to these other two so I can run queries and other settings for the users.


A form will be created to show the invoice results with the header and details. the results will only be by what the user inputs though.

They can either enter the first few letters of the customer which will result in multiple invoices.

Or if they know the invoice number, the user would enter the specific invoice number.

Other option would be a invoice date range, etc ...


currently this is the query I am testing with, by invoice number input.

Run it on the server it's located on, it takes about 30-40 seconds. Run it on Terminal Server, it takes almost a minute.

They would like the result to show in less then 15 seconds. How I can make this happen? Keeping in mind that the data will continue to grow each day with a daily download into the tables with the pass through queries.

SELECT ARN_InvoiceHistoryHeader.InvoiceNumber, ARO_InvoiceDetails.LineType, ARO_InvoiceDetails.LineIndex, ARO_InvoiceDetails.DetailSeqNumber, ARO_InvoiceDetails.TierSeqNumber, ARO_InvoiceDetails.HeaderSeqNumber, ARO_InvoiceDetails.SOItemNumber, ARO_InvoiceDetails.SODescription, ARO_InvoiceDetails.SODiscount, ARO_InvoiceDetails.SOQtyShipped, ARO_InvoiceDetails.SOUnitOfMeasure, ARO_InvoiceDetails.SOUnitMeasConvFctr, ARO_InvoiceDetails.SOUnitPrice, ARO_InvoiceDetails.SOExtChargeAmount, ARO_InvoiceDetails.SOUnitCost, ARO_InvoiceDetails.Weight, ARO_InvoiceDetails.Freight, ARO_InvoiceDetails.DiscountAmount, ARO_InvoiceDetails.SnaggNumber, ARO_InvoiceDetails.SOLotNumber, ARO_InvoiceDetails.SOLotItemRecptQty, ARO_InvoiceDetails.SOSerialNumber, ARO_InvoiceDetails.SOSerialItemRecptQty
FROM ARN_InvoiceHistoryHeader INNER JOIN ARO_InvoiceDetails ON (ARN_InvoiceHistoryHeader.InvoiceNumber = ARO_InvoiceDetails.InvoiceNumber) AND (ARN_InvoiceHistoryHeader.HeaderSeqNumber = ARO_InvoiceDetails.HeaderSeqNumber)
WHERE (((ARN_InvoiceHistoryHeader.InvoiceNumber)=[Enter Invoice Number]));
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top