TABLE

[Space Cowboy]

Good Afternoon Good People

Is there a way to directly reference a table and field in an earlier query that was not not explicitly referenced in that query?

I didn't reference it because the additional field would have made my query go all shapes. I dont want to pull the table into the new related query as I have found this can generate issues as well.

 

[GPGeorge]

You could, perhaps, using a subquery or even DLookup, although the latter would be shudderingly inefficient, I suspect.

What does it mean, by the way for a query "to go all shapes"? And what issues are generated by joining in additional tables?

Sometimes, when working with databases, which are notoriously averse to ambiguity, it pays to be as unambiguous as possible when describing problems. If you can articulate the exact context, including the inputs and the outputs involved along with the specific steps followed, you can often resolve the problem simply by understanding it better.

 

[ebs17]

No. A table must be declared (in the FROM part) before fields from it can be used in the query.

 

[Minty]

As @GPGeorge has said, you need to show real details and the schema you are working with, to get sensible answers.
Remember we haven't seen your tables or we don't understand your data, so from that position, re-read your post and see if you could provide assistance. I suspect not.

And please don't go down the Bannanas, Apples and Widgets route. We don't care if they are called TopSecretParts, we just need to see the real data layouts, and interactions.

 

[The_Doc_Man]

Is there a way to directly reference a table and field in an earlier query that was not not explicitly referenced in that query?

Short answer? No. A query is not a wide-open conduit to every field in every table used in the query.

The FROM clause lists the sources of data to be returned by the SELECT query. These sources can be tables or other queries. Query nesting is allowed up to a high enough limit that I would not care to contemplate that deep a query.

The query's SELECT sub-clause lists the fields or other data (e.g. expressions) to be returned from the recordset sources named in the FROM clause. If your field in question doesn't appear directly in the comma-separated list of "things returned by the SELECT query" (between the words SELECT and FROM) then it is not in the query's so-called result set. The whole point of a SELECT query is that it explicitly selects what is going to be returned and how it is returned (i.e. simple values, formulas, formatting functions, etc.) but it only returns what you explicitly asked for and nothing else.

In fact, there is a rarely discussed subject called "Interrogatory logic" that is the study (within the broader field of logic) of whether a particular question and answer actually go together. I bring it up here because under interrogatory logic rules, if a query returned something that wasn't asked of it, that query engine would be considered as inaccurate, failed, or broken.

Therefore, the answer to YOUR question is NO. If you didn't mention a particular field in query A then query B can't see that field even if it references query A.

Now, here's the really ugly side-effect of what you asked. Let's say you ran query A and, whatever you wanted from it, you got. Then you started query B. IF query B references query A within itself, query B must re-run query A because once query A exits, the result-set is DISCARDED. That is, layering means you must run (or as implied in your question, RE-RUN) every query layer from scratch. So if you were trying to take a short-cut to get to that field, EVEN IF IT WAS IN QUERY A that you just ran, you have to run query A a second time (based on the exact way you asked your question.) Query results are NOT persistent. They are not cached somewhere after they close. If a query is closed, it contains NO DATA.

 

[Space Cowboy]

@The_Doc_Man

Thank you so much for that explanation, I did not think that it would be possible but conversely was taught that there is no such thing as a daft question so thought it better to check.
You explanations are so acute and concise, its also a gift that you can make them so understandable and thought provoking, even for someone new to access, like me.
You have also managed to provide a possible solution for another issue that i have experienced a couple of times "input cell value for ??????"
You have increased my understanding no end.

Thank you everyone for your time and trouble to help.

 

[The_Doc_Man]

You are quite correct. If you really don't know the answer and it is even slightly related to your problem, there is no such thing as a daft question.

Note, however, that in the Watercooler and under other non-technical headings, "silly" questions are perfectly legit and occur quite frequently.

 

[Pat Hartman]

IF query B references query A within itself, query B must re-run query A

Technically, the query engine takes the parts and makes a single query out of it. Therefore, I frequently use the concept of a "base" query. It selects frequently required data from a table/join and adds calculated fields such as combining the name parts into a FullName string or the address fields into a FullAddress string or the tax amount.

 

[The_Doc_Man]

