Sub Query Assistance

[PNGBill]

Hi Forum, Access 2010 accdb

Still not understanding Subquery and would appreciate any advice on the following.

RepaymentID is a unique record of a Repayment being made.

SELECT tblMemberRepayments.RepaymentID
FROM tblMemberRepayments;

CommRepayRef can be matched to RepaymentID.

SELECT tblCommunication.CommDate, tblCommunication.CommTime, tblCommunication.OperatorID, InStr(1,[CommNotes],"R") AS strLength, Left([CommNotes],([strLength]-1)) AS CommRepayRef
FROM tblCommunication
WHERE (((tblCommunication.CommNotes) Like "*RepayRef*"))
ORDER BY tblCommunication.CommDate DESC , tblCommunication.CommTime DESC;

I am checking for any records in tblCommunication that match a record in tblMemberRepayments. This is to alert the operator that an email has been sent, by who and when.

 

[Galaxiom]

Seems like it should just need a join between the tables.

 

[PNGBill]

I thought so too but because of this part ( I Think) it fails.

InStr(1,[CommNotes],"R") AS strLength, Left([CommNotes],([strLength]-1)) AS CommRepayRef

Here we get the position of the first "R" and then create a new query field with the chrs to the left of the R less 1 (space).

When we send an email, I thought of putting the Repayment Ref as part of the String we keep on file and then extract this value to compare for any further email attempts.

I was trying to avoid adding a additional field to tblCommunications to hold the tblMemberRepayments Reference.

 

[Galaxiom]

A derieved field cannot be used to derive another field in the same query.You could use a subquery but why not just build the whole expression in one. I don't imagine you need the strLength in the final value.

 

[PNGBill]

This gives the result needed but uses two sql's instead of one

SELECT tblMemberRepayments.RepaymentID, Max(aqryTest2.CommID) AS MaxOfCommID, aqryTest2.CommDate, aqryTest2.CommTime, aqryTest2.OperatorID
FROM tblMemberRepayments INNER JOIN aqryTest2 ON tblMemberRepayments.RepaymentID = aqryTest2.CommRepayRef
GROUP BY tblMemberRepayments.RepaymentID, aqryTest2.CommDate, aqryTest2.CommTime, aqryTest2.OperatorID
HAVING (((tblMemberRepayments.RepaymentID)=[enter repayID]));

aqryTest2 sql is

SELECT tblCommunication.CommID, tblCommunication.CommDate, tblCommunication.CommTime, tblCommunication.OperatorID, CLng((Left([CommNotes],(InStr(1,[CommNotes],"R")-1)))) AS CommRepayRef
FROM tblCommunication
WHERE (((tblCommunication.CommNotes) Like "*RepayRef*"))
ORDER BY tblCommunication.CommDate DESC , tblCommunication.CommTime DESC;

Now only using One Derived Field. Had to convert to Long for it to join with tblMemberRepayments.

Any ideas on how to get all this into just one sql

 

[Taruz]

Hello..

This could work..:

SELECT 
            tblMemberRepayments.RepaymentID, 
            Max(trz.CommID) AS MaxOfCommID, 
            trz.CommDate, 
            trz.CommTime, 
            trz.OperatorID
FROM tblMemberRepayments 
INNER JOIN (
            SELECT CommID, CommDate, CommTime, 
            OperatorID, CLng((Left([CommNotes],(InStr(1,[CommNotes],"R")-1)))) AS CommRepayRef
            FROM tblCommunication
            WHERE (((CommNotes) Like "*RepayRef*"))
            ORDER BY CommDate DESC , CommTime DESC 
) as trz 
ON tblMemberRepayments.RepaymentID = trz.CommRepayRef
GROUP BY tblMemberRepayments.RepaymentID, trz.CommDate, trz.CommTime, trz.OperatorID
HAVING (((tblMemberRepayments.RepaymentID)=[enter repayID]))

 

[PNGBill]

Works perfect, appreciate the assistance Taruz. I will try and get my head around the concept.

 

[Galaxiom]

Everyone has variations on the layout of SQL involving subqueries but whatever format is used it is essential especially as the number of layers increases.

By the ime you reach four layers it is absolutely crucial. with big queries I sometimes construct it in Notepad to avoid losing the formatting if Access decides to drop it. This can happen if the query is opened in Design mode.

The following is quite a common formatting and might allude to structure. Each subquery is indented relative to its parent.

