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.

 

[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.

 

[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?

 

[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.

 

[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.

 

[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

 

[The_Doc_Man]

Since you didn't know about relationship diagrams, I'm going to suggest that you do some serious reading on database normalization. The reason you want to do that is two-fold. First, normalizing your DB saves space (usually) by pointing to something that is re-used a lot. Second, if you have a good relationship diagram that is closer to normalized, the query and form wizards can read the relationships and "power assist" you in building something that will work more efficiently.

"Database Normalization" is what you would seek on the general Internet. On this forum, which is oriented to databases, you only need to ask for "Normalization."

Here is one problem. When you look at your data, it is possible that your master data set isn't normalized, but you claim to have not power over the data set. How you deal with that will be a serious issue.

Here is another problem. You mentioned that you have a FoxPro back-end DB but you also had some tables in a native-Access back-end. You can't draw relationships between the two because a limitation of Access is that you can only relate tables that are in the same file. You can still express a relationship implicitly in your queries, but that "power assist" won't be there for things that are in different files.

 

[The_Doc_Man]

No dispute, Pat. However, consider that even Access can have more than one DB file open at the same time - a split back-end as a workaround for the 2GB limit in a single BE file. A single DB engine runs the operation there, but you still cannot do a cross-file relationship, can you? (I know you can't and it has to do with where the relationship is stored.)

If I recall correctly, with ORACLE you CAN establish cross-file relationships because ORACLE supports multi-file "containers" that can even be on different disks. I ran an ORACLE personnel DB for the U.S. Navy that spanned 10 disks x 16 GB/disk. Their relationships don't look the same as Access relationships, but they exist. (I went online to verify that point.)

At the other extreme, I think SQL Server has more trouble with cross-file relationships and I wouldn't suggest otherwise.

Therefore my point was only that Access cannot build relationships across files, a narrow statement intended as such. You are free to disagree with your interpretation of what I said.