Hi
Just joined this forum looking for some help on a small ( 11Meg) Access database.
I do not have a SQL server background
I work in a larger organization where we have a DBA group but they do not support the migration from Access to SQL server. They support the actual database only No query support on a migration.

I have just reworked an Access database to provide additional functionality. We need to share the data Nationally so we want to backend the database on a SQL server to provide all the benefits of SQL server. I have already run through the simple queries and made them Passthrough queries.
The issue I have is I inherited about 15 queries with the same Dlookup in each of them
The Access forms actually have embedded DLookups as well
Looking for a solution that would allow a more cut and paste fix as the Dlookups are all the same
Here is an example of one of the queries:

SELECT SerializedInventory.InventoryStatus, SerializedInventory.UsageStatus, SerializedInventory.InventoryType, SerializedInventory.Manufacturer, SerializedInventory.ModelName, SerializedInventory.SerialNumber, SerializedInventory.PinNumber, SerializedInventory.MACAddress, SerializedInventory.EndofLife, SerializedInventory.Group, InventoryType.Type
FROM InventoryType INNER JOIN SerializedInventory ON InventoryType.TypeID = SerializedInventory.InventoryType
WHERE (((SerializedInventory.InventoryStatus)="undetermined" Or (SerializedInventory.InventoryStatus)="current inventory") AND ((SerializedInventory.EndofLife)=0) AND ((SerializedInventory.Group)=DLookUp("[defswitchboard]","tblDefaults")));

I have already found the downfall of the DLookup's in that they create a large amount of traffic between the workstation and server:mad:
Would really like to get them out of the code as passing the data across the country really slows down the interface response

