Not sure where to start. (1 Viewer)

[KadeFoster]

Hey all,

Has been a while since i have been on here. I am tying to create a weekly report. So today -7.

TASKID	Status	Updated
130	Scaling	1/12/23
130	Completed	2/12/23
131	Completed	25/11/23
132	Completed	2/12/23
134	Scaling	22/11/23
134	Scaling	4/12/23
134	Completed	6/12/23

I want all records for the TASKIDs where the last entry has STATUS as "Completed" and UPDATED is within TODAY()-7.

So all entries for that TASKID and others that fit the criteria.

Suggestions or ideas, i struggle with queries.

 

[arnelgp]

create a query:

select taskid, status, [updated] from
yourTableName
where
[updated] between Date()-7 And Date()
And
[status] = "updated";

 

[Uncle Gizmo]

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String
Dim strSQL7 As String
Dim strSQL8 As String
Dim strSQL0 As String

strSQL1 = "SELECT t.TASKID, t.Status, t.Updated "  ' Select fields from the table
strSQL2 = "FROM YourTable t INNER JOIN "  ' Specify the table and start an inner join
strSQL3 = "(SELECT TASKID, MAX(Updated) AS MaxDate "  ' Subquery to get the latest update date for each TASKID
strSQL4 = "FROM YourTable "  ' Specify the table for the subquery
strSQL5 = "WHERE Updated >= Date() - 7 "  ' Filter records in the subquery to the last 7 days
strSQL6 = "GROUP BY TASKID) groupedt "  ' Group the subquery results by TASKID
strSQL7 = "ON t.TASKID = groupedt.TASKID AND t.Updated = groupedt.MaxDate "  ' Join condition to match the latest record per TASKID
strSQL8 = "WHERE t.Status = 'Completed'"  ' Filter the main query to include only 'Completed' status

' Combine them to form the full SQL query
strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & strSQL8

' strSQL0 now contains the full query

 

[Minty]

@Uncle Gizmo I hope that was a poor ChatGP generated answer, as that is the messiest, most long winded way to make a SQL string eVah !

 

[Uncle Gizmo]

Line continuation V String variables
The "traditional" line continuation method is visually cleaner, but can be error-prone, particulary with long strings. The string variable approach provides clarity and, the most useful thing, ease of modification.

For example let's say you wanted to add an option group to your form so that the user could select and display of current orders, all orders, unforfilled orders in a subform.

With the line continuation method, you would need to write out three separate SQL statements. With the variable approach you haave the option of adding one or two new lines and substituting them in the build-up SQL string.

In a fictitious example of displaying orders in a subform a string like this would show "all orders"

strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & strSQL8.

And you wood have a slightly different string which would show current orders:-

strSQL0 = strSQL1 & strSQL2 & strSQL5 & strSQL6 & strSQL9 & strSQL10

I have used this method for years, and between the two methods I prefer the string variable method. ChatGPT uses the traditional line continuation method.

I find it very easy to make mistakes with the line continuation method. It's easy to miss out an underscore, and difficult to find where, if you do. The string variable method reduces the potential for mistakes in two other ways:-

1) Copying and pasting from/to code held in the line continuation format is perilous! Not so with the variable approach.

2) The string variable method It is easier to debug and modify.

What it boils down to is horses for courses. Just because it's the traditional approach does not mean you need to use it. I prefer the string variable method which l perceive as being less error prone, easier to enhance and a good choice for me.

 

[Minty]

Fair enough - I use

strSQL = "SELECT "
strSQL = strSQL & " blah blah "

As I find the continuation method difficult to read and debug.

 

[Uncle Gizmo]

I use

strSQL = "SELECT "
strSQL = strSQL & " blah blah "

And that's the "Third Way"...

I say that, only because I maintain, as a general rule, that there are usually three ways of doing something in MS Access, and often more. You've just confirmed my belief, as we now have three ways of building SQL Statements.

Any more, anyone?

 

[Gasman]

I tended to build my sql string like that, but just the one variable. Plus I always put the space at the beginning of the new line, so you do not have to scroll to the far right to see if one is there, though the subsequent debug.print would reveal that.

 

[CJ_London]

And you wood have a slightly different string which would show current orders:-

does it pick up typo's?

 

[NauticalGent]

It's pile-on Tony day, I see.... Good thing he is thick-skinned!

And I am going to go out on a limb here and say the OP has no idea what you are talking about and was hoping someone would show how to do this in the QBE grid:

 

[ebs17]

The first consideration might be whether it even makes sense to break up a CONSTANT SQL statement into VBA lines, or whether to store your SQL code in a query object and thus use a stored query that you call with the name.
Arguments: Use of the execution plan, can be tested directly, VBA code is shorter or even unnecessary.

 

[Uncle Gizmo]

how to do this in the QBE grid:

That's the 4th, and probably the best way ...

 

[Uncle Gizmo]

I haven't checked to see if it's returning the right set of Records. Here's the demo if anyone wants to have a dabble...

 

[KadeFoster]

It's pile-on Tony day, I see.... Good thing he is thick-skinned!

And I am going to go out on a limb here and say the OP has no idea what you are talking about and was hoping someone would show how to do this in the QBE grid:

View attachment 111313

Yeah a little but the alternatives are interesting as well. I didn't think this would get replied to quickly. OK i'll give this/these a go and see how it works, and try to understand whats going on.

 

