Queries within queries?

[BonnieG]

I have no idea if what I'm doing is even possible...

However this is what I'd like to do.

I have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.

Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.

Let's say I have a table called "users" and within that I have:

Surname
Forename
FieldA
FieldB
FieldC

FieldA has a value of either NULL or a 12-digit number

FieldB has the values are "ENABLED", "DISABLED" and "N/A"

FieldC contains a value of either "1" or NULL

This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.

Sooo... I'd like my query to categorise for me as follows:

Category1 = FieldA IS NOT NULL and FieldB="ENABLED"
Category2 = FieldA IS NOT NULL and FieldB="N/A"
Category3 = FieldA IS NULL and FieldB="ENABLED"
Category4 = FieldA IS NULL and FieldB="N/A"
Category5 = FieldA IS NOT NULL and FieldC = 1
... etc.

You get the idea.

I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.

Is this actually possible within queries? Sorry, I am quite new to all this and haven't done much with advanced queries. It's the syntax I'm not sure about...

If anyone can give me any pointers, I'd be so grateful. Thank you!

 

[pr2-eugin]

BonnieG, as much as we would like to help you. We would want you to help us to help you. We get the idea of what you want to do,m but still you are the one who knows what you want. The basic idea would be to create a function, pass the three fields to the function and manipulate the data there, and return the appropriate category. Then use this Function in the Query for the final column.

Apart from that, privacy is important, but knowing your field name means nothing to us, but will help us understand the data better. Saying about that, we do not need your actual field names either, something that will reflect the data. Because FieldA, FieldB, FieldC will make no sense as much as field names like employeeDesignation, startDate, activeEmployee. In a rough look we can understand employeeDesignation might be a String/Text. startDate is a Date/Time field, activeEmployee might be a Yes/No type.

 

[namliam]

I wouldnt do this with a function, simply create a table to join on the fields.
Make up some value for the NULL fields and presto...
Only "issue" I can see is the FieldA, and well, 2 * 3 * 2 = 12 options isnt that hard to write out in a function... so its a viable option atleast.

 

[BonnieG]

Thank you for your kind reply pr2-eugin.

Sorry, I thought that giving the fields "simple" names would make more sense! My bad...

FieldA = text field, max. 50 chars and it's called uuid (it should probably be changed to a number field, right, with max of 12 characters? Hmmm - I didn't create the table initially... is it possible to change it now it's got data in it?)

