Query Performance Improvement

[Seth Schrock]

I've got a query that I'm using to track if certain tasks are completed. A task is completed based on different fields being populated. I have a table that lists which field to look for. My current solution is to use a public function to determine which field to look for and then check if there is a value in that field. The only issue is that using a function for a bunch of records decreases the performance. I'm hoping that someone knows some trick to be able to do it via SQL.

For some examples, let say I have a table of customers (tblCustomers) with several fields

tblCustomers
CustomerName
BirthDate
SSN
FavoriteColor

Then we have a table listing all the tasks and then the field name to look into for a value

tblTasks
 TaskDescription                           TaskField
Name Entered                              CustomerName
Birthday Provided                         BirthDate
SSN Entered                               SSN
Favorite Color Provided                   FavoriteColor

Is there a way for SQL to know to look in CustomerName for the task Name Entered? It is easy with VBA, but I can't think how to do it in SQL.

[NeoPa]

Hi Seth.

Your explanation is not good but I can see you put effort into it so I'll offer what I can.

Would I be correct to guess that a record in your [tblTasks] table might have a [TaskDescription] value of the string "Birthday Provided" and a [TaskField] value of "BirthDate" to reflect that's the field to be matched when filtering? Thus you would find all customers from [tblCustomers] where the [BirthDate] field matches some date value you've not told us about yet?

I have to say the more I look at this the more weird the design appears to be. Maybe I just don't understand (Not too surprising in the circumstances in spite of your efforts) but this just seems strange to me.

Maybe if you complete the question it might make more sense.

[cactusdata]

You could start with creating a query that lists the status of the records:

SELECT 
    CustomerName Is Not Null As [Name Entered],
    BirthDate Is Not Null As [Birthday Provided],
    SSN Is Not Null As [SSN Entered],
    FavoriteColor Is Not Null As [Favorite Color Provided]
FROM 
    tblCustomers

Then you can filter on that as you like.

[isladogs]

I'm also somewhat perplexed by the point of this but you could just do something like this:

SELECT Nz([CustomerName],"") AS [Name Entered], Nz([BirthDate],Null) AS [Birthday Provided], 
Nz([SSN],"") AS [SSN Entered], Nz([FavoriteColor],"") AS [Favorite Colour Provided]
FROM tblCustomers;

The difference between the 2 approaches is that using @cactusdata method, you will get a series of boolean values.
Using Nz, you will get actual values where they exist.

BUT in fact you will get EXACTLY the same output just by aliasing each field in your query

SELECT CustomerName AS [Name Entered],BirthDate AS [Birthday Provided], 
SSN AS [SSN Entered], FavoriteColor AS [Favorite Colour Provided]
FROM tblCustomers;

EDIT:
Once you have done either of the above, you can then easily enter data in tblTasks according to whatever rules you have.
BUT if you used the same field names in both tables, none of the above would be necessary as the tables could easily be linked.
Having said all of that I'm not sure I see the purpose of tblTasks as written. If it only has those fields it seems to be pointless to me.
If it really is needed, why not add an autonumber CustID field to the first table and the same field to the second table as a foreign key.
You can then easily link the 2 tables and the other fields in tblTasks can be deleted. Queries can then be used to join the 2 tables as required

Perhaps I'm missing something in your post that would indicate why none of those would be valid approaches for you

[Rabbit]

If the number of actual fields is managable, you can "unpivot" the main table using a view with a series of union all. This will allow you to join the unpivoted view to the task table.

SELECT
    ID,
    'Field1' as FieldName,
    Field1 as FieldValue
FROM Table
 
UNION ALL 
 
SELECT
    ID,
    'Field2' as FieldName,
    Field2 as FieldValue
FROM Table
 
UNION ALL
 
...

[Seth Schrock]