Most developers believe it is important to align the open and close parentheses on the subquery which is something Taruz has neglected, probably accidentally. Making them obvious by placing them alone on a line and slightly indenting the subquery iteslf is also common.

taruz has also obscured the structure by excessively indenting the Select fields of the top level of the query.

Be sure to align all keywords that apply to the same level of the query.

SELECT 
  tblMemberRepayments.RepaymentID, 
  Max(trz.CommID) AS MaxOfCommID, 
  trz.CommDate, 
  trz.CommTime, 
  trz.OperatorID
FROM tblMemberRepayments 
INNER JOIN 
      (
       SELECT CommID, CommDate, CommTime, OperatorID, 
         CLng(Left([CommNotes],(InStr(1,[CommNotes],"R")-1))) AS CommRepayRef
       FROM tblCommunication
       WHERE CommNotes Like "*RepayRef*"
       ORDER BY CommDate DESC , CommTime DESC 
      ) AS trz 
ON tblMemberRepayments.RepaymentID = trz.CommRepayRef
GROUP BY tblMemberRepayments.RepaymentID, trz.CommDate, trz.CommTime, trz.OperatorID
HAVING tblMemberRepayments.RepaymentID=[enter repayID]

Removing any unnecessary parentheses added by Access's obsession with the things also helps with readability.

 

[PNGBill]

How would you join the two tables ?

tblCommunication doesn't have a field that matches tblMemberRepayments.

The derived field

CommRepayRef: CLng((Left([CommNotes],(InStr(1,[CommNotes],"R")-1))))

matches but how do you join on a derived field ?

 

[Galaxiom]

Although the Query Desiger can't represent them, joins can be made on many things besides Equals in the SQL View, including a complex expression, greater than, less than, not equals etc.

Generally though it is tidier to derive the join fields in subqueries then do a normal join in the next level.

Don't too intimidated by subqueries, they aren't as hard as they look. Start by building them as a set of cascaded queries. Once you have that working, make a copy of the top level query.

Paste the sql from the subquery on the next level in place of the corresponding query name in the top level. Format it so as to be readable, add the parentheses and an alias name (eg AS A) and rename the matcing fields in the SELECT to A.whatever in the top level. (Otherwise you can alias to the name of the original query but then I think Access might get confused unless you delete the original.)

Try to avoid opening the query in Design view as this can destroy your careful formatting.

After a bit of experience doing this you will be able to write the subquery code directly. However it often does save a bit of typing by using the desiger to write some part of the query then editing the SQL.

BTW Other than cluttering the navigation pane, I have read that there is really nothing wrong with leaving them as a set of cascaded queries because Access optimises the group before running.

 

[PNGBill]

Thanks Galaxiom for the detailed explanation.

The more you learn it seems the more you have to learn

I am trying to avoid adding queries to the navigation pane because of my past where I had chains of them. One extreme to another I guess

I watched (TV) a game of Rugby on Saturday night that could have had a few subqueries. The Pensioners form Ireland gave the Young Guns a lesson they will take a bit of time to forget.

 

[Galaxiom]

The more you learn it seems the more you have to learn

Most definitely. I lost count of the times I thought I was really starting to get my head around Access only to find whole new areas I hadn't even touched yet.

I am trying to avoid adding queries to the navigation pane because of my past where I had chains of them. One extreme to another I guess

That is sensible as it becomes more and more difficult to keep track of the dependencies of the queries and the names become increasingly cumbersome.

One easy way to reduce the number of queries is use an SQL RecordSource on you forms and reports. Simply paste in the SQL from the query and delete the query.

I have moved further and further towards storing queries in code and Executing them, leaving the navigation window with very few or no queries at all.

This is a natural progression if you anticipate moving into coding database applications without Access. I have done Excel apps like this and will be looking into VB.NET in the next few months.

I also increasingly use forms based on ADO recordsets which is the natural progression when contemplating development of web based applications.

Ultimately all developers should be heading down that path because that is where the furture lies. Have no doubt of this because Microsoft has already revealed that the focus of the application development platform from Windows 8 on will be with Javascript and CSS. This is not just for the web but compiled applications too.

 

[PNGBill]

Thanks again Galaxiom.

One easy way to reduce the number of queries is use an SQL RecordSource on you forms and reports. Simply paste in the SQL from the query and delete the query.

I did this excercise a year or two ago and all forms and reports have their own SQL Recrod Source. Except one where the Union Query is so long it wouldn't fit

Forums like this are invaluable.