Help with SQL Join Query

[grendell2099]

Hi all, this is my first post to the board. I’ve been searching through the forums but haven’t been able to put all the pieces together on my problem. First some background on my application:
I have 3 tables ISSUES, UPDATES, and PRTS. Each record in ISSUES has several records in UPDATES, and may have a single record in PRTS. The issues and updates tables have 3 “clones” (everything except the table name prefix is the same) that are used by other departments. All of the clones use the same PRTS table.
I am trying to put a query into SQL (I first built it using the query builder & an example from Microsoft for a query to find the last record) that will return each issue, along with the last update associated with the issue. My intent is to switch the table names using code depending on the user’s selection. I know how to switch “ElectricISSUES” to “PowerISSUES” below, but I do not know how to switch the source for the “qryLastUpdate”. Any help would be greatly appreciated.

issueSQL = "SELECT ElectricISSUES.Index, ElectricISSUES.Status, ElectricISSUES.Description, ElectricISSUES.Driver, ElectricISSUES.Champion, ElectricISSUES.PRTS, ElectricISSUES.SubDate, ElectricISSUES.DateClosed, ElectricISSUES.Diamond, Date()-[Subdate] AS DaysOld, qryLastUpdate.Update, qryLastUpdate.Udate, qryPRTS.Step, qryPRTS.[Next Step], qryPRTS.Designee, qryPRTS.[Ewo#], qryPRTS.BreakPointDate " & _
"FROM (qryLastUpdate RIGHT JOIN ElectricISSUES ON qryLastUpdate.UIndex = ElectricISSUES.Index) LEFT JOIN qryPRTS ON ElectricISSUES.PRTS = qryPRTS.[Prob#] " & _
"WHERE (((ElectricISSUES.Status) = 'closed')) ORDER BY ElectricISSUES.Index;"
db.OpenRecordset (issueSQL)

 

[neileg]

A better approach is to combine your clones into single tables and use an extra field to identify which department is which. Your queries will become vastly simpler.

 

[grendell2099]

Thanks neileg... that option was considered when this app was first created. I decided to use clones for several reasons- the backends reside on a very slow network & there are several fields of the memo type, etc. As the databases grow, I am confident single tables would bog it down.
The SQL I have posted here is really the only complicated one in the entire app and the only roadblock I have right now. Is there another approach to this better than SQL?

 

[neileg]

OK, I don't think you will find single tables will be a performance hit, but that's your choice. If performance is an issue, stored querydefs are more efficient than building SQL in text like this.

To use qryLastUpdate in this query, you need to substitute the SQL instead of the query name.

 

[grendell2099]

Thanks for the input... I have not used querydefs before so I need to do some reading.

 

[neileg]

Sorry, a querydef is simply a stored Access query, either built using the query grid or entered in SQL view. Access compiles these when they are saved. SQL built in text has to be interpreted 'on the fly' so it takes longer.

 

[grendell2099]

Thanks for the clarification- sounds like a querydef is where I want to end up. So it sounds like I have 2 options:
1- Create a working SQL and save it as a querydef
2- Use the query builder & build a query for each clone, then assign the correct one to a querydef via code depending on the user's selection
Am I on the right track?

 

[neileg]

The saved query is the querydef, you don't assign anything. The problem of having more than one clone is why I suggested one table would be easier.

 

[grendell2099]

Well, here is what I did: I made the few queries I needed in SQL and then used code to change the table sources in the SQL and save the new querydef (if it was different). This worked like a charm for the new forms I added to the application, but the existing forms seemed to lose their relationships- subforms would not open the correct record, some fields became lost on the forms etc.
Ultimately none of that mattered because the wait time increased substantially while moving from record to record. (The previous version ran a query at startup that loaded the requested backend table data into a temp table on the user's pc... so there was a substantial wait at startup, but after that it there was no delay. By going directly to the backend, I now have annoying wait time while "inside" the app.)
I don't know why, but after I rebuilt a couple of forms from scratch (after making the queryDefs), everything works fine.