My actual process is very complex, so I tried to create a simple version to get to the heart of my question. However, it seems I left out some details in my quest for simplicity. I'm going to continue with my simple version, but try to provide some background. There are different types of customers, each having their own checklist. The tasks on the checklist vary in number and description. I also want it to be flexible, so that if a process changes sometime in the future (a somewhat common occurance), I don't have to change/add fields, fix forms, queries, reports, etc., but can just add or remove a record - hence the need for [tblTasks]. [TaskDescription] is the human readable text the user will see as the label for the task. [TaskField] is the field that the system needs to look in to know if the task has been completed.

@NeoPa
You are correct about the [tblTasks] having the two fields (among others) [TaskDescription] and [TaskField] having those strings as multiple records. However, I'm not matching [tblCustomers] values to "some date value", but just to the fact that there is a value. So for each customer in [tblCustomers], there is a checklist of all the tasks (listed in [tblTasks]) with a checkmark that data is provided or not (data validation happening on the entry side, not the checklist side. So if the checklist sees data, it assumes it is valid data.). The reason for having a separate table for the checklist is that there are different categories of customers, each with their own checklist and I also want the list to be changeable without a redesign of the database. My goal (not sure if its possible) is to somehow use the value in TaskField to pull that field from [tblCustomers] using SQL instead of a public function. Rabbit's suggestion would allow me to do that, but isn't as flexible as I was hoping for. Using VBA, it is just as simple as Recordset.Fields(FieldName) allowing you to use a variable to get the value from any field that you want in the recordset. That type of thing is what I've got in my mind.

@cactusdata
My goal is to have each task listed as a separate record and not all the tasks as fields of one record.

@isladogs
I'm actually just wanting boolean values to represent data present or not. For the purpose of my checklist, I don't care what the value is. I think I may have caused some confusion with my depiction of [tblTasks]. The field names are in bold and the lines below are records with the applicable data under each field name. This allows me to have each task as a separate record, instead of separate fields of the same record.

@Rabbit
My actual checklist has up to 33 items on it. Let me see if I'm understanding your solution correctly. I would have each individual field in [tblCustomers] in a separate query and then UNION them all together? Such as the following:

SELECT 
     ID,
     'CustomerName' As FieldName,
     CustomerName As FieldValue
FROM tblCustomers
 
UNION ALL
 
SELECT
     ID,
     'BirthDate' As FieldName,
     BirthDate As FieldValue
FROM tblCustomers
 
...

This would allow me to join to [tblTasks] on the FieldName field.

[cactusdata]

use a variable to get the value from any field that you want in the recordset. That type of thing is what I've got in my mind.

Field and table names in SQL must be static, so you can't.
Stay with VBA and DAO, indeed as it is so simple to implement this way.

[isladogs]

I agree. Your description of tblTasks was anything but clear.
Having read your clarification, I agree with what @cactusdata is saying.

[Rabbit]

My actual checklist has up to 33 items on it. Let me see if I'm understanding your solution correctly. I would have each individual field in [tblCustomers] in a separate query and then UNION them all together?

Yes, your words don't quite match but the sample query you sent is what I meant

[NeoPa]

Hi Seth.

It's often very difficult to explain a situation that's very complex. It shows consideration to try to simplify it, and I have to applaud you for that, however that also often leads to a mess. I think it fair to say you fell into that category on this one. I can sympathise, and still applaud you for the attempt.

CactusData:
Field and table names in SQL must be static, so you can't.
Stay with VBA and DAO, indeed as it is so simple to implement this way.

I can't disagree with this but I might want to differentiate between using DAO Recordsets in VBA and using VBA to adjust SQL stored somewhere (Even in QueryDefs.) so that it matches your requirements. After all SQL strings within VBA are just strings.

Clearly though, in case you weren't aware before, SQL syntax doesn't allow the use of variable data in references (Tables; Fields; etc).

