Help with Query in ADP

[Fred Newtz]

I am trying to convert a query that did not get converted with the Upsizing
Wizard.

The query needs to return a record only if the primary key is not in another
related table. So basically I am trying to show all of the orders that are
in place that have not been invoiced yet. So I need to be able to check the
tblInvoices table and see if the OrderID is not in there. Here is the
current query in Access.

SELECT JobClass.Item, Orders.JobClass, Orders.Priority, Orders.OrderID,
Orders.CustomerID, Customers.CompanyName, Orders.EntryDate, Orders.RcvdDate,
Quotes.QuoteDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.WIPDescription, Orders.OrderDate, Orders.InvoiceCreated,
tblInvoices.OrderID, Orders.Payment, IIf(DCount("[Quantity]","Order
Details","OrderID = '" & [Orders].[OrderID] &
"'")<>0,CCur((DSum("[Quantity]*[UnitPrice]","Order Details","OrderID = '" &
[Orders].[OrderID] & "'"))-[Orders.Payment]),CCur("0")) AS OrderAmount

FROM Customers INNER JOIN (((Orders LEFT JOIN Quotes ON Orders.OrderID =
Quotes.OrderID) INNER JOIN JobClass ON Orders.JobClass = JobClass.JobClass)
LEFT JOIN tblInvoices ON Orders.OrderID = tblInvoices.OrderID) ON
Customers.CustomerID = Orders.CustomerID

WHERE (((Orders.JobClass)<>"SERVICE JOBS - TO EVALUATE" And
(Orders.JobClass)<>"SERVICE JOBS - WAITING ON CUST" And
(Orders.JobClass)<>"SERVICE JOBS - COMPANY EQUIPMENT NOT SOLD" And
(Orders.JobClass)<>"TRACEABILITY ORDER") AND ((Orders.InvoiceCreated)=False)
AND ((tblInvoices.OrderID) Is Null)) - This last statement is what is
giving me headaches.

ORDER BY JobClass.Item, Orders.Priority;


Anyone have any ideas or what I should search for even.

Thanks,

Fred

 

[Van T. Dinh]

There are a number of things that are not straight-forward
conversions to MS-SQL View / SQL String:

1. IIF is an Access function which is not available in MS-
SQL. You need to convert it to MS-SQL CASE Statement.

2. The String delimiter in MS-SQL is SINGLE-quote, so you
need to convert all double-quote to single quotes.

3. DCount is an Access function only. You need to use the
sub-query with Count() function in MS-SQL.

4. CCur is a VBA function only. You need to use eith CAST
or CONVERT function in MS-SQL.

You will probably need to use the MS-SQL Query Analyzer to
help you construct the SQL String. All MS-SQL elements I
mentioned above are available in MS-SQL BOL (Books On
Line).

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am trying to convert a query that did not get converted with the Upsizing
Wizard.

The query needs to return a record only if the primary key is not in another
related table. So basically I am trying to show all of the orders that are
in place that have not been invoiced yet. So I need to be able to check the
tblInvoices table and see if the OrderID is not in there. Here is the
current query in Access.

SELECT JobClass.Item, Orders.JobClass, Orders.Priority, Orders.OrderID,
Orders.CustomerID, Customers.CompanyName,

Orders.EntryDate, Orders.RcvdDate,

Quotes.QuoteDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.WIPDescription, Orders.OrderDate, Orders.InvoiceCreated,
tblInvoices.OrderID, Orders.Payment, IIf(DCount ("[Quantity]","Order
Details","OrderID = '" & [Orders].[OrderID] &
"'")<>0,CCur((DSum("[Quantity]*[UnitPrice]","Order Details","OrderID = '" &
[Orders].[OrderID] & "'"))-[Orders.Payment]),CCur("0")) AS OrderAmount

FROM Customers INNER JOIN (((Orders LEFT JOIN Quotes ON Orders.OrderID =
Quotes.OrderID) INNER JOIN JobClass ON Orders.JobClass = JobClass.JobClass)
LEFT JOIN tblInvoices ON Orders.OrderID = tblInvoices.OrderID) ON
Customers.CustomerID = Orders.CustomerID

WHERE (((Orders.JobClass)<>"SERVICE JOBS - TO EVALUATE" And
(Orders.JobClass)<>"SERVICE JOBS - WAITING ON CUST" And
(Orders.JobClass)<>"SERVICE JOBS - COMPANY EQUIPMENT NOT SOLD" And
(Orders.JobClass)<>"TRACEABILITY ORDER") AND ((Orders.InvoiceCreated)=False)
AND ((tblInvoices.OrderID) Is Null)) - This last statement is what is
giving me headaches.

ORDER BY JobClass.Item, Orders.Priority;


Anyone have any ideas or what I should search for even.

Thanks,

Fred


.

 

[Fred Newtz]

Van,

This helps quite a bit. I was not sure what I should use for the
DCount. The fields need to be editable, so should I use a Stored Proceedure
or an inline function and pass the [orders].[ordersid]?

I have several queries that used distinctrow and they all need to be
updateable also. I am using Access 2002 and SQL 2000. However, no matter
the settings I put on the properties of the forms they will not update. I
have created a stored proceedure and an inline function and set that as the
record source for the form with the SELECT * FROM qrySPWhatever(@OrderID).
The input parameters property is automatically set. I get the query to work
and show the data properly, it is just not updateable. Are there specific
properties that need to be set on the tables in SQL? What about the
relationships for the tables? They all definately use more than one table
to list data.

Do I need to use ADO for the queries that have DistinctRow, so that I
can add records or update the form?

Thanks,

Fred

There are a number of things that are not straight-forward
conversions to MS-SQL View / SQL String:

1. IIF is an Access function which is not available in MS-
SQL. You need to convert it to MS-SQL CASE Statement.

2. The String delimiter in MS-SQL is SINGLE-quote, so you
need to convert all double-quote to single quotes.

3. DCount is an Access function only. You need to use the
sub-query with Count() function in MS-SQL.

4. CCur is a VBA function only. You need to use eith CAST
or CONVERT function in MS-SQL.

You will probably need to use the MS-SQL Query Analyzer to
help you construct the SQL String. All MS-SQL elements I
mentioned above are available in MS-SQL BOL (Books On
Line).

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am trying to convert a query that did not get converted with the Upsizing
Wizard.

The query needs to return a record only if the primary key is not in another
related table. So basically I am trying to show all of the orders that are
in place that have not been invoiced yet. So I need to be able to check the
tblInvoices table and see if the OrderID is not in there. Here is the
current query in Access.

SELECT JobClass.Item, Orders.JobClass, Orders.Priority, Orders.OrderID,
Orders.CustomerID, Customers.CompanyName,

Orders.EntryDate, Orders.RcvdDate,

Quotes.QuoteDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.WIPDescription, Orders.OrderDate, Orders.InvoiceCreated,
tblInvoices.OrderID, Orders.Payment, IIf(DCount ("[Quantity]","Order
Details","OrderID = '" & [Orders].[OrderID] &
"'")<>0,CCur((DSum("[Quantity]*[UnitPrice]","Order Details","OrderID = '" &
[Orders].[OrderID] & "'"))-[Orders.Payment]),CCur("0")) AS OrderAmount

FROM Customers INNER JOIN (((Orders LEFT JOIN Quotes ON Orders.OrderID =
Quotes.OrderID) INNER JOIN JobClass ON Orders.JobClass = JobClass.JobClass)
LEFT JOIN tblInvoices ON Orders.OrderID = tblInvoices.OrderID) ON
Customers.CustomerID = Orders.CustomerID

WHERE (((Orders.JobClass)<>"SERVICE JOBS - TO EVALUATE" And
(Orders.JobClass)<>"SERVICE JOBS - WAITING ON CUST" And
(Orders.JobClass)<>"SERVICE JOBS - COMPANY EQUIPMENT NOT SOLD" And
(Orders.JobClass)<>"TRACEABILITY ORDER") AND ((Orders.InvoiceCreated)=False)
AND ((tblInvoices.OrderID) Is Null)) - This last statement is what is
giving me headaches.

ORDER BY JobClass.Item, Orders.Priority;


Anyone have any ideas or what I should search for even.

Thanks,

Fred


.

 

[Jack]

-----Original Message-----
I am trying to convert a query that did not get converted with the Upsizing
Wizard.

The query needs to return a record only if the primary key is not in another
related table. So basically I am trying to show all of the orders that are
in place that have not been invoiced yet. So I need to be able to check the
tblInvoices table and see if the OrderID is not in there. Here is the
current query in Access.

SELECT JobClass.Item, Orders.JobClass, Orders.Priority, Orders.OrderID,
Orders.CustomerID, Customers.CompanyName,

Orders.EntryDate, Orders.RcvdDate,

