Trying to join 3-Forms

[djclinton15]

Hi, I'm having trouble joining 3-Forms all joined (according on, “FirstLetter’ fld) I was able to join Form-3 to Form-2 (according on “FirstLetter’ fld). However, when I try joining Form-1 to Form-2 (according on, “FirstLetter’ fld) I receive this lengthy error message "The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain to many complicated elements. Try simplifying the expression by assigning parts of the expression in variables." I cannot begin to understand this error message, nor know how or what needs fixing. When if refers to, "Try simplifying the expression by assigning parts of the expression in variables" are they meaning in the VBA, which I don't understand it's language. Might someone be willing to evaluate the dbs & the frm?


Or any advice/suggestions will be greatly appreciated! Thanks in advance!

[Micron]

Don't understand what you mean by joining forms. I suspect you have a form with two subforms on it, or a form with a subform that has a subform? If so, the relationships might be too complex (many to many to many). If that's not the case, more info about the forms is required. It might be an issue with data that isn't formatted correctly for the joins you are attempting. If the forms are based on queries, try testing each of them to ensure they work independently. Could also try joining 1 to 2 without 3 being joined.

[djclinton15]

First, thank for responding to my thread. Yes, my form does have 2-forms within it & they are based on queries. I do receive the same error message when I run the query as well. Also, I think when I only joined 1 & 2 I still received the same error message. I don' understand how I can have (many to many to many) relationships if all the fields are the same. Would sending you the SQ be of any use to you?

[Micron]

If the queries themselves present this error when you run them, I'd say that's where the problem lies. Start with copies of the queries and replace any form reference criteria with valid values and run the query. If you still get the error, remove one or two fields at a time from the query grid and test again. When you have something that works, you will have zeroed in on at least one offending portion. I have seen this error before, and the cause was not what one would expect from the message. Sorry I cannot recall exactly, but it was something simple - like a syntax error or invalid reference.
Don't understand what you mean by SQ (sql?). If you mean the sql statement, I think not since I'd have no data to validate it against.

[djclinton15]

I hope that you don't mind me pursuing this thread with you? The databases consist of tracking what Artists 'ArtistsID' the 'Duration' (length of time played) & 'DyEvn' (whether played in the day time or the even time). So, if I may I would like to break on all 3-queries in listing all of the fields & illustrate how these queries are joined (or linked). I'll start with the fist eury that contains all existing fields & will refer as, Qeury 3.


Query 3:
-FirstLetter: (based on 'ArtistsID' fld)
-Mnths: (formatted from 'AEDate' fld)
-WkDys: (formatted from 'AEDate' fld)
-AEDate: (ArtistsID_Enties Date) \ (Date fld)
-ArtistsID:
-Duration: (Number fld)
-DyEvn: ("D" or "E")


Query 2: (joined/linkd on 'ArtistID')
-FirstLetter
-WkDys
-ArtistsID
-CountOfDys: (ArtistsID/Count)


*Query 2 works great; it's when I join/link it to Query 3 which I can't undersand & is bugging the h*ll of me!


Qeury 3: (joined/linked on 'FirstLetter' from Query 2)
-FirstLetter:
-CountOfArtistsID: (ArtistsID/Count) *I really want to see the count of 'ArtistsID' according to ArtitsID & am really racking my brain!


This may or may not be useful to you & I apologize if I was too lenghty. Thanks!

[djclinton15]

Correction:

CountOfArtistsID: (ArtistsID/Count) *I really want to see the count of 'ArtistsID' according to 'FirstLetter'

[Micron]

Since queries are a representation of data collected from sources, I'd say there's very little chance I or anyone could shed any light on what's wrong by reading about their connections - at least in this case. What's foremost in my mind at the moment is that I see no comment as to whether or not query 3 runs by itself. After that, I'm thinking that if you have to join queries to queries (and there are no tables involved) to get at the data, your tables may not be set up properly. Maybe you'll have to post a zipped copy of your db if you have already tried opening query 3 and it runs OK.

[djclinton15]

The first sub-query 'ArtistsiD_Entries_FL_sbqry*' is the sub-query in which I'm getting the above error & am having issues with. Linked to this sub-query is the sub-query entitled, 'ArtistsiD_Entries_FL_sbqry1*' which operates fine.


If can, please provide detail steps in any changes/corrections. Needed to deleteed Macros & other changes to posted/sent dbs. Thanks

[orange]

Dave,

I agree with Micron re joining 3 forms. That is not a typical or common term in Access. I think my comments here are just different words for what Micron has advised and commented. I'll try giving my view to see if it helps.
Showing us SQL without the related data and some sample of the expected output will not be very helpful. In my view you have a very large number of Access objects --tables, queries, forms - with very similar names. I'm sure you understand each and the abbreviations used in the names. But for someone not knowing the context, nor the naming structures coupled with your terminology, it is difficult to get a grasp of the issue(s).

