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

[U]tblCustomers[/U]
CustomerName
BirthDate
SSN
FavoriteColor

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

[U]tblTasks[/U]
[B]TaskDescription                           TaskField[/B]
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 ;-)

[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:

[U][B]tblCustomers[/B][/U]
CustomerName        BirthDate
Seth Schrock        [I]null[/I]

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.