Quotes.QuoteDate, Orders.RequiredDate, Orders.ShippedDate,
Orders.WIPDescription, Orders.OrderDate, Orders.InvoiceCreated,
tblInvoices.OrderID, Orders.Payment, IIf(DCount ("[Quantity]","Order
Details","OrderID = '" & [Orders].[OrderID] &
"'")<>0,CCur((DSum("[Quantity]*[UnitPrice]","Order Details","OrderID = '" &
[Orders].[OrderID] & "'"))-[Orders.Payment]),CCur("0")) AS OrderAmount

FROM Customers INNER JOIN (((Orders LEFT JOIN Quotes ON Orders.OrderID =
Quotes.OrderID) INNER JOIN JobClass ON Orders.JobClass = JobClass.JobClass)
LEFT JOIN tblInvoices ON Orders.OrderID = tblInvoices.OrderID) ON
Customers.CustomerID = Orders.CustomerID

WHERE (((Orders.JobClass)<>"SERVICE JOBS - TO EVALUATE" And
(Orders.JobClass)<>"SERVICE JOBS - WAITING ON CUST" And
(Orders.JobClass)<>"SERVICE JOBS - COMPANY EQUIPMENT NOT SOLD" And
(Orders.JobClass)<>"TRACEABILITY ORDER") AND ((Orders.InvoiceCreated)=False)
AND ((tblInvoices.OrderID) Is Null)) - This last statement is what is
giving me headaches.

ORDER BY JobClass.Item, Orders.Priority;


Anyone have any ideas or what I should search for even.

Thanks,

Fred


.

In an ADP you cannot order by. You will need to take the
view to a stored procedure to set the order by clause.

 

[Van T. Dinh]

I don't think that is correct.

I have the following View in a test ADP (AXP + MS-SQL2K)
with ORDER BY Clause and it works fine.

SELECT TOP 100 PERCENT PFL.ProdFactLineID,
PFL.frg_ProductID, FL.FactLineCode,
PFL.frg_FactLineRank, PFL.frg_CoilerWinderID,
PFL.CalLineRateTarget,
PFL.CalLineLimitingMachine, PFL.CalBaseLineTarget,
PFL.CalBaseLine, PFL.CalBaseLineLimitingMachine
FROM dbo.tblProductFactLine PFL INNER JOIN
dbo.tblFactLine FL
ON PFL.frg_FactLineID = FL.FactLineID
ORDER BY PFL.frg_ProductID, FL.FactLineCode

HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Are you sure you use ADP? There is no "DISTINCTROW" in MS-
SQL Server T-SQL language.

Instead of DCount, you need to use a Sub Query / SQL
String as per my last post.

Can't say I am experienced too much about updatability of
Views (I don't actually use ADPs) but the few I have in a
test ADP are updatable. Calculated Fields are not
updatable even in an updatable View, of course.

HTH
Van T. Dinh
MVP (Access)

 

[Fred Newtz]

I am in the process of upsizing from a MDB file to ADP. The previous query
used DISTINCTROW in a multitable query to allow updates to the data in each
of those tables. So I am looking at the DISTICT keyword, but I am not sure
if it is the correct keyword or not.

Thanks,

Fred

Are you sure you use ADP? There is no "DISTINCTROW" in MS-
SQL Server T-SQL language.

Instead of DCount, you need to use a Sub Query / SQL
String as per my last post.

Can't say I am experienced too much about updatability of
Views (I don't actually use ADPs) but the few I have in a
test ADP are updatable. Calculated Fields are not
updatable even in an updatable View, of course.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Van,

This helps quite a bit. I was not sure what I should use for the
DCount. The fields need to be editable, so should I use a Stored Proceedure
or an inline function and pass the [orders].[ordersid]?

I have several queries that used distinctrow and they all need to be
updateable also. I am using Access 2002 and SQL 2000. However, no matter
the settings I put on the properties of the forms they will not update. I
have created a stored proceedure and an inline function and set that as the
record source for the form with the SELECT * FROM qrySPWhatever(@OrderID).
The input parameters property is automatically set. I get the query to work
and show the data properly, it is just not updateable. Are there specific
properties that need to be set on the tables in SQL? What about the
relationships for the tables? They all definately use more than one table
to list data.

Do I need to use ADO for the queries that have DistinctRow, so that I
can add records or update the form?

Thanks,

Fred

 

[Van T. Dinh]

DISTINCT certainly won't help as an Access Query / SQL
View with DISTINCT does not uniquely identifies the Row /
Record and therefore the Query / View will NOT be
updateable.

HTH
Van T. Dinh
MVP (Access)