Complex QUERY help please! 3

[ppetree]

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:

usr_id, usr_name, usr_company, usr_addr, usr_referer

Table Users Content:

1, ttester, MakeOneUp, 123 Main St., google.com
2, jjester, DummyUp, 69 Main St., domain.com

Table Purch Schema:

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:

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:

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!)

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#

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

 

[traingamer]

Can I/How do I tell Access to not return any data from table users?

Select purch.* ... from

How can I speed up this query

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

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

 

[PHV]

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

 

[ppetree]

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:

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

 

[angelandgreg]

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]));