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.Field s(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 ;-)