[isladogs]

When writing query sql in code, I normally do so as a single block with line continuations as I find that easier to read and debug.

However, there are two types of situation where I would split it using string variables:
1. The sql is so long that I get 'a too many line continuation error' - if so I'd use @Minty's approach to fix
2. Where the various elements of the query are entered at runtime by the user e.g. by selecting from choices in combos etc.
For example, see https://www.isladogs.co.uk/multiple-group-filter/index.html

In the latter case, I use a fifth way (if I haven't lost count...) building the sql for each clause ...
strSQL = strSELECT & strFROM & strWHERE & strGROUPBY & strHAVING & strORDERBY

Doing that is of course similar to the idea shown in post #3 but it does have the advantage of meaningful string variables

 

[Uncle Gizmo]

, I use a fifth way (if I haven't lost count...)

Colin's post reminded me of a sixth way:-

Instead of placing the SQL string in a string variable, you can use a function to create the string variable and you can then pass a parameter through to change the string returned.

Particularly useful if you want to change the sort order of a subform.

On the form you would have an option group that would allow you to select ascending or descending. The option group would return a number.

The option group value is fed into the order by function and that creates the correct string for ascending or descending.

Function fstrORDERBY(sortOption As Integer) As String
    Select Case sortOption
        Case 1
            fstrORDERBY = " ORDER BY Price ASC"
        Case 2
            fstrORDERBY = " ORDER BY Price DESC"
        Case Else
            fstrORDERBY = ""
    End Select
End Function

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQL As String

strSELECT = "SELECT ProductID, ProductName, Price"
strFROM = " FROM Products"
strWHERE = " WHERE Price > 100"  ' Example condition

' Assuming the option group value is passed here
strSQL = strSELECT & strFROM & strWHERE & fstrORDERBY(1) ' 1 for ascending order

You could also use the same technique for creating different where clauses.

 

[Uncle Gizmo]

Here's an example of creating the where clause. Note that by passing through the price zero (0) causes the where clause function to return an empty string which means it shows all of the records.

Function fstrWHERE(priceThreshold As Long) As String
    If priceThreshold = 0 Then
        fstrWHERE = ""  ' No restriction, return all records
    Else
        fstrWHERE = " WHERE Price > " & priceThreshold
    End If
End Function

Dim strSELECT As String
Dim strFROM As String
Dim strSQL As String

strSELECT = "SELECT ProductID, ProductName, Price"
strFROM = " FROM Products"

' Using the function for the WHERE clause
' Passing 0 will return all records
strSQL = strSELECT & strFROM & fstrWHERE(0)

 

[The_Doc_Man]

Colin's method (building the various clauses piecemeal and concatenating after the parts are built) was what I did for one of my more ambitious queries. Turns out that some of the parts were re-usable so that I didn't have to rebuild the whole query string at the lowest level every time.

 

[isladogs]

Colin's method (building the various clauses piecemeal and concatenating after the parts are built) was what I did for one of my more ambitious queries. Turns out that some of the parts were re-usable so that I didn't have to rebuild the whole query string at the lowest level every time.

That's one of the main reasons I use that approach for complex queries. For examples of this in use, see my example app Multiple Group & Filter (isladogs.co.uk)

In fact, I take it a step further by concatenating the output from multiple combos, textboxes & option groups to create the WHERE filter

Private Sub GetRecordSource()

strWhere = strWhere & strYear & strTG & strGender & strFSM & strCOP & strLowEn & strLowMa _
    & strPupilPremium & strEAL & strFirstLang & strEthnicity & strLEACare

'combine with strSelect (set in Form_Load) and strOrderBy (depends on user choice)
strRecordSource = strSelect & " WHERE " & strWhere & " ORDER BY " & strOrderBy & ";"

Me.RecordSource = strRecordSource

CheckFilterFormat     'used to add green shading to filtered fields
Me.Requery
GetListTotal     'shows the recordset count and a summary of the filters used

End Sub

 

[Pat Hartman]

10 points to #11

Unless my Where clause is dynamic as it would be in a search form for instance, I let the QBE save me the brain strain of having to remember - and type without error - every table and column name in every application I work with in any given week. Once the Select and order by and group by are built, I might switch to SQL view for the Having and/or Where if they are complex or if I have to use a sub query. That way I can control the formatting, at least to some degree and as long as I never save the query again in QBE view, Access won't "help" me by adding a hundred parentheses and rearranging a complex conditional statement. And then unless the Select clause is also dynamic, which it rarely is, I still build the Select with the QBE and just use the query as the from. That lets me build "base" queries with calculated columns (not aggregated) such as concatenating a name and/or address so instead of doing it over and over and over again, I get to do it once and reuse the query.

And, I get to test the query without having to run the code.

I remember the olden days of working with COBOL where if I was lucky, I might get two compiles per day and It was a pain to waste one on getting the SQL correct. I used to dream of having a tool like the QBE. Once we switched to DB2, IBM had a tool that would let us build SQL and run it outside of our code. That was a huge help but it didn't have intellisense. I am probably 40 times more efficient with my current tool set, used correctly, than I ever could have been with COBOL. Of course, COBOL today is interactive and so I'm probably only 10 times more productive than I would be working with COBOL today. And being a lazy bum, I'm not looking to go back