Now, I can see that your explanations now are a lot cleaner and clearer (which is great) but if you wouldn't mind giving me the resultant SQL you're after, as examples, for just the first two scenarios in your list - Name & Birth Date Fields - and then I think I can be more confident I actually follow what you're after. Forget using any flexibility in either SQL string and you probably won't even need to refer to the table [tblTasks], just post the SQL that does what you want it to. Feel free to use SELECT [Table].* in this even if you wouldn't want to do that in your actual work. I'm not too interested in which Fields are being shown - just the logic ;-)

[Seth Schrock]

@NeoPa
I was pretty sure that I couldn't directly use variable data in references, I was hoping that you or Rabbit knew some trick that resulted in a similar outcome. Both of you seem to be able to work magic and pull the rabbit out of the hat (pun intended ;) ) on the things you create.

Let me start with my desired outcome. I want the user to be able to see a list of people (tblCustomers) and then expand the checklist for each customer (I'm using subdatasheets for this). This checklist will be filtered to only include tasks not completed. So I want to have the following data:

tblCustomers
CustomerName        BirthDate
Seth Schrock        null

And see

+ Seth Schrock
      Name Provided:         Completed
      Birthday Provided:     ---

As far as what I was expecting the SQL to look like, I have no idea. Rabbit's idea of the UNION query is actually a whole lot simpler than I had imagined. In thinking about it, I could change the SQL of the UNION query using VBA each time the database opened, just looping through a SELECT DISTINCT query of all the fields in tblTasks that are required. Hmmm...


Part of the complexity that I was leaving out is that there are two other tables besides tblCustomers (one-to-many relationship to tblCustomers) that have the data to represent a given task being completed. That is part of the reason that I thought having a table for all the tasks would be helpful. Along with the checklist being different for each type/category of customer. Once I got a solution for one table, I can then apply that solution to the other two.

I'm trying to develop this database in such a way that it is very user friendly and doesn't feel like a software engineer designed the UI, but an end user did, without compromising good design practices. I realize that this involves a lot more effort on my part, but if it makes it easier for the end user, then it is time well spent.

[isladogs]

Suggest you upload a cut down version of your database so that anyone interested can understand exactly what you are trying to do.

[NeoPa]

Hi Seth.

The problem with just trying to explain every time and ignoring requests is that you really aren't making yourself very clear. I don't want to hear about how many other things are complicating the situation I need to think about. I need clarity & understanding. Let's just get to that first base where we actually understand what you're trying to explain. Terms like checklist and tasklist - and even tasks - aren't helpful when you seem to have a completely differnt idea of what these words mean than everyone else.

Please refer to my earlier post where I requested you send the example SQL. This was deliberately & carefully considered to be something you could do quite easily without in any way stretching your ability to explain anything. Otherwise we just seem to be going from one idea that is explained without clarity to the next. I'm bewildered here. Help me out.

[NeoPa]

IslaDogs:
Suggest you upload a cut down version of your database so that anyone interested can understand exactly what you are trying to do.

We could work with that too I expect. You deserve a bit of extra help as you've contributed much work yourself in your time. I'd be happy to go the extra mile or so for one of our own experts.

[Seth Schrock]

I don't have an example SQL of what I'm wanting as far as doing it through SQL. Right now I'm just in the planning stage and all I have is a scratchpad database to test ideas. The following does it via DLookup(), which isn't the method I want to use, but I just built this to provide a working solution with some SQL for you. [tblLoans] corresponds to [tblCustomers] from my simplified version.

SELECT tblLoans.LoanId_pk
, tblLoans.CustomerName
, tblTasks.TaskName
, IIf(
        Nz(DLookUp([LookupValue],"tblLoans","LoanID_pk = " & [LoanID_pk]),0)=0
        ,"---"
        ,"Done"
    ) AS TaskCompleted
FROM tblLoans INNER JOIN tblTasks ON tblLoans.LoanTypeId_fk = tblTasks.LoanTypeId_fk;

Lets see if I can explain what I mean by "checklist" and "task". When I'm given a job to do, I'm given a piece of paper with a list of tasks required to do that job. As I complete each task, I put a checkmark next to it and initial it saying that I've completed that step or "task". The piece of paper is the "checklist", which is made up of multiple "tasks". What I'm trying to do is digitize this process so that there isn't a piece of paper, but there is a form within Access that shows the list of tasks for each customer. Each customer may have a different checklist (different set of tasks) dependent on the type of customer. I don't want the end user to have to do the task (usually enter a value in a field) and then put the checkmark on the checklist for that task. I want the system to see that the value is there and mark the task complete automatically.

I did see where you asked for the SQL, which is why I responded with

As far as what I was expecting the SQL to look like, I have no idea.

I wasn't ignoring your request, I just simply didn't have anything. I'm trying my best to explain what I'm looking for and I apologize if I've missed some questions. Obviously I'm not explaining myself well, which is why I keep trying to further explain both what I'm trying to do and what my question is.

I will try to get my database cleaned up and a thorough explanation written up and then upload it. It may take some time though, as it is very messy at the moment from trying many things for many topics.

[Seth Schrock]

During the process of creating a clean database to upload, I tried Rabbit's suggestion and it works beautifully! I'm uploading what I have to show how I did it.

***Caution - Very Complex***

If you open frmLoans, you will see my ultimate goal. Each record with a subform listing the appropriate checklist tasks. Some completed, others not. Now, the fun part.

Table structure
[tblLoans] is the root table. Records here are what triggers a new process. All the other tables support it or are just there to tell the database how to behave.

[tblTasks] sets all the tasks based on the type of loan (from [tblLoanTypes]). There are a bunch of fields, but the important ones for this question are CompletionType (tells the system where to look to know if the task has been completed) and LookupValue, which has multiple functions. If [CompletionType] is "Document Uploaded", then [LookupValue] gives the document type ID that needs to be attached in [tblLoanDocuments] to mark the task completed. If [CompletionType] is "Loan Value", then it gives the field name to look for in [tblLoans]. If [CompletionType] is "Loan Value" and [ValueType] is "Group", then there are a group of fields required in [tblLoans] required to mark the task as completed. When there is a group, then [tblTaskAdditionalInfo] comes into play, with the table and field names to complete that task.

[tblLoans] and [tblTasks] have a many-to-many relationship, so [tblLoanTasks] is the join table between them. For those who are curious, if [CompletionType] is "Task Value" (from [tblTasks]), then the user is entering values into the fields TaskDate, TaskText, or TaskYesNo depending on the value in [tblTasks].[ValueType]. For the purpose of this example, however, I haven't included any of those values in my checklist at this time.

Queries
It would take way to much time to completely describe every single query included, but I'll provide a bit of a road map.

[qryLoanFields] is the "unpivoted" query that Rabbit suggested.

[qryChecklist] gets the tasks assigned to each loan and provides some of the information necessary to split them out based on the [CompletionType].

[qryLoanDocumentCounts] checks for how many documents are attached to each loan, grouped by the document type.

[qryChecklist_DocumentTasks] then takes the count to see if the appropriate documents are uploaded for each task and marks the task complete or not.

[qryLoanTaskAdditionalInfoNotCompleted] counts the number of fields in [tblLoans] that are empty, but required in [tblTaskAdditionalInfo]. It works based on the principle that if the count is more than 0, then not all the fields in that group are populated.

[qryChecklist_LoanValueGroup] uses the info from [qryLoanTaskAdditionalInfoNotCompleted] to mark the task completed or not.

[MasterChecklist] combines the results of [qryChecklist_DocumentTasks] and [qryChecklist_LoanValueGroup] to form the results.

Ultimately, there would be other queries to handle the other [CompletionTypes] and then combined in the [MasterChecklist] UNION query.


Disclaimer
This is not my final design. I'm hoping to be able to reduce all the QueryDefs and possibly change the possible means of completing a task. At this point I'm just trying to figure out what is possible so that I can meet all the user requirements and have a flexible design.