FieldB = text field, max. 50 chars called status (beginning to think this is my ex-boss's default data field type!)

FieldC = Number field, Long Integer called yyy (again this should always be either NULL or 1)

Is that any more useful?

I really appreciate your help, thank you.

 

[pr2-eugin]

I am more intrigued in knowing the method namliam has proposed, joining the table. I will wait for his response. Then I will give some information about the function method I had in mind (of couse something like namliam's final suggestion).

 

[BonnieG]

I wouldn't have the first clue how to implement the table method so I'm intrigued... how would that be done namliam?

 

[namliam]

I would definatly consider a function here like I posted, but if you want to go the table route...

Make a query... qryFillNulls

Select anyfieldsyoumayneedlateron
   , iif(isnull(FieldA), "Blank", "Filled") as FieldADesc
   , FieldB
   , nz(FieldC, "XXX") FieldCDesc
from Yourtable

Now make a table that holds the 3 columns and an additional category field. Creating a record for each available option

FieldA FieldB   FieldC  CategoryField
Blank  Enabled  XXX     Category1
Blank  Enabled  1       Category2
Blank  Disabled XXX     Category3
Blank  Disabled 1       Category4
etc

Create a new query qryCategories, join the above query and table on the 3 fields.
The beauty of this solution is that it should be relatively easy for people to add new categories if your FieldC should now also contain 2,3,4 or any other value.
Or if you FieldB gets an additional status of "Pending" or something.

That is the downside of the function, you need some sort of new coding and thus someone with coding experience to get it right if you want to add/change things. Here you just have someone go into the table and add/change to their heart's content.

Hardcoding options can run out of control if you consider that 2 * 3 * 4 already means you have now got 24 options instead of 12

 

[BonnieG]

Thank you for that! Unfortunately just left the office so can't test it out until tomorrow morning. I'm not 100% comfortable that I know what I'm doing with the above but I'll have a go.

It's unlikely that other categories will need adding to the list so I don't mind doing a function instead if that's easier (I have no idea...)

Thank you both. I'd be interested to hear what your solution might be pr2-eugin... if you don't mind sharing.

 

[pr2-eugin]

I am , in the first reply you mentioned..

I wouldnt do this with a function,

Now you say..

I would definatly consider a function here like I posted,

Make up your mind namliam ! LOL

I really like the idea of the table, it is efficient, I did not go for that because I did not know how exactly I should achieve this. Well now I know. I definitely agree hardcoding might not always help ! Your solution is much better than what I had in my head.

Public Function getCategory(FieldA As Variant, FieldB As Variant, FieldC As Variant) As String
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim aLng As Long, bLng As Long, xLng As Long
    
    aLng = IIf(IsNull(FieldA), 2, 4)
    bLng = IIf(FieldB = "ENABLED", 3, IIf(FieldB = "DISABLED", 5, 7))
    xLng = IIf(IsNull(FieldC), 9, 11)
    
    Select Case (aLng * bLng * xLng)
        Case 54
            [COLOR=Green]'FieldA Is Null, Field B Is 'Enabled', FieldC Is Null[/COLOR]
            getCategory = "Category - 1"
        Case 66
            [COLOR=Green]'FieldA Is Null, Field B Is 'Enabled', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 2"
        Case 90
            [COLOR=Green]'FieldA Is Null, Field B Is 'Disabled', FieldC Is Null[/COLOR]
            getCategory = "Category - 3"
        Case 110
            [COLOR=Green]'FieldA Is Null, Field B Is 'Disabled', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 4"
        Case 126
            [COLOR=Green]'FieldA Is Null, Field B Is 'N/A', FieldC Is Null[/COLOR]
            getCategory = "Category - 5"
        Case 154
            [COLOR=Green]'FieldA Is Null, Field B Is 'N/A', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 6"
        
        Case 108
            [COLOR=Green]'FieldA Is Not Null, Field B Is 'Enabled', FieldC Is Null[/COLOR]
            getCategory = "Category - 7"
        Case 132
           [COLOR=Green] 'FieldA Is Not Null, Field B Is 'Enabled', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 8"
        Case 180
            [COLOR=Green]'FieldA Is Not Null, Field B Is 'Disabled', FieldC Is Null[/COLOR]
            getCategory = "Category - 9"
        Case 220
            [COLOR=Green]'FieldA Is Not Null, Field B Is 'Disabled', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 10"
        Case 252
            [COLOR=Green]'FieldA Is Not Null, Field B Is 'N/A', FieldC Is Null[/COLOR]
            getCategory = "Category - 11"
        Case Else
            [COLOR=Green]'FieldA Is Not Null, Field B Is 'N/A', FieldC Is Not Null[/COLOR]
            getCategory = "Category - 12"
    End Select
End Function

Although the other downfall to the JOIN namliam has posted, is that it will make the Query non-up datable/read only.

 

[namliam]

I wouldnt do this with a function, .... so its a viable option atleast.

This from my original post , ambiguous bastard I am

Wow paul, that also requires some math skills to make sure you have unique numbers....

My function would probably have been more rudimentary, something like:

       If 1=2 then 
       elseif isnull(FieldA) and FieldB = "Enabled" and isnull(FieldC) then
             getCategory = "Category - 1"
       elseif isnull(FieldA) and FieldB = "Enabled" and not isnull(FieldC) then
             getCategory = "Category - 2"
etc....
       else
             getCategory = "NoCurrentMapping" 
       endif

 

[namliam]

Or perhaps a little more easy to verify you have all options and perhaps a little more along the way of Paul...

If isnull(fieldA) then
    myEval = "1" 
else 
    myEval = "2"
endif

If fieldB = "Enabled" then
    myEval = myeval & "1" 
elseif fieldb = "Disabled" then
    myeval = myeval & "2"
etc..
Endif

If fieldC .... 
...

Select Case myEval
    case "111"
        getcategory = "1"
    case "112" 
        Getcategory = "2" 
End Select

 

[pr2-eugin]

This from my original post , ambiguous bastard I am

Ha Ha, maybe I was not paying close attention.

Wow paul, that also requires some math skills to make sure you have unique numbers....

Something else I am good at, I guess

My function would probably have been more rudimentary

True, that was the first thing that came up to my mind, but then thought about the combination you provided and thought this might be better.

 

[BonnieG]

Ah gosh thanks guys. I'm going to attempt the table method tomorrow morning and chances are I'll be back with more questions as I'm VERY much a novice.

 

[namliam]

howabout:

Iif(isnull(dbo_user.yyy), "No", "Yes") as Browser

???
You can use simular IIF to fix your AccountExists....

Iif (dbouser.Status= "Something", "this"
  , iif(dbouser.status = "another", "that", "nothing" )
   )

 

[BonnieG]

Thanks so much guys. You have both been really helpful. I may be 2.5 hours past my intended deadline but I managed to get the query done and ready to run on a weekly basis! It's even better than I thought it would be. Happy team all round!