Using a calculated field in relation between tables

[SunTop]

Hello everyone

I have a query in my database, in that query a column is calculated based on another column in the same query. I wanted to link (link here means create a relation) this query to another table in my database and create a second query based on the first query and the linked table, the field in the first query that I will use to link to the table in the database is the calculated column in the first query, but when I run the second query a message appeared saying that there are no current records, but this is not true, there are matching records in the first query and the linked table.
This seems a little confusing to let me try to explain it in another way:



query1
field1
field2
field3 <- calculated using an expression based on filed2

table1
field1
field2

To create query2 I linked query1 to table1 using field3 from query1 to filed1 from table1

Is the reason that why am getting no current records are found is that you cannot use a calculated field in the relation between tables ?

I hope this makes sense !!!!

[JoeM]

Can you post small samples of your data table and query, so we can see what the data in these fields looks like?

[CJ_London]

Is the reason that why am getting no current records are found is that you cannot use a calculated field in the relation between tables ?

no, chances are your calculation is wrong

[SunTop]

Sorry it took long time to upload the sample database, I was trying to make sure that I did not miss something, I tried everything I can think of to get this query to work, but am missing something here.

I am trying to create a new query based on the RoadMaintenanceDayWorkCenter1PartThree query and the Calendar table, I named the new query RoadMaintenanceDayWorkCenter1PartFour, but as you can see in the sample database, when I try to run the query I get an error no current records found, my head is spinning now, help please

[CJ_London]

whatever it is regret I don't have time to track it all back, but at some point in your massive chain of queries you are not returning any fields. Go back to a previous one and open in design view. All you will see is an asterix, not all the field names

[JoeM]

I think it might have something to do with the WorkingDays2 code you have, that uses RecordSets.

I did some testing, and if I write the results of RoadMaintenanceDayWorkCenter1PartThree out to a Table (using a Make Table Query), and then used this table instead of RoadMaintenanceDayWorkCenter1PartThree in your RoadMaintenanceDayWorkCenter1PartFour query, it worked without error.

I have seen weird things with access, when you nest a lot of queries, especially if you have User Defined Functions involved. I believe that Access tries to re-order the queries to make them more efficient, and it sometimes causes unexpected errors, like you are seeing.

I wish I could pinpoint exactly what the issue is, but sometimes that is like looking for needles in a haystack, especially if there is a lot going on.

[JoeM]

whatever it is regret I don't have time to track it all back, but at some point in your massive chain of queries you are not returning any fields. Go back to a previous one and open in design view. All you will see is an asterix, not all the field names

I was getting that too, until I enabled the VBA code in the project. Note that the queries use a UDF.
Once I enabled content, I was able to see all the fields and not the asterisks anymore.

[SunTop]

but the immediate query to RoadMaintenanceDayWorkCenter1PartFour is RoadMaintenanceDayWorkCenter1PartThree, and when I run that query it works fine, this means that up to the point of RoadMaintenanceDayWorkCenter1PartThree everything works fine, I don't know where did you see an asterix instead of field names ?

[SunTop]

I did thought of the experiment you did when you turned the query to a table via make table query, and it worked for me too, but why does it work with a table and not a query ?! I thought a query treats its predecessor query as a table !!!

[JoeM]

but why does it work with a table and not a query ?! I thought a query treats its predecessor query as a table !!!

Possibly for the reasons I already mentioned in my last reply.

Remember, the query has all the other queries behind it (unlike a table). When you nest queries inside of other queries, Access doesn't always perform them in the order you do or would expect. It takes them together as a whole, and tries to determine the most efficient way of performing all the actions of all the queries.

I once had a situation where I had a similar situation. My first query was filtering out some records with errors in a field that I wanted to join on at some point down the road. There were a few other queries between that and my last one. Even though all the underlying ones worked, the last one did not, and was returning errors. Come to find out it was because of my first one, excluding the erroneous records. I guess when Access reorganized it all when processing, it didn't apply that criteria until after it did the join. So it was trying to join on erroneous records, causing errors.

My work-around there was to do the same thing as I did here, write the results to a temp table and use that table. I made sure to keep that temp table current and dynamic through the use of Macros/VBA.

[SunTop]

can you please point me to url to read about creating temp tables and keeping them current and dynamic, because I have never did that before in access, I don't know where to start ? as a side note, it really seem counter intuitive that access rearranges the queries and in in the process breaks them

[JoeM]

it really seem counter intuitive that access rearranges the queries and in in the process breaks them

Not really. SQL does the same thing (a lot of database engines do, I believe). They don't always count on users designing queries in the most efficient manner. If they did, it cause very inefficient queries to try to run that could crash your system.

can you please point me to url to read about creating temp tables and keeping them current and dynamic,

It is nothing really too technical. I just created a Macro in Access that list out all the steps that need to be done, and run that when I want the data refreshed.
I also used an Append Query and simply deleted the data out each run and re-built it. I like that better than a Make Table Query, because with a Make Table Query, you cannot set the Data Types or Properties on the fields the way you want (unless you do it manually after the fact). So I just design the Temp table exactly like I want, and just delete and add data to it.

So the Macro would typically include actions, like:
- Run a Delete Query (or a one line SQL code to first delete the data) from the Temp table
- Run an Append Query to add the data to the Temp table

My Macros often had a few more steps in them, as I often had it import new data to other tables too, and maybe export my results out to a file. Basically, that is just driven by whatever the need is.

So, then it is just a matter of running the Macro (with a single click, or attaching it to a button).
Where VBA would come in is if I wanted to do some advanced things (like put date stamps on my file names), I would use the "Convert Macros to Visual Basic" functionality, which will convert all those Macros steps to VBA code (this is a great little tool, by the way), where I could edit it to make it a little slicker.

[JoeM]

By the way, here is a good write-up on Jet, the database engine that Access uses. It talks about it tries to optimize the queries:
https://www.techrepublic.com/article...cient-queries/

[SunTop]

In case anyone faces a similar situation or otherwise interested, I think I have located the problem. The query RoadMaintenanceDayWorkCenter1PartThree is based on the query RoadMaintenanceDayWorkCenter1PartTwo, which has a field named TimePerLengthRoadResultRunningTotal that I use to calculate a running total for another field in the same query and I use a subquery to calculate the running total, earlier I calculated the running total using a DSum function, but I was advised to use a subquery because a DSum function might impact performance of the query (which it did). Now I know that a query which contains a subquery cannot be edited (meaning you cannot add or delete data from the underlying tables of the query). Apparently you cannot use it to create new relations with other queries or tables also. I am not really sure if this is true, but that is what I came up with to make sense of why I get no current records when I try to use the query to create a new relation with another table. When I used the DSum function to calculate the running total, I was successful in creating a new relation with another table and I got the records that I was expecting to get.
go figure!