Forms may contain 1 or more subforms. Forms/subforms are often used in depicting tables that have a 1 to many relationship. Main table has the 1 side data and the subform has the many side data. There is a linking field between the tables (and is identified in the form/subform). [eg. A Student can study Many Subjects]

I think your use of sub-query may be adding some confusion also (for me at least). I would just call a query a query for now.

When I run this query 'ArtistsiD_Entries_FL_sbqry*', I get no errors? But I have a suspicion that I may have misunderstood your query3 and query2 example in your post.

I think you will get more focused responses if you tell us specifically WHAT you are trying to do in plain English (no Access jargon or terminology). Also, identify explicit query(ies) by name and a sample of WHAT you expect as output. You know your application, names, queries etc. better than any of us, so you'll have to help readers understand by being very specific with the issue, samples etc.

I have never seen object names with an asterisk?? It must mean something specific for you, is that so?

[Micron]

Unfortunately, I cannot open it, at least in part due to an error saying it cannot find the object 'databases'.

[djclinton15]

Hi orange, thanks for checking out my dbs & replying back to me! Man, I could have sown I sent this post late last night. I was tied up yesterday & today working on other queries & forms, but I can't let this issue go. This is so darn frustrating for me (not at you guys) is that I try really hard & feel that I'm typing out my post very clearly & precisely, but I'm not on the side & obviously I'm not doing a good job at expressing myself. In post #5 I broke down my 3-queries, listed all of the fields, (all derived from 1-Table) & how I was trying to join (or link) them together. If you open the query, 'ArtistsiD_Entries_FL_sbqry1' that's linked by 'ArtistsID' from a third query, which both has the field, 'FirstLetter. When you opened the query, 'ArtistsiD_Entries_FL_sbqry' did you try clicking on the sub-query, because that's when I get the error message. The 'FirstLetter' fld was FORMATTED in a query off of the 'ArtistsID' fld; could that be the issue? You mentioned about one to many relationships, well in my case take the letter, "A" I may have 5-ArtistsID with the letter, "A." So I WANT a query that shows me the COUNT of ArtistsID by it's first letter. Creeatng such a query is not a problem (it works) however the problem lies when I try linking it to ArtistsiD_Entries_FL_sbqry1.' I don't understand because that has, 'FirstLetter' & 'CountOfArtistsID' was created from, ArtistsiD_Entries_FL_sbqry1 so I just don't understand the issue At times like this I feel so damn frustrated & think that I ought not to be using Access because I just DO NOT understand language, relationships, structure. I'm smart & understand somethings, but when it starts to get in the area of, "programming" forget it; I never had
the mindset & failed at Computer Science in college.


I had gotten side-tracked in tending to another Table & queries which wasn't planned & consumed my entire day yesterday & most of today. This probably won't work, but about to try to create a Table which just containing the 'FirstLetter' & try to link it to the ArtistsiD_Entries_FL_sbqry1 linked on, 'FirstLetter.' I don't know...

[orange]

Dave,
I understand and appreciate your frustration. However, there are many queries in your database with somewhat similar names. For anyone not familiar with your application and naming convention, and not knowing exactly what you are trying to do, it's difficult to get to the details.

I have added a module and 2 forms to your database. I did not change any of your material.
The purpose of my changes is to scan each of your queries and to insert the latest query name and SQL into a table called tblQueries. The form (Show_Query_Options_frm) has options to a) provide an option to show the tblQueries info , or b) to refresh/rescan the current queries. If you build another query, the refresh will add it to the list. The second form (Show_Query_SQL_frm) displays the contents of tblQueries.

The purpose of this is to get the SQL identified so that anyone trying to assist you can see the SQL of the query(ies) you are talking about. You have several queries that are based on other queries that are themselves based on other queries. You know the details, but readers don't, and most (myself included) get lost in the query inside a query inside a query....

Can you describe in as simple terms as possible WHAT you are trying to do? And what queries specifically are involved?

Since you do not have any relationships defined for your tables, I find it difficult to understand the application.

I can see from your latest post

I just DO NOT understand language, relationships, structure

a factor in your frustration.
I am going to recommend that you watch some youtube videos by Dr. Daniel Soper. He has an 8 part series that is as good as any I have found. As a start you should watch 1 , 2 and 4 to get a better handle on the underlying concepts and importance of a data model. The nice thing about these is that you can stop, rewind and repeat these videos to emphasize a point/get clarification...

Good luck and I hope the material I have attached is helpful. I added my additions to a copy of your database and updated your zip file. I have provided links to the Dr. Soper videos.

After original post:
I looked at the tblQueries again, and have created a query QueryUsesQuery_Orange to show, hopefully, which queries use other queries. I have uploaded the latest within the zip.