I believe, from other posts, that Space Cowboy is in an ODBC environment using Visual Foxpro. I'm not saying that VP doesn't do the same thing as Access, but I will say that I don't KNOW that it does the same thing.

 

[Pat Hartman]

I'm not saying that VP doesn't do the same thing as Access, but I will say that I don't KNOW that it does the same thing.

The query engine does it before it sends the query to FP.

 

[Space Cowboy]

Technically, the query engine takes the parts and makes a single query out of it. Therefore, I frequently use the concept of a "base" query. It selects frequently required data from a table/join and adds calculated fields such as combining the name parts into a FullName string or the address fields into a FullAddress string or the tax amount.

That is good to know Pat, a couple of things to sort out then I will need to do some concatenating. Before I start thinking about it in earnest, is it possible to mix text and numbers?

 

[Pat Hartman]

Before I start thinking about it in earnest, is it possible to mix text and numbers?

In what context? are you talking about concatenating text with numbers. No problem but you might want to format the numbers. So to create a file name:

Me.txtFileName = "ThankYouLetter_" & Me.LastName & "_" & Format(Me.CustID, "000000") & "_" & Format(Date(), "yyyyMMDD") & ".Docx"

 

[LarryE]

As @GPGeorge has said, you need to show real details and the schema you are working with, to get sensible answers.
Remember we haven't seen your tables or we don't understand your data, so from that position, re-read your post and see if you could provide assistance. I suspect not.

And please don't go down the Bannanas, Apples and Widgets route. We don't care if they are called TopSecretParts, we just need to see the real data layouts, and interactions.

@ Space Cowboy:
Instead of agreeing with Minty and giving a thumbs up, why don't you do what was asked? Just take a screenshot of your table and relationship window and post it so we can all be on the same page. We can't see any data in the relationship window anyway. Help us help you.

 

[Space Cowboy]

In what context? are you talking about concatenating text with numbers. No problem but you might want to format the numbers. So to create a file name:

Me.txtFileName = "ThankYouLetter_" & Me.LastName & "_" & Format(Me.CustID, "000000") & "_" & Format(Date(), "yyyyMMDD") & ".Docx"

Thanks for that Pat, it is good to know I will not be running up a cul-de-sac.

 

[Space Cowboy]

@ Space Cowboy:
Instead of agreeing with Minty and giving a thumbs up, why don't you do what was asked? Just take a screenshot of your table and relationship window and post it so we can all be on the same page. We can't see any data in the relationship window anyway. Help us help you.

Hello Larry
Thank you for advice, what is a relationship window?
I cannot show the names or contents of my tables.

 

[Pat Hartman]

I cannot show the names or contents of my tables.

It makes sense that you can't show proprietary data and we wouldn't ask you to, but how can your column names be "secret"?

Thank you for advice, what is a relationship window?

Having to ask this means that you are not defining relationships. You need to do this. The relationship window is where you create the relationships and enforce Referential Integrity. This is what a relationship diagram looks like. Notice how the PK names match the FK names and all joins are PK to FK

To get there:

 

[Space Cowboy]

Hello Pat

thanks for that clear explanation, I have been doing a bit of work on joins and relationships so I think that I now have a bit of understanding in that regard, hard earned by trial and error.
My only concern in your schematic would be Entitygroup - group. That would annoy me.

I would be looking for a way to sort that out, probably by trial and error but would still look for alternatives,

I will have a look at object dependencies as well see what is in there.

 

[Pat Hartman]

My only concern in your schematic would be Entitygroup - group. That would annoy me.

Why does that annoy you? Entities can belong to multiple groups. EntityGroup is the junction table that implements the many-many relationship

 

[Space Cowboy]

sorry Pat for not being clear,
the link between entity group and group where the join on "group name" is not indexed or key. Far right tables
On Wednesday I would not have spotted that, my joins would have been Guest to last name

 

[Pat Hartman]

The join is on PK to FK. It is just that this PK is a text field and not an ID. The EntityGroup table is the junction table so the joins come from opposite directions. Notice the 1-side is the PK and the many side is a data field which is the FK. All the other PKs are autonumbers and so have an ID suffix. This particular table existed in the old database and so I saw no reason to add an artificial ID and then have to change all the existing records.