How do I express this MySQL in Access?

[JJSRich]

I am using the following query to get a list of items to bill, put into a multiselect listbox where each item can be selected and billed.

SELECT 
q.quote_id AS [SE Ref]
, q.date_rtn_final AS [Date Returned]
, q.add1 AS [Address Line 1]
, IIf(IsNull(c2.company),q.distributor,c2.company) AS Builder [COLOR=green] 'Use name from company table, otherwise use name input into quote table. - latter is for backwards-compatability[/color]
, r.Rate_Name AS [Rate Name]
FROM (((quotations AS q 
INNER JOIN rates AS r ON q.Rate_ID = r.Rate_ID) 
LEFT JOIN companies AS c2 ON q.build_id = c2.Comp_ID) 
LEFT JOIN companies AS c1 ON q.dist_id = c1.Comp_ID) 
LEFT JOIN billing AS b ON q.quote_id = b.Quote_ID
WHERE (((q.date_rtn_final) Is Not Null) 
AND ((IIf(IsNull([c1].[company]),[q].[distributor],[c1].[company]))=[Forms]![Billing].[dist]) [COLOR=green]'Used to restrict to company names that can get the invoices, )don't want to bill cust A for something that cust B has to pay for) and backwards compatability is needed.  May be obsoleted by c1 join[/color]
AND ((q.Billed)=0) 
AND ((q.excluded)=0) 
AND ((IIf([q].[rate_id]=1,0,IIf([q].[NoWin]=1,IIf([q].[JobWon]=1,1,0),1)))=1) [COLOR=green] 'if it is a no-win no-fee, only show the ones we have won.[/color]
AND ((b.Bill_ID) Is Null) 
AND ((q.Rate_ID)<>1 
And (q.Rate_ID)<>11 
And (q.Rate_ID)<>12))
ORDER BY q.quote_id DESC;

I want to add in a column that tells me the value of each item for billing. The way we are set up, an item (quotation) is made up of a bunch of individual drawings. The MySQL that shows me this follows

select
q.quote_id
,q.date_rtn_final
,q.add1
,r.rate_name
,if(q.rate_id = 2,q.fix_price,
   if(r.hourly=1,(sum(w.hoursworked) * if(q.hrate='0.00',31.25,q.hrate)),
    if(q.rate_id = 1,0,
    (sum(h.hours) * if(q.hrate='0.00',31.25,q.hrate))
    )
   )
  )
from quotations as q
left join housetype as h on h.quote_id = q.quote_id and h.deleted = 0
left join hoursworked as w on h.ht_id = w.ht_id
left join rates as r on q.rate_id = r.rate_id
group by q.quote_id

The logic is this: If the job is a fixed price, print that. If not, then, if it is a hours worked type rate, multiply the total hours worked by the selected hourly rate. If the nincompoops didn't enter the rate, use our normal hourly rate.

If the rate is free, (rate ID 1) then print zero. Anything else is going to be billed by how much time it should have taken. Sum up the hours assigned. Again, if the nincompoops didn't enter the hourly rate, use our normal rate.

 

[Remou]

The SQL is the same as for Access, apart from the second query where if need to be changed to iif. Do you wish to include a join to the second query or to nest it? Have you considered passthrough queries, rather than running complex queries in Access, which can be slow?

 

[JJSRich]

I'll try it as a pass-through, but it'll need to be re-written after every afterupdate event for the [Forms]![Billing].[dist] control. No bother, done it in a dozen places already.

I tried changing my ifs to iifs, and ran into complaints over aggregate functions, which I don't know how to tackle. When that comes up on complex queries, I just throw in the towel and write a pass-through query.

 

[Remou]

Sorry, I was not paying attention. Perhaps:

SELECT q.quote_id
, IIf(q.[rate_id]=2,q.[fix_price],
    IIf(r.[hourly]=1,(w.[hw]*IIf(q.[hrate]='0.00',31.25,q.[hrate])),
     IIf(q.[rate_id]=1,0,([h].[hrs]*IIf(q.[hrate]='0.00',31.25,q.[hrate]))))) AS Result
FROM ((quotations AS q 
LEFT JOIN (Select quote_id,ht_id, sum(hours) as hrs from housetype where deleted=0 group by quote_id,ht_id) AS h ON q.quote_id = h.quote_id) 
LEFT JOIN (Select ht_id, sum(hoursworked) as hw from hoursworked group by ht_id) AS w ON h.ht_id = w.ht_id) 
LEFT JOIN rates AS r ON q.rate_id = r.rate_id

 

[JJSRich]

Sorry for the late reply, returned to work after nice long Christmas holiday.

Thanks for the input, though the code you supplied returns either a 0 or #ERROR. I have absolutely no idea what it's doing though so I will need to experiment and figure out what the code provided by you actually does. Them joins is weird

 

[ESquared]

IIf(IsNull(c2.company),q.distributor,c2.company)

=

Nz(c2.company, q.distributor)

 

[lespaul]

Them joins is weird

Maybe check out Understanding SQL Joins

Leslie

Have you met Hardy Heron?

 

[JJSRich]

it's the select inside the join that is weird. I need to play around with it, as I've never seen something like that done before. Now that I've had some time to sit and think about it, I think I understand, and will try to re-write it to work correctly.

If I get it, what the join

LEFT JOIN (Select quote_id,ht_id, sum(hours) as hrs from housetype where deleted=0 group by quote_id,ht_id) AS h ON q.quote_id = h.quote_id)

means is Left join this created table of qoute_ID, ht_id, sum(hours) aliased as h where the quote_id's are equal. Somehow, this calculation is pulled out in the main select even though the calculating of sum(hours) is being performed during the JOIN operation. Correct?

 

[Remou]

The sum occurs in the sub query and is referenced in the main query, yes.

http://lessthandot.com

 

[JJSRich]

Cool, thanks. I will practise it and become enlightened