Circular Reference with calculated Query Field

[figment222]

Hello, i'm kinda new to access, so excuse me if my problem here is poorly-worded.

I have a form that tracks Jobs and a subform that tracks various items for each Job and the status for each: Completed, elapsed minutes, etc. Each item might require additional Processes and I want to know how long it took for each item as they are completed. So, essentially, I want a subtotal for each item, which includes how long each of of the processes took.

The subform is fed by a query called "qry_MetalsItemsActive" with a field for the time it takes to complete a process. Each process has a unique ID. When a process is part of a larger assembly, we have a field called ParentID, which will be equal to whatever the ID was of the parent.

An example:
Record1, ID=1, ParentID=Null
Record2, ID=2, ParentID=1
Record2, ID=3, ParentID=1

unfortunately, the creator of this table only populated the ID in the ParentID field if it was not the Parent, so some are blank and some are not. I've used this to identify blanks as Parent records and non-blanks as child records.

So, I created a new field in the main query called AssyID: IIf(IsNull([ParentID]),[ID],[ParentID])

So, in my example
Record1, ID=1, ParentID=Null, AssyID=1, minutes=20
Record2, ID=2, ParentID=1, AssyID=1, minutes=30
Record2, ID=3, ParentID=1, AssyID=1, minutes=10

Now, I can sort by this field in the subform datasheet view and see each record and identify the ones that are part of the same assembly, without grouping them together. BUT, I still only see how long each record took, NOT a subtotal of 60 minutes.

I want another field that tells me the subtotal for the assembly: 60 minutes

Then, I created another query called "qry_MetalsItems_MinPerAssy", where I group by the AssyID and then created a calculated field:
MinPerAssy: Sum([ElapsedTimeEach]/[quantity])

This works well to give me how many minutes it took for each assembly.

My problem is that I can't seem to bring this field BACK into the query that feeds my subform. I open my qry in DES view and add the new qry with the MinPerAssy field, but when I try to INNERJOIN, it tells me there is a circular reference. I've created a relationship in the qry DES window between the two [AssyID] fields.

I even tried circumventing it and using a DLOOKUP, but I can't get it to work: here's what i have in the control source for that field:
=DLookUp("MinPerAssy","qry_MetalsItems_MinPerAssy","AssyID='" & [Forms]![qry_MetalsItemsActive subform]![txtAssyID] & "'")

I've been working on this for DAYS! please tell me what's wrong.

 

[xenou]

your last idea is always a possibility and I've used that approach before as well. Maybe you can post the queries that source your form? I don't really understand what you mean by another query that references the first query to get a value back into the original query. I do that all the time without circularity (though normally I do use SQL Server these days, which can sometimes handle queries that bog Access down).

 

[figment222]

@xenou, Thank you for continuing to help me troubleshoot this!

here is the SQL code for the query that feeds the subform:
(This is taken from the Control Source field while the subform is open in design view)
SELECT qry_MetalsItemsActive.AssyID, qry_MetalsItemsActive.Item, qry_MetalsItemsActive.ItemType, qry_MetalsItemsActive.Quantity, qry_MetalsItemsActive.Machinist, qry_MetalsItemsActive.ElapsedTime, qry_MetalsItemsActive.ElapsedTimeInMin, qry_MetalsItemsActive.OperationComplete, qry_MetalsItemsActive.Outsource, qry_MetalsItemsActive.DateOut, qry_MetalsItemsActive.DateIn, qry_MetalsItemsActive.Completed, qry_MetalsItemsActive.JobNum, qry_MetalsItemsActive.Location, qry_MetalsItemsActive.ElapsedTimeEach, qry_MetalsItemsActive.AvgMinutes, qry_MetalsItemsActive.MinPerAssyEach
FROM qry_MetalsItemsActive
ORDER BY qry_MetalsItemsActive.AssyID, qry_MetalsItemsActive.ItemType;

It is basically taking the fields I want from that query and sorting them by the AssyID to help me group the parents and children. Then it sorts by Item Type (Main = Parent, Sub = Child)

