Access and SQL query Issue

A

Ayaz Hoda

Hi All
My Access applcation crash when running this query from Access Query
Desinger but work fine in SQL


SQL ---- return 15 rows

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
min(Orders.CID) AS FirstOfCID,
min(Orders.Customer) AS FirstOfCustomer,
min(Orders.Date) AS FirstOfDate,
Orders.PostToBook, Customers.Rep, Customers.InternalRep,
Count(CountOfProInvoices.CountOfKeyNum) AS CountOfCountOfKeyNum

FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID)
LEFT JOIN CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook, Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((min(Orders.Date))>='16-Apr-2008 ')
AND ((Orders.PostToBook)=1)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((min(Orders.Date))>=' 16-Apr-2008 ')
AND ((Orders.PostToBook)=1))


Access--- This query Hang and no response ultimatly need to shut down access
query designer.

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
First(Orders.CID) AS FirstOfCID, First(Orders.Customer) AS FirstOfCustomer,
First(Orders.Date) AS FirstOfDate, Orders.PostToBook, Customers.Rep,
Customers.InternalRep, Count(CountOfProInvoices.CountOfKeyNum) AS
CountOfCountOfKeyNum
FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID) LEFT JOIN
CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook, Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((First(Orders.Date))>=#4/16/2008#)
AND ((Orders.PostToBook)=True)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((First(Orders.Date))>=#4/16/2008#) AND
((Orders.PostToBook)=True));
 
A

Arvin Meyer [MVP]

The 2 queries are different. One uses the Min function, the other the First
function. Have you tried running the exact same query in both places?
 
A

Ayaz Hoda

SQL Doesn't know First()

VBA MIN() replaced by SQL FIRST()

Arvin Meyer said:
The 2 queries are different. One uses the Min function, the other the First
function. Have you tried running the exact same query in both places?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ayaz Hoda said:
Hi All
My Access applcation crash when running this query from Access Query
Desinger but work fine in SQL


SQL ---- return 15 rows

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
min(Orders.CID) AS FirstOfCID,
min(Orders.Customer) AS FirstOfCustomer,
min(Orders.Date) AS FirstOfDate,
Orders.PostToBook, Customers.Rep, Customers.InternalRep,
Count(CountOfProInvoices.CountOfKeyNum) AS CountOfCountOfKeyNum

FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID)
LEFT JOIN CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook, Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((min(Orders.Date))>='16-Apr-2008 ')
AND ((Orders.PostToBook)=1)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((min(Orders.Date))>=' 16-Apr-2008 ')
AND ((Orders.PostToBook)=1))


Access--- This query Hang and no response ultimatly need to shut down
access
query designer.

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
First(Orders.CID) AS FirstOfCID, First(Orders.Customer) AS
FirstOfCustomer,
First(Orders.Date) AS FirstOfDate, Orders.PostToBook, Customers.Rep,
Customers.InternalRep, Count(CountOfProInvoices.CountOfKeyNum) AS
CountOfCountOfKeyNum
FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID) LEFT JOIN
CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook, Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((First(Orders.Date))>=#4/16/2008#)
AND ((Orders.PostToBook)=True)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((First(Orders.Date))>=#4/16/2008#)
AND
((Orders.PostToBook)=True));
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

Then try First in the Access query. Actually, Min and First are very
different, especially in an unsorted dataset. Here's a quote from an old
Access help file:

The First and Last functions are analogous to the MoveFirst and MoveLast
methods of a DAO Recordset object. They simply return the value of a
specified field in the first or last record, respectively, of the result set
returned by a query. Because records are usually returned in no particular
order (unless the query includes an ORDER BY clause), the records returned
by these functions will be arbitrary.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ayaz Hoda said:
SQL Doesn't know First()

VBA MIN() replaced by SQL FIRST()

Arvin Meyer said:
The 2 queries are different. One uses the Min function, the other the
First
function. Have you tried running the exact same query in both places?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ayaz Hoda said:
Hi All
My Access applcation crash when running this query from Access Query
Desinger but work fine in SQL


SQL ---- return 15 rows

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
min(Orders.CID) AS FirstOfCID,
min(Orders.Customer) AS FirstOfCustomer,
min(Orders.Date) AS FirstOfDate,
Orders.PostToBook, Customers.Rep, Customers.InternalRep,
Count(CountOfProInvoices.CountOfKeyNum) AS CountOfCountOfKeyNum

FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID)
LEFT JOIN CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook,
Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((min(Orders.Date))>='16-Apr-2008 ')
AND ((Orders.PostToBook)=1)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((min(Orders.Date))>=' 16-Apr-2008
')
AND ((Orders.PostToBook)=1))


