Help with this query, please?

[overkil6]

Hello,

I am trying to create a pivot table of sorts with the following query:

Select o.Rep, count(o1.Order_Type) as [Quotes], sum(o1.subtotal) as
[Value], count(o2.Order_Type) as [Orders], sum(o2.subtotal) as
[Value], count(o3.Order_Type) as [Invoices], sum(o3.subtotal) as
[Value]

from OEOrdHead o
left join OEOrdHead as o1 on o1.Rep = o.Rep and o1.Entered_Date =
o.Entered_Date and o1.Order_Type = 'Q'
left join OEOrdHead as o2 on o2.Rep = o.Rep and o2.Entered_Date =
o.Entered_Date and o2.Order_Type = 'O'
left join OEOrdHead as o3 on o3.Rep = o.Rep and o3.Entered_Date =
o.Entered_Date and o3.Order_Type = 'I'
where o.Entered_Date between @Start and @End
group by o.Rep

However when I run this query I get the following error: Syntax error
(missing operator) in query expression ''. When I click OK it
highlights "d 01" from the first join statement and the last and
statement. "d" being the d from and.

Can anyone see where I've gone wrong?

 

[Melody]

Hi, I think I see what you are trying to do, and here is what I would change
if you are using this in an Access project:

Select o.Rep, count(o1.Order_Type) as Quotes, sum(o1.subtotal) as
subTotal1, count(o2.Order_Type) as Orders, sum(o2.subtotal) as
subTotal2, count(o3.Order_Type) as Invoices, sum(o3.subtotal) as
subTotal3
from OEOrdHead o left outer join
OEOrdHead o1 on o1.Rep = o.Rep and o1.Entered_Date =
o.Entered_Date and o1.Order_Type = 'Q' left outer join
OEOrdHead o2 on o2.Rep = o.Rep and o2.Entered_Date =
o.Entered_Date and o2.Order_Type = 'O' left outer join
OEOrdHead o3 on o3.Rep = o.Rep and o3.Entered_Date =
o.Entered_Date and o3.Order_Type = 'I'
where o.Entered_Date between @Start and @End
group by o.Rep

Good luck

Hello,

I am trying to create a pivot table of sorts with the following query:

Select o.Rep, count(o1.Order_Type) as [Quotes], sum(o1.subtotal) as
[Value], count(o2.Order_Type) as [Orders], sum(o2.subtotal) as
[Value], count(o3.Order_Type) as [Invoices], sum(o3.subtotal) as
[Value]

from OEOrdHead o
left join OEOrdHead as o1 on o1.Rep = o.Rep and o1.Entered_Date =
o.Entered_Date and o1.Order_Type = 'Q'
left join OEOrdHead as o2 on o2.Rep = o.Rep and o2.Entered_Date =
o.Entered_Date and o2.Order_Type = 'O'
left join OEOrdHead as o3 on o3.Rep = o.Rep and o3.Entered_Date =
o.Entered_Date and o3.Order_Type = 'I'
where o.Entered_Date between @Start and @End
group by o.Rep

However when I run this query I get the following error: Syntax error
(missing operator) in query expression ''. When I click OK it
highlights "d 01" from the first join statement and the last and
statement. "d" being the d from and.

Can anyone see where I've gone wrong?