Here is the SQL for qry_MetalsItemsActive
SELECT [qry_MetalShopTRC >> JobNum].JobNum, IIf(IsNull([ParentID]),[ID],[ParentID]) AS AssyID, dbo_tbl_MetalShopItems.ID, dbo_tbl_MetalShopItems.ParentID, dbo_tbl_MetalShopItems.Item, IIf([ID]=[AssyID],"Main","Sub") AS ItemType, dbo_tbl_MetalShopItems.Location, dbo_tbl_MetalShopItems.Quantity, dbo_tbl_MetalShopItems.Machinist, dbo_tbl_MetalShopItems.ElapsedTime, dbo_tbl_MetalShopItems.ElapsedTimeInMin, Round([ElapsedTimeInMin]/[Quantity],1) AS ElapsedTimeEach, DLookUp("MinPerAssy","qry_MetalsItems_MinPerAssy","AssyID='" & [AssyID] & "'") AS MinPerAssyEach, qry_MetalsItemsAverages.AvgMinutes, dbo_tbl_MetalShopItems.OperationComplete, dbo_tbl_MetalShopItems.Outsource, dbo_tbl_MetalShopItems.DateOut, dbo_tbl_MetalShopItems.DateIn, dbo_tbl_MetalShopItems.Completed
FROM qry_MetalsItemsAverages RIGHT JOIN ([qry_MetalShopTRC >> JobNum] INNER JOIN dbo_tbl_MetalShopItems ON [qry_MetalShopTRC >> JobNum].[TRC #] = dbo_tbl_MetalShopItems.[TRC #]) ON qry_MetalsItemsAverages.MetalShopItem = dbo_tbl_MetalShopItems.Item
ORDER BY IIf(IsNull([ParentID]),[ID],[ParentID]);

This one uses a query called: qry_MetalShopTRC >> JobNum that converts the TRC# to the JobNumber. (The author of the dbo_tbl_MetalShopItems SQL Table decided to concatenate the First 3 letters of the customerID with the JobNumber (which we call a TRC#, which we use to link up information all over our database. So, in order to get things to match up, I use that query to shave off the customerID and isolate the TRC# from (example) "MAR 44925ab" to just "44925ab".

it also includes another one called: qry_MetalsItemsAverages that groups by the item category: (Pin, Insert, whatever), so I can get a running average of the time it takes to produce each item category. This way, I can use that average to predict how long it might take to produce one that is in the queue. (If each pin takes 30 minutes and I have 2 more pins to make, then I know I'll need about 60 minutes to get these 2 Pins made).

Otherwise, from this query: qry_MetalsItemsActive has a few calculated fields, which I highlighted with RedText above in the SQL code. One of them is the AssyID. Another one is ElapsedTimeEach, which just accounts for multiples in the qty field, so if it took 20 minutes to do 4 of them, then I know it took 5 minutes each. THAT is what I want to consider when calculating the average time taken for each Item category, but since some of these items have children processes (or Subs), I need to make sure I include the time it took to do them and since I couldn't figure out a way within this query to get a subtotal of the ElapsedTimeEach for each unique AssyID, without grouping the query and losing the ability to see the details on the subform for the child processes, I decided to do another query: qry_MetalsItems_MinPerAssy

Here is the SQL for qry_MetalsItems_MinPerAssy
SELECT qry_MetalsItemsActive.JobNum, qry_MetalsItemsActive.AssyID, Sum(qry_MetalsItemsActive.ElapsedTimeEach) AS MinPerAssy
FROM qry_MetalsItemsActive
GROUP BY qry_MetalsItemsActive.JobNum, qry_MetalsItemsActive.AssyID;

So, I'm grouping by JobNum and then AssyID, so I only get 1 record for each AssyID. Then, I calculate the sum of ElapsedTimeEach and call it MinPerAssy.

I still can't figure out why I can't bring this new field MinPerAssy back into qry_MetalsItemsActive, without either a circular reference or sacrificing a HUGE performance hit with a DLOOKUP

My form was already loading slowly BEFORE all this, which makes me wonder if I shouldn't be using these queries to populate a junction table and using THAT as my control source for the subform.

 

[xenou]

Well at least I see the circularity now. Seems like qry_MetalsItems_MinPerAssy is joining qry_MetalsItemsActive and vice versa. They can't both be subqueries for each other simultaneously (A depends on B and B depends on A). You are supposed to make one of them (and only one of them) a subquery to the other.

I don't see any ID's in your queries. It can certainly cause slowness if the queries have to create records for every single job in the database every time you display the form.

But I'm not sure what your form is meant to do. Is it supposed to display only one job or all jobs (if job is the right word here)?

 

[figment222]

Well, each job has items and each item can be either a Main (Parent) or a Sub (Child), so the form is supposed to display all the information about a single job. The Main form references a query of Jobs, which provides general information, while the subform provides all the details about each item the Job is comprised of.

I'm trying to show the status for the job as it progresses through our system. Example: "This job has 12 out of 14 items completed. The remaining 2 items are Pins and since Pins take 30 minutes on average, then it should take us about another hour of labor to get the rest of the items for this job completed"

Regarding the ID's; There are multiple ID's in qry_MetalsItemsActive. These are the first 4 columns of the query:
JobNum: like the TRC#, this is a unique string for each job. Since each Job can have many items, I need JobNum, because that is the Child Field Link for the subform. (TRC # is the Master Field Link). JobNum can appear many times in the list of items.
AssyID: As mentioned before, this is the field used to group the children to the parent records
ID: This is a unique identifier for each item, whether or not it is part of an assembly.
ParentID: This is only populated for items that need a child record. For any child record, this field would have the ID of the parent record.
This is why I used the formula I did to get the AssyID: "If ParentID is blank, then I know THIS record is the parent, so for this record, AssyID = ID. If ParentID is NOT blank, then I know this is a child record. For this record, AssyID = ParentID

If I have 3 records (Items) with one parent and 2 children, I would have 1 Assembly.
Record 1, where ParentID = Null. AssyID = ID
Record 2, where ParentID = NotNull. AssyID = ParentID
Record 3, where ParentID = NotNull. AssyID = ParentID

Now I have 3 records with the same AssyID, where previous to the creation of the AssyID field, I had no way of grouping them together.

I'm not sure I understand what you said about causing slowness when the queries have to create records for every single job in the database every time the form is displayed.

Regardless, I finally found a way around it by creating yet another query to calculate some of these fields separately, basically a junction query. This query creates the AssyID field, as well as the ItemType (Main vs Sub) and some others. Now, I can reference AssyID from THIS query, when Grouping by AssyID and summing the ElapsedTimeEach as MinPerAssy. Then, I can reference MinPerAssy in qry_MetalsItemsActive without the circular reference. I just hate having a maze of queries, but I don't see another way around it.

Here's the SQL for the "Junction" query, in case anyone else might stumble upon this:
SELECT dbo_vwFetchAllMetalShopItems.[TRC #], dbo_vwFetchAllMetalShopItems.Item, IIf([ID]=[AssyID],"Main","Sub") AS ItemType, dbo_vwFetchAllMetalShopItems.ID, dbo_vwFetchAllMetalShopItems.ParentID, IIf(IsNull([ParentID]),[ID],[ParentID]) AS AssyID, dbo_vwFetchAllMetalShopItems.ElapsedTimeInMin, dbo_vwFetchAllMetalShopItems.Quantity, Round([ElapsedTimeInMin]/[Quantity],1) AS ElapsedTimeEach, dbo_vwFetchAllMetalShopItems.Completed
FROM dbo_vwFetchAllMetalShopItems
ORDER BY dbo_vwFetchAllMetalShopItems.[TRC #], IIf(IsNull([ParentID]),[ID],[ParentID]);



Thanks again for the help.

 

[xenou]

I'm not sure I understand what you said about causing slowness when the queries have to create records for every single job in the database every time the form is displayed.

To explain this simply, let's say I bind a form to a table with Items - very simply, my data source for the form is "select * from Items". BUT my item table has 5,432,212 items in it. To make things worse, the database is on a remote server on the other side of the USA. Well, it could take a while for that form to populate because I'm potentially grabbing 5 million records every time I open the form and waiting for them to arrive via HTTP across multiple networks.

Let's say instead I want to show only the records for items that were shipped yesterday. To really optimize this, I use a stored procedure in SQL Server that is located on the remote server. The form is bound to the results of the stored procedure. Now instead of 5,000,000+ items I am only interested in the 5,454 that shipped yesterday and my form has much fewer records to display -- 3 orders of magnitude fewer. So it should be a much more efficient form.

That's what I mean by not looking at every record in the database. If you can look at a small subset of records from what would otherwise be an extremely large set of records, then you should get improved performance, especially when working with remote servers rather than locally stored data on your own pc.

In short, you want to grab only the data you need, and avoid grabbing a lot of data that you don't need.

Note that in this particular case, if you could limit your query(s) to results for one job, and of course use those job numbers and related primary keys and foreign keys for that job only, then it should be faster than a query that has to make calculations on many jobs.