Thanks
RRII:(

Try

...SerializedInventory.Group= (SELECT defswitchboard FROM tblDefaults)

I wonder if ElookUp functions (http://allenbrowne.com/ser-42.html) with some modification to work with SQL server instead would be worth the investment?

If I understand the goal here correctly, I don't think so. I believe the goal is to have something that can run on the server (pass through query or stored procedure). Allen's function, while excellent, would not be recognized by SQL Server any more than DLookup is. An alternative would be a UDF equivalent on the server, but I suspect the subquery will be just as fast. You could also write a stored procedure that got the value into a variable and then used it.

Thanks- I was kinda of wondering about that one- I know that UDF won't go on servers, but since ELookup used SQL, I thought that would be a possibility.

I know that UDF won't go on servers
Banana:

Can you explain what you mean by this? I'm confused because I use UDF's on a SQL Server all of the time.

Well, presuming you pass the ELookup the same way the DLookup is being passed, the server won't know what it is (since it's not T-SQL). You're trying to call a function that the server doesn't know how to find, because it lives in the Access side. You could write a SQL Server UDF that did the same thing, but I'm not sure you gain anything in this case.

I think he meant a VBA UDF Bob, like ELookup.

Bob- What pbaldy said, I wasn't being precise enough. My apologies.

Pbaldy, that makes sense- even if the function is just vanilla SQL, it doesn't really matter since the key here is the location of function and with Elookup, it's on the wrong side of track, so to speak.

Just to clarify Yes I am doing Passthrough queries to get the workload on the SQL server instead of the Workstation Saves the network traffic back and forth

Well the SubSelect statement works for some of the queries
Thanks PBaldy

The one below is making me scratch my head :eek:
It's a bit deep for my knowledge level
Any help much appreciated!
RRII


SELECT SerializedInventoryTransactions.Notes, qrySerializedInventoryByGroup.InventoryStatus, qrySerializedInventoryByGroup.UsageStatus, qrySerializedInventoryByGroup.ModelName, SerializedInventoryTransactions.DateAllocated, SerializedInventoryTransactions.RecipientName, SerializedInventoryTransactions.AllocateReference, SerializedInventoryTransactions.SerialNumber, qrySerializedInventoryByGroup.WorkstationNumber, qrySerializedInventoryByGroup.Group, qrySerializedInventoryByGroup.EndofLife
FROM qrySerializedInventoryByGroup LEFT JOIN SerializedInventoryTransactions ON qrySerializedInventoryByGroup.SerialNumber = SerializedInventoryTransactions.SerialNumber
WHERE (((SerializedInventoryTransactions.Notes)="Current User") AND ((qrySerializedInventoryByGroup.InventoryStatus)="Allocated to Client") AND ((qrySerializedInventoryByGroup.ModelName)="EVO NC6220") AND ((SerializedInventoryTransactions.DateAllocated)>#1/1/2008#) AND ((qrySerializedInventoryByGroup.Group)=DLookUp("[defswitchboard]","tblDefaults")) AND ((qrySerializedInventoryByGroup.EndofLife)=0))
ORDER BY qrySerializedInventoryByGroup.ModelName, SerializedInventoryTransactions.SerialNumber;

I'm not sure I see what the problem is other than the DLookup. Or is it because you're joining to a query that wouldn't exist in SQL Server? If so, what does that query do?

So here is the query with the Dlookup replaced and I have switched it to a passthrough query
SELECT [SerializedInventoryTransactions].[Notes], [qrySerializedInventoryByGroup].[InventoryStatus], [qrySerializedInventoryByGroup].[UsageStatus], [qrySerializedInventoryByGroup].[ModelName], [SerializedInventoryTransactions].[DateAllocated], [SerializedInventoryTransactions].[RecipientName], [SerializedInventoryTransactions].[AllocateReference], [SerializedInventoryTransactions].[SerialNumber], [qrySerializedInventoryByGroup].[WorkstationNumber], [qrySerializedInventoryByGroup].[Group], [qrySerializedInventoryByGroup].[EndofLife]
FROM [qrySerializedInventoryByGroup] LEFT JOIN [SerializedInventoryTransactions] ON [qrySerializedInventoryByGroup].[SerialNumber] = [SerializedInventoryTransactions].[SerialNumber]
WHERE ((([SerializedInventoryTransactions].[Notes])='Current User') AND ([qrySerializedInventoryByGroup].[InventoryStatus])='Allocated to Client') AND (([qrySerializedInventoryByGroup].[ModelName])='EVO NC6220') AND (([SerializedInventoryTransactions].[DateAllocated])>#1/1/2008#)
AND ([qrySerializedInventoryByGroup].[Group])=(SELECT defswitchboard FROM tblDefaults) AND [qrySerializedInventoryByGroup].[EndofLife]=0
ORDER BY [qrySerializedInventoryByGroup].[ModelName], [SerializedInventoryTransactions].[SerialNumber];

Here is the error I am getting
ODBC Call Failed........
Incorrect Syntax near '#'. (#102) Microsoft SQL Server Driver Sql Server Incorrect snytax near the keyword 'AND' (#156)
Now if I just remove the Dlookup and put the SubSelect in the query runs fine
Hmmmm
RRII

SQL Server will want single quotes around the date rather than #:

[DateAllocated])>'1/1/2008'

Got it
Thanks again PBaldy:)
So I have managed to get through over a dozen of the queries now and transform them to Passthrough's which is great

Now moving onto my next challenges
Have a couple of queries with nz's in them
I know you can convert to ISNULL or COALESCE
I am just not getting the right syntax

Here is an example query

SELECT [NonSerializedInventoryTransactions].[TransactionType], [NonSerializedInventoryTransactions].[TransactionDate], [NonSerializedInventoryTransactions].[ReceiptReference], [NonSerializedInventoryTransactions].[AllocateReference], [NonSerializedInventoryTransactions].[RecipientName], [NonSerializedInventoryTransactions].[UnitsReceived], [NonSerializedInventoryTransactions].[UnitsAllocated], [NonSerializedInventoryTransactions].[UnitsShrinkage], [NonSerializedInventoryTransactions].[Notes], [NonSerializedInventoryTransactions].[ProductNumber], ((nz([UnitsReceived])-nz([UnitsAllocated])-nz([UnitsShrinkage]))) AS CalcUnits0nHand, [NonSerializedInventoryTransactions].[NonSerializedId]
FROM NonSerializedInventoryTransactions
ORDER BY [NonSerializedInventoryTransactions].[TransactionDate];


Second question is with an embedded Form! in the query like this:

SELECT [SerializedInventory].*
FROM SerializedInventory
WHERE ((([SerializedInventory].[SerialNumber])=Forms!frm_SerializedInventory!SerialNumber));


Third question is when referencing another query like this:

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];


How do I get the syntax right?
If I can get an example I can do the coding but my searches are not giving me the answers i need
Sorry for being a problem child


RRII

The T-SQL IsNull function is a direct replacement for Nz, so you should just be able to switch them out (but I'm not sure you can leave off the second argument, so make sure you specify it).

Frankly, simple queries that get a criteria from a form I would probably leave as Access queries. Jet is pretty good about passing the criteria to SQL Server, so you'll only get the matching records coming back over the wire anyway. Your options otherwise both involve interpreting the value from the form control and adjusting the SQL accordingly, using a DAO QueryDef.

Is qrySumUnitsOnHandTotal a pass through query, and is the posted SQL a pass through? You can use a pass through query in an Access query like that, but not in another pass through, since SQL Server knows nothing about it.

I already have 2 problem children, and they're a lot more expensive than you are. :p

Thanks for great feedback
qrySumUnitsOnHandTotal is not a passthrough Query as it gives me grief if I convert it because it is referencing qryUnitsonHandTotal

Here is the syntax for qrySumUnitsOnHandTotal

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

Here is the syntax of qryUnitsOnHandTotal query it is referencing:

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=NonSerializ edInventoryTransactions.NonSerializedId
GROUP BY NonSerializedInventoryTransactions.UnitsReceived, NonSerializedInventoryTransactions.UnitsAllocated, NonSerializedInventoryTransactions.UnitsShrinkage, NonSerializedInventoryTransactions.NonSerializedId , NonSerializedInventory.ProductNumberID, NonSerializedInventoryTransactions.TransactionID;

I believe I can get the forms to load faster once I change the qryUnitsOnHandTOTAL to a Passthrough
As the other two rely on the output from this query
Thanks
RRII

Have you considered making that a View on SQL Server? That might be the most efficient, since it doesn't have parameters. Then you could join to it from pass through queries or link to it and use it directly in the front end.

The Idea of a view in SQL is great but I wanted to tweak the queries as much as possible first if I can. I don't have the SQL experience to create the views myself
I do have SQL Server Management Studio loaded on my laptop so I can do a bit of manipulation. I also loaded the MSSMA and have messed with it a bit but since I had so many Dlookups figured I better get them out of the way first.
Now working on an issue that is stopping input of new records in the NonSerialized form. Need to address it before I tweak any more. It works fine on the Access backended Tables but when we moved to Linked Tables the Add Record is not working
RRII

Got another stumper for me with Dlookups

Have a form with 4 of the following Control Source similar syntax:
=DLookUp("[Manufacturer]","nonserializedproducts","[ProductNumber] = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'")
When the form loads it seems to load these fields the longest
What is the best way to change this syntax to get rid of the Dlookup's?

I tried this but still have the wrong syntax
=(Select ProductNumber FROM nonserializedproducts) = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'"

RRII:confused:

Unfortunately you can't have SQL as the control source of a textbox. Are they different fields from the same record?

What this part of the form does is based on a Product numberID the Dlookups bring up the appropriate info for that product number
So here are the 4 Dlookups with the field names:

Manufacturer
=DLookUp("[Manufacturer]","nonserializedproducts","[ProductNumber] = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'")

Product Description:
=DLookUp("[ProductDescription]","nonserializedproducts","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

Inventory Type:
=DLookUp("[Type]","qryNonSerializedInvTypes","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

Product Notes:
=DLookUp("[Notes]","nonserializedproducts","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

When the form loads it takes about 10 seconds or more on WAN link with a Calculating in the bottom Right
The rest of the form is up and showing only these 4 Dlookup fields are empty until the Calculating is completed
I am thinking it is the Dlookups that are slowing it down because the tables are on the SQL server

So is there a better way to do these than the Dlookups?

RRII:confused:

Why not just include the info in the combo box in fields displayed with 0" and just reference the combo. Should be much faster.

Try this in the Current event:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT Manufacturer, ProductDescription, Type, Notes " _
& "FROM nonserializedproducts " _
& "WHERE [ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Me.Manufacturer = rs!Manufacturer
Me.ProductDescription = rs!ProductDescription
'etc

Set rs = Nothing
Set db = Nothing

Ah, that's what happens when you have to go help a user while typing a response. If the product is in a combo, Bob is correct about getting the values from there.

These particular boxes are text boxes
We did not want the user to be able to change the data in the boxes as it is descriptive to the Product Number
The user can change the Product Number and the Dlookup fields will change with it
I cut and pasted a view of this part of the form
The second attachment is in design view showing the fields with the DLookups

RRII

Then Bob's suggestion is better (I didn't pick up on the combo). In the row source of your combo, include those other fields (presuming they all come from the same table as the displayed field). Make sure you adjust the column count and column width properties, then simply use this as the control source of the textboxes:

=ComboName.Column(x)

where x is the number of the column containing the desired field (and the column property is zero based).

Here is the Row Source info from the Product ID Combo box
SELECT [nonserializedproducts].[ProductNumber] FROM [nonserializedproducts] ORDER BY [ProductNumber];

So I add the Manufacturer, Product Description and Inventory Type columns to the row source of the combo box
Right?
Like This
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[InventoryType], [nonserializedproducts].[ProductDescription], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber];

Then in the Text boxes I change out the Dlookups to ??
I looked for a parameter for the Column count and Column Width but not there for Text Boxes???

Just need a bit of clarity
Been working on this every night and most everyday inbetween my real job duties to try and get it running right
Getting a bit punchy

RRII

Then in the Text boxes I change out the Dlookups to ??

In the After Update event of the combo put:

Me.YourTextBoxName = Me.YourComboName.Column(x)

where x is the column of the data you want in that text box.

As Paul mentioned, it is zero based, so if you want column 2 you would put .Column(1) and if you wanted column 5 you would use .Column(4)

I'm confused
You keep talking about the combo box but only 1 of the boxes is a combo
The first one for Product Number
The 4 below are just text boxes
I understand adding the specific fields to the combo box no problem
I just don't get the mechanics of the Txt boxes


So how does this populate the Txt boxes which are not Combo boxes:confused:
I must be getting thick here

RRII

The code is what assigns the values to the text box from the columns in the combo.

You're letting the combo get all 4 fields for you. All you need in the textboxes is:

=ComboName.Column(x)

to display the values. That will look at the appropriate column of the combo and display the value associated with the chosen value.

So
Here is my Row Source for the combo box
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[InventoryType], [nonserializedproducts].[ProductDescription], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber],[Manufacturer],[ProductDescription],[InventoryType],[Notes];

Here is the Event Proc on the Combo Box

Private Sub Product_Number_ID_AfterUpdate()
Me.DLManufacturer = Me.DLManufacturer.Column(1)
Me.DLManufacturerType = Me.DLManufacturerType.Column(2)
Me.DLInventoryType = Me.DLInventoryType.Column(3)
Me.DLProductNotes = Me.DLProductNotes.Column(4)
End Sub

Here is the Control Source for each Txt Box
DLManufacturer
=[Product Number ID].Column(1)
DLManufacturerType
=[Product Number ID].Column(2)
DLInventoryType
=[Product Number ID].Column(3)
DLProductNotes
=[Product Number ID].Column(4)

So only the DLmanufacturer the first column is populating
Is it my Event Procedure Syntax?

RRII

You want one technique or the other, not both (either unbound textboxes and the code, or no code and the control source in the textbox). If you're only getting the first one populated, make sure the column count property of the combo is 5.

Got it finally Took a bit of understanding that the Select order is the Column count not the Group by order
Got one bit of a surprise in that the Inventory Type is actually populates from a query not the same place as the combo box
So could I create a combo box to pull the data in for the Inventory Type but make it invisible and use the same technic?
My twisted brain logic thinks it should work

RRII

Depending on that query, you might actually be able incorporate it into the single combo query. Post the SQL from both queries so we can see.

Here is the SQL from the Product Number Combo box which now does 3 of the Txt boxes for me
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[ProductDescription],[nonserializedproducts].[InventoryType], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber],[Manufacturer],[ProductDescription],[InventoryType],[Notes];
The Inventory Type is actually a reference number that does not provide the correct information
The Correct Inventory Type information I need is in this Dlookup statement
=DLookUp("[Type]","qryNonSerializedInvTypes","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")
Here is the sql for the qryNonSerializedInvTypes
SELECT nonserializedproducts.ProductNumber, InventoryType.Type
FROM InventoryType INNER JOIN nonserializedproducts ON InventoryType.TypeID = nonserializedproducts.InventoryType;


The Type field actual gives me the right Data
So is there a way to merge the qryNonSerializedInvTypes.Type into the Select from the Combo Box above?
It would be great if we can!
If I can solve this Dlookup I have two left on the this form
Both are stinkers as they are Text boxes as well
I will wait on them until I get this one fixed
These DLookups are just killing this form when you open it on a link like me VPN'd in from home on a Wireless ISP 200-512k link.
But it really gives me the real life of what it would be like on the other side of the country to use!;)
Thanks again for all the great help!:)
RRII

I said SQL from BOTH queries. What is the SQL for qryNonSerializedInvTypes? Not the DLookup you were using it in, what is the actual SQL???

Bob
I added the actual sql for the query into the post above
Sorry It was getting late

RRII

Not 100% certain about the final syntax of this as I usually use the QBE grid, but this shouild get it:

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

When I put the code in the Combo box as the Row Source I get an error in the FROM from the Sql Statement Query Builder in Access
I must be getting thick again
This should be obvious to me
RRII

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

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=qrySumUnits OnHandTotal.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=qrySumUnits OnHandTotal.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!:eek:
RRII

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.SpecialUsageNote s, dbo_V_NonSerialInventoryEndOfLife.Group, dbo_V_NonSerialInventoryEndOfLife.Manufacturer, dbo_V_NonSerialInventoryEndOfLife.ProductDescripti on, dbo_V_NonSerialInventoryEndOfLife.EndofLife
FROM dbo_V_NonSerialInventoryEndOfLife


Any thoughts?

thanks
RRII