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]

I think I'm lost on your IDs. Your descriptions include parents and children (that's two things). Your data shows more: ParentID, ID, Record (I.e. "Record1", "Record2"), and AssyID (that's four things).

I think you have to draw this back down to your two basic types of records, parents and children. Otherwise, if you want to have record numbers and AssyIDs too then you have to define what they are and explain what those fields mean.

You also have to decide if a record that has a null ID is stand-alone (an item without a parent or a parent without an item), or logically a parent-child pair (a parent that is it's own child and a child that is it's own parent) - the reason for this is that if it's stand alone then the operation to get all items is a union of two types of records (the children of parents that have children and also all parents without children). If it's logically parent-child pairs then you simply get all children. I just think this is a matter of mindset but its an important part of the mindset. For instance, in the latter case, there really are no null parent IDs because all items have parents.

Also, I don't know what forms have to do with any of this. You should be able to do this without a form. You only need queries. Yes, forms eventually of course of course - but think about your DATA, not your forms. Write a query first.

 

[figment222]

Thank you for the reply and the advise. I wish I could adjust the original tables to populate these fields differently, but unfortunately, I cannot, because they are linked SQL tables. So, I'm stuck working with their original design and trying to accommodate my needs via queries. Not ideal, but doable, as long as I can keep it all straight in my head long enough to build the queries I need. To your point, I agree the queries need to come first, BUT, I was able to successfully build a query that correctly summed them in the way I needed to via Query with totals that groups by the AssyID and another field that sums the minutes, so I get one record for each AssyID that gives me 60 minutes for all 3 original records with that AssyID.

Again, my problem is bringing that field back into the query. I don't see why I'm getting a circular reference error. Let me explain these fields a little better:

The Query feeding my subform is: "qry_MetalsItemsActive". Here are the relevant fields:
- ID: this is the primary key and unique identifier provided via the SQL table. An example: {000FC569-FD17-E811-A1A6-000C29DA175A}
Each record has an ID and is considered an "Item", hence the name of my query:
- ParentID: IF the record is a child, it will have the same ID as it's parent. If it is a Parent record, then this field will be blank. If I had set this up, i would have populated the ID in this field for Parent records. That way, ALL records would have a ParentID field that could be grouped together. Since they were not, I needed another field called:
- AssyID: This is the field I created in the query that would give me the same ID for the Parent and all the Child records. So, if there was one parent and two children, I would have 3 records with the same AssyID.
IIf(IsNull([ParentID]),[ID],[ParentID])
(If the ParentID for this record is blank, then make this the ID, but if the ParentID is NOT blank, then I know it is a child, so give me the parentID, so I can group them together)

I couldn't think of a way to subtotal the minutes for each AssyID within the query, so I created another query called: "qry_MetalsItems_MinPerAssy"
I brought in the previously created field: AssyID from the qry_MetalsItemsActive
I clicked the "Totals" button in the ribbon and chose the AssyID field to group by.
Then I created another field in this query: "MinPerAssy: Sum([ElapsedTimeEach]/[quantity])"
Simple enough. It did exactly what I wanted. I see 1 record for each assembly and how many minutes it took to get it done, whether the assembly was on it's own, or was comprised of 1 or more child items.

I hope this explains them a little better. My thought was to bring the new field "MinPerAssy" from the separate query back into qry_MetalsItemsActive, but I can't figure out why it won't work. both queries have the same field "AssyID" and I thought I could just show the new grouped query, link the two AssyID fields in design view and then add the new "MinPerAssy" field to the grid. Why won't that work?

If my new query has a "MinPerAssy" field that says 60 for this particular AssyID that really consisted of 3 records, then I realize bringing this field back into the original query would report 60 as the MinPerAssy for all 3 of those records. That's fine. I have another field that differentiates the Parent from the Child. (Listing each record as either Main or Sub, where Main=Parent and Sub=Child). I would only use the 60 value for records where that field said "Main".

let me post a screenshot that might help:

https://drive.google.com/file/d/11ux...ew?usp=sharing
https://drive.google.com/file/d/11ux...FDnAeQAHq/view

I guess you can't just insert an image without a URL. I tried the insert image thing, but I doesn't seem to work. Hopefully, you can just click one of those links to see the screenshot.

 

[figment222]

Ok, I figured out a workaround, but I'm not sure it's the best way, because I seem to be taking a pretty serious performance hit over this. I wound up bringing the "MinPerAssy" field from the qry_MetalsItems_MinPerAssy back into the original query: qry_MetalsItemsActive by just creating another calculated field with a DLookup

MinPerAssyEach: DLookUp("MinPerAssy","qry_MetalsItems_MinPerAssy","AssyID='" & [AssyID] & "'")

Then I was able to include this field in the subform pretty easily.

using a DLOOKUP in a query field like this doesn't seem like the best way. I still don't understand what the best way actually WOULD be, though. It brings another subquestion to mind... "Is it better to use queries with calculated fields that run every time I load or change the form, or would it be better to create a new table and convert the queries from select queries to UPDATE queries. Then, I would use the new table as the control source for the subform and only have to use the queries to add new or update existing records.

Seems like all these queries and calculations are bogging down the performance when loading this form and selecting different records...

 

[xenou]

Your images shows three records that sum to 120, not 60. So I'm not sure what you are trying to show. I also do not see any AssyIDs in there. For that matter, there appears to be about a dozen rows in the form, not just three. Not sure what you are trying to accomplish in that form. Is it supposed to be all of your items, or just one group of related items?

 

[figment222]

Sorry about that. The value of 60 in the original post was me trying to generalize an example. You are correct that 120 is the value I'm looking for as the TOTAL minutes for that assembly: (45 + 45 + 30)

As we've gone deeper into this, the generalized example needed to a bit more specific.

AssyID in the subform is hidden, because {000FC569-FD17-E811-A1A6-000C29DA175A} would be meaningless to the user. It is merely to associate records together that would otherwise be isolated. It is a sortfield in the query, which allows me to see the parent records and if any child records exist for that parent, they'll be directly below until the next parent record. I included the field in the subform, but i put it in the footer, which is hidden.

In my example, mentioning the other rows would have confused things, so I thought it would be best to simplify.

After the workaround to get me the info I wanted, I have created another screenshot to help illustrate what I was after:
https://drive.google.com/file/d/1BxdeE99LgRWTMj_j8f4R3OC4evRIb4Qa/view

In the boxes highlighted in Red, you'll see the new field: MinPerAssyEach. It only populates for records where it is the Parent (Type="Main"). This particular job had a number of parents, but only one of them had children and the sum of minutes for that one and the two children was 120 minutes. That's what I wanted in that field. Now, I can sum the MinPerAssyEach to get the total elapsed for the entire job. "I've spent 1,244.375 minutes so far on this job". (I need to widen the textbox, because it is cutting off the preceding "1" and also adjust the formula for that field to account for one of items having a quantity of 4, but you get the gist.

Still... using the DLOOKUP seems to have drastically slowed my performance and I'm wondering if there is a way to do this apart from DLOOKUP (a function which I have found to be a bit touchy for other reasons, as well)

 

[xenou]

Just a note: your link above requires a sign in (before with the other two links it didn't). Can you fix that?

 

[figment222]

Ok. Sorry. Try again:
https://drive.google.com/open?id=1BxdeE99LgRWTMj_j8f4R3OC4evRIb4Qa
https://drive.google.com/file/d/1BxdeE99LgRWTMj_j8f4R3OC4evRIb4Qa/view

 

[xenou]

This seems to somewhat get back to my original comment which was why not write a query that gets you this data right from the start. You seem to be using a query that depends on having information in your form first (hence, the circularity). I would prefer to have the form based on a data source that has the information you need. By data source, I mean, a query that includes the items and the summed values you seek already there.

However, it hard to really say without examining how your form is actually populated. Note that DLookup() would be especially slow if every time it calculates it needs to round trip back to SQL Server, sum the values from the original remote tables, and return a value back to the form.

If this is meant to be real time data you can grab info related to what you are looking at, then work with it locally. If this is somewhat static data (such as finished jobs or jobs as of the previous day), you could potentially crunch data at night and report results based on some preprocessed results (i.e., a sort of data warehousing approach).

A similar concept might be to create a stored procedure that gets what you need. If the stored proc is right in SQL that's ideal, but if it's in MSAccess then you just imitate a stored proc with MSAccess queries and/or temporary tables to store results for local use.

These aren't solutions, just ideas. I would hazard to guess that using DLookup does generate a new call to the server - possibly many new calls for every row where it appears in the form. so that may be why it's slow.

 

[figment222]

Having a single query feeding the subform all the Item info I needed is exactly what I've been trying to do, but when some of those items are parents/children, the need arose to get a subtotal of the minutes for each. I'm all for having all the fields in queries BEFORE dealing with the subform. In fact, that was my goal and that is also what I've achieved (although not so elegantly with the DLOOKUP field slowing it down).

The query as it stands does not reference the form. The main form has some calculated fields that reference calculated fields on the subform (this is how I get a count of completed vs totals for each item, such as Pins, Towers, etc)

I'm getting circularity because I'm having to use another query to give me a subtotal for a field that I need to bring back into the original query. I have an ElapsedTimeEach that gives me how many minutes the user applied to that record, but when it is a child of a parent record, I need the sum for both the parent AND child records. The combination of the Parent + Children = Assembly. This is why I created the AssyID. It gives me a common identifier for the Parent Record and (If any) Child Records with that same ParentID.

It made sense to me that I should create a new query to group by the AssyID and get a sum of the elapsed minutes. I just don't understand why it has to be so hard to get that value back into the original query. It seems if i reference the original table in the new query, then I can't bring a field from the new query back into the original. Does that make sense? Maybe a junction table...

Maybe I should use the new query as an update query to populate a separate table? would that bypass these circular reference issues and eliminate the performance hit?