Converting DLookups in Access DB Backended in SQL Server (1 Viewer)

Well, considering TYPE is an Access reserved word that could have something to do with things. (make sure you don't use reserved words for object/field names).

See here for a list:
http://support.microsoft.com/kb/q286335/

Try using your original query for the combo box in the QBE grid and then add the other query into the query as a table and then link the two together on the TypeID.

I got it

The FROM statement cannot have two variables
Here is the updated SQL which works great so far!
Figured I would post it for reference if anyone ever follows this thread

SELECT nonserializedproducts.ProductNumber, nonserializedproducts.Manufacturer, nonserializedproducts.ProductDescription, nonserializedproducts.InventoryType, nonserializedproducts.Notes, InventoryType.Type
FROM InventoryType INNER JOIN nonserializedproducts ON InventoryType.TypeID = nonserializedproducts.InventoryType
ORDER BY nonserializedproducts.ProductNumber, nonserializedproducts.Manufacturer, nonserializedproducts.ProductDescription, nonserializedproducts.InventoryType, nonserializedproducts.Notes;

Thank you very much for the help on this one
Now I have two text boxes left with Dlookup calculations against queries to get fixed
I am thinking they are the culprits making the form slow to come up
It sits with Calculating.... for about 20-30 seconds on my slow connection
I am thinking I may have to do the same trick with a combo box but hide the combo in the form just to get the results out of the text boxes without the DLookups
I'm going to work away at this and see if I can make it work
Thanks again

RRII

More fun with Dlookups and enbedded queries

Sorry this just keeps getting deeper and deeper
The form I am working on has a subform in the Centre of the form
The record source for it is a query that uses a second query as a source that uses a third query as its source!
Original record Source query
qrynonserializedproducttotalsbysite

SQL for qrynonserializedproducttotalsbysite

SELECT NonSerializedInventory.ProductNumberID, NonSerializedInventory.PhysicalLocation, NonSerializedInventory.NonSerializedID, qrySumUnitsOnHandTotal.SumOfCalcUnits0nHand
FROM NonSerializedInventory LEFT JOIN qrySumUnitsOnHandTotal ON NonSerializedInventory.NonSerializedID=qrySumUnitsOnHandTotal.NonSerializedId
ORDER BY NonSerializedInventory.ProductNumberID, NonSerializedInventory.PhysicalLocation;

Sql for qrySumUnitsOnHandTotal

SELECT qryUnitsOnHandTOTAL.ProductNumberID, Sum(qryUnitsOnHandTOTAL.CalcUnits0nHand) AS SumOfCalcUnits0nHand, qryUnitsOnHandTOTAL.NonSerializedId
FROM qryUnitsOnHandTOTAL
GROUP BY qryUnitsOnHandTOTAL.ProductNumberID, qryUnitsOnHandTOTAL.NonSerializedId;

Sql for qryUnitsOnHandTOTAL
SELECT Sum(((nz([UnitsReceived])-nz([UnitsAllocated])-nz([UnitsShrinkage])))) AS CalcUnits0nHand, NonSerializedInventoryTransactions.UnitsReceived, NonSerializedInventoryTransactions.UnitsAllocated, NonSerializedInventoryTransactions.UnitsShrinkage, NonSerializedInventoryTransactions.NonSerializedId, NonSerializedInventory.ProductNumberID, NonSerializedInventoryTransactions.TransactionID
FROM NonSerializedInventory INNER JOIN NonSerializedInventoryTransactions ON NonSerializedInventory.NonSerializedID = NonSerializedInventoryTransactions.NonSerializedId
GROUP BY NonSerializedInventoryTransactions.UnitsReceived, NonSerializedInventoryTransactions.UnitsAllocated, NonSerializedInventoryTransactions.UnitsShrinkage, NonSerializedInventoryTransactions.NonSerializedId, NonSerializedInventory.ProductNumberID, NonSerializedInventoryTransactions.TransactionID
ORDER BY NonSerializedInventoryTransactions.TransactionID;

No wonder the form is taking time to load with this many sublevels!
So what is the best way to tackle this one?
I am thinking I need to get rid of the two subqueries
Because of all the linking the queries are not candidates for passthorough's
On top of this form there are still the two text boxes with the DLookups to be addressed

Calculations scripts
First Text Box

=DLookUp("[SUMofCalcUnits0nHand]","qrynonserializedproducttotalsbysite","[NonSerializedID] = " & "[Forms]![frm_NonSerializedInventory]![NonSerializedID]")

SQL from qrynonserializedproducttotalsbysite
SELECT NonSerializedInventory.ProductNumberID, NonSerializedInventory.PhysicalLocation, NonSerializedInventory.NonSerializedID, qrySumUnitsOnHandTotal.SumOfCalcUnits0nHand
FROM NonSerializedInventory LEFT JOIN qrySumUnitsOnHandTotal ON NonSerializedInventory.NonSerializedID=qrySumUnitsOnHandTotal.NonSerializedId
ORDER BY NonSerializedInventory.ProductNumberID, NonSerializedInventory.PhysicalLocation;

Second Text Box

=DLookUp("[SUMofSUMofCalcUnits0nHand]","qrynonserializedproducttotalsbyproductNumber","[ProductNumberID] = " & "[Forms]![frm_NonSerializedInventory]![ProductNumberID]")

SQL from qrynonserializedproducttotalsbyproductNumber

SELECT [NonSerializedInventory].[ProductNumberID], Sum(qrySumUnitsOnHandTotal.[SumOfCalcUnits0nHand]) AS SumOfSumOfCalcUnits0nHand
FROM NonSerializedInventory INNER JOIN qrySumUnitsOnHandTotal ON [NonSerializedInventory].[NonSerializedID] = qrySumUnitsOnHandTotal.[NonSerializedId]
GROUP BY [NonSerializedInventory].[ProductNumberID]
ORDER BY [NonSerializedInventory].[ProductNumberID];





The problem child again!
RRII

Trying to Merge qry's together

So In looking at the queries I quoted above
Is there an easier way to take a calculated value and sum it based on the ProductNumberID
In other words we have multiply transactions and transactionID's for each product number We put stock in and pull stock out.
I want to get a running total of the stock on hand based on Part number from multiply sites
I would really like to get this down to 2 or even 1 qry if I can

Thanks again
RRII

So I took one of the suggestion and converted all the Access Queries to Sql Views and redid each of the Access queries to reference the appropriate SQL view
That way I don't have to redo all the Access forms as well
Now I have 1 access query that if I reference it to the SQL view I cannot change a true/false field in a form. Only 1 just to make me crazy
The sql view will allow me to change the field but when I bring the view into an access query it will not
here is the original Access SQL code

SELECT NonSerializedInventory.*, nonserializedproducts.*
FROM nonserializedproducts INNER JOIN NonSerializedInventory ON nonserializedproducts.ProductNumber = NonSerializedInventory.ProductNumberID
WHERE (((NonSerializedInventory.EndofLife)=1))
ORDER BY nonserializedproducts.ProductNumber;

Here is the SQL View Code

SELECT TOP (100) PERCENT dbo.NonSerializedInventory.NonSerializedID, dbo.NonSerializedInventory.ProductNumberID,
dbo.NonSerializedInventory.PhysicalLocation, dbo.NonSerializedInventory.MinimumQTY, dbo.NonSerializedInventory.RMReserved,
dbo.NonSerializedInventory.ReceiveLog, dbo.NonSerializedInventory.PONumber, dbo.NonSerializedInventory.DoNotRestock,
dbo.NonSerializedInventory.InventoryStatus, dbo.NonSerializedInventory.UsageStatus, dbo.NonSerializedInventory.SpecialUsageNotes,
dbo.NonSerializedInventory.[Group], dbo.nonserializedproducts.Manufacturer, dbo.nonserializedproducts.ProductDescription,
dbo.NonSerializedInventory.EndofLife
FROM dbo.nonserializedproducts INNER JOIN
dbo.NonSerializedInventory ON dbo.nonserializedproducts.ProductNumber = dbo.NonSerializedInventory.ProductNumberID
WHERE (dbo.NonSerializedInventory.EndofLife = 1)
ORDER BY dbo.NonSerializedInventory.ProductNumberID

Here is the modified Access Query pointing to the SQL View

SELECT dbo_V_NonSerialInventoryEndOfLife.NonSerializedID, dbo_V_NonSerialInventoryEndOfLife.ProductNumberID, dbo_V_NonSerialInventoryEndOfLife.PhysicalLocation, dbo_V_NonSerialInventoryEndOfLife.MinimumQTY, dbo_V_NonSerialInventoryEndOfLife.RMReserved, dbo_V_NonSerialInventoryEndOfLife.ReceiveLog, dbo_V_NonSerialInventoryEndOfLife.PONumber, dbo_V_NonSerialInventoryEndOfLife.DoNotRestock, dbo_V_NonSerialInventoryEndOfLife.InventoryStatus, dbo_V_NonSerialInventoryEndOfLife.UsageStatus, dbo_V_NonSerialInventoryEndOfLife.SpecialUsageNotes, dbo_V_NonSerialInventoryEndOfLife.Group, dbo_V_NonSerialInventoryEndOfLife.Manufacturer, dbo_V_NonSerialInventoryEndOfLife.ProductDescription, dbo_V_NonSerialInventoryEndOfLife.EndofLife
FROM dbo_V_NonSerialInventoryEndOfLife


Any thoughts?

thanks
RRII

I would create a function once and for all.
attach to Schema = dbo
repeat for several Return DataTypes and each database you have

now you can search and replace
DLOOKUP(
with
[dbo].[DLOOKUP](
tweaking at a minimum

CREATE FUNCTION [dbo].[DLOOKUP_INT]
(@ColumnName NVARCHAR(50),
@FQTN NVARCHAR(255),
@Pattern NVARCHAR(450)
)
RETURNS INT
AS
BEGIN
RETURN (
(SELECT @ColumnName
FROM @FQTN
WHERE @Pattern
)
END;
GO

Forgive the bugs I am typing from memory. I usually create each new function by editing a copy of a similar 1

This :banghead: represents my search for the option to post my avatar to my account page

I've been trying to follow this thread as I am about to do my first SQL offsite. It seems to me that to the goal of pass through is to reduce the net traffic.

So why not just use Pass Through Queries as the root query and then just continue using whatever method you need to populate forms?

And treat the pass through queries as tables.