Access--- This query Hang and no response ultimatly need to shut down
access
query designer.

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
First(Orders.CID) AS FirstOfCID, First(Orders.Customer) AS
FirstOfCustomer,
First(Orders.Date) AS FirstOfDate, Orders.PostToBook, Customers.Rep,
Customers.InternalRep, Count(CountOfProInvoices.CountOfKeyNum) AS
CountOfCountOfKeyNum
FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID) LEFT JOIN
CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook,
Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((First(Orders.Date))>=#4/16/2008#)
AND ((Orders.PostToBook)=True)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND
((First(Orders.Date))>=#4/16/2008#)
AND
((Orders.PostToBook)=True));
Click to expand...
Click to expand...
Click to expand...
 
A

Ayaz Hoda

Well Arvin I am using first() in access not min()

Thanks

Arvin Meyer said:
Then try First in the Access query. Actually, Min and First are very
different, especially in an unsorted dataset. Here's a quote from an old
Access help file:

The First and Last functions are analogous to the MoveFirst and MoveLast
methods of a DAO Recordset object. They simply return the value of a
specified field in the first or last record, respectively, of the result set
returned by a query. Because records are usually returned in no particular
order (unless the query includes an ORDER BY clause), the records returned
by these functions will be arbitrary.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ayaz Hoda said:
SQL Doesn't know First()

VBA MIN() replaced by SQL FIRST()

Arvin Meyer said:
The 2 queries are different. One uses the Min function, the other the
First
function. Have you tried running the exact same query in both places?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi All
My Access applcation crash when running this query from Access Query
Desinger but work fine in SQL


SQL ---- return 15 rows

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
min(Orders.CID) AS FirstOfCID,
min(Orders.Customer) AS FirstOfCustomer,
min(Orders.Date) AS FirstOfDate,
Orders.PostToBook, Customers.Rep, Customers.InternalRep,
Count(CountOfProInvoices.CountOfKeyNum) AS CountOfCountOfKeyNum

FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID)
LEFT JOIN CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook,
Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((min(Orders.Date))>='16-Apr-2008 ')
AND ((Orders.PostToBook)=1)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND ((min(Orders.Date))>=' 16-Apr-2008
')
AND ((Orders.PostToBook)=1))


Access--- This query Hang and no response ultimatly need to shut down
access
query designer.

SELECT Orders.MID, Orders.TotalValue, Sum(OBook.Value) AS SumOfValue,
First(Orders.CID) AS FirstOfCID, First(Orders.Customer) AS
FirstOfCustomer,
First(Orders.Date) AS FirstOfDate, Orders.PostToBook, Customers.Rep,
Customers.InternalRep, Count(CountOfProInvoices.CountOfKeyNum) AS
CountOfCountOfKeyNum
FROM ((Orders LEFT JOIN OBook ON Orders.MID = OBook.MID)

INNER JOIN Customers ON Orders.CID = Customers.CID) LEFT JOIN
CountOfProInvoices ON Orders.MID = CountOfProInvoices.MID

GROUP BY Orders.MID, Orders.TotalValue, Orders.PostToBook,
Customers.Rep,
Customers.InternalRep

HAVING (((Sum(OBook.Value))>[TotalValue]+0.01 Or
(Sum(OBook.Value))<[TotalValue]-0.01)
AND ((First(Orders.Date))>=#4/16/2008#)
AND ((Orders.PostToBook)=True)) OR (((Orders.TotalValue)<>0)
AND ((Sum(OBook.Value)) Is Null) AND
((First(Orders.Date))>=#4/16/2008#)
AND
((Orders.PostToBook)=True));
Click to expand...
Click to expand...
Click to expand...
 
Top