Solved

How do I add a column to an ms-access query which provides field header info where value is null?

Posted on 2011-03-23
20
522 Views
Last Modified: 2012-05-11
Hi Experts!
In MS-Access 2003 I have a query with lots and lots of columns. This query serves as an exception report listing long records but with incomplete data here and there.
I would like to add a column at the very beginning of this long query with the header name 'Exception', which simply mentions the header name of only the first encountered cell for each record where the value is NULL.

Thanks!

Exception | Header A | Header B | Header C | Header D |etc...
-----------------------------------------------------------------------------
Header B | 15645      | NULL        | 58492      | NULL        | etc...



0
Comment
Question by:TicketMan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
20 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 250 total points
ID: 35196357
let say you have header until Header E

SELECT
IIF(ISNULL([Header A], 'Header A',
   IIF(ISNULL([Header B], 'Header B',
      IIF(ISNULL([Header C], 'Header C',
         IIF(ISNULL([Header D], 'Header D',
            IIF(ISNULL([Header E]', 'Header E', '') ) ) ) ) AS Exception
, * 
FROM TableName

Open in new window

0
 

Author Comment

by:TicketMan
ID: 35196472
Thanks for such fast response (I will accept this as part or whole answer). Because I have so many colums I am now thinking this could become a very long and type error prone IIF statement... Would it be possible to handle this with a vba procedure instead where it iterates through all the columns  and getting the header names by their indexes?
0
 

Author Comment

by:TicketMan
ID: 35197537
I tried your answer JoeNuvo, which is spot-on if there are a few columns only.

Unfortunately I am getting an error message "Expression too complex in query expression" after typing the 26th nested IIF(...) and that's because I have many columns.

If only your answer could be translated into a VBA function which returns the header names in the Exception column, that would be the solution.

Many thanks again for your help!
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35197843
I'll feedback later if I could find some solution for it :)
meanwhile, hopefully other expert will come to shed some light.
0
 
LVL 58
ID: 35198495

 You'd be better off to do this in the report with code rather then trying to do it in the SQL statement.

JimD.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35198804
2 temporary solutions,
perform cascade sub query,
I uses 16 fields as sample

SELECT 
iif(NOT isnull(Sub1.E1to7), Sub1.E1to7,
iif(NOT isnull(Sub1.E8to14), Sub1.E8to14,
iif(NOT isnull(Sub1.E15to16), Sub1.E15to16, ''))) AS [Exception], Sub1.*
FROM
   (SELECT
   Table1.*,
   iif(isnull(field1),'Field1',
   iif(isnull(field2),'Field2',
   iif(isnull(field3),'Field3',
   iif(isnull(field4),'Field4',
   iif(isnull(field5),'Field5',
   iif(isnull(field6),'Field6',
   iif(isnull(field7),'Field7',NULL))))))) AS E1to7,
   iif(isnull(field8),'Field8',
   iif(isnull(field9),'Field9',
   iif(isnull(field10),'Field10',
   iif(isnull(field11),'Field11',
   iif(isnull(field12),'Field12',
   iif(isnull(field13),'Field13',
   iif(isnull(field14),'Field14',NULL))))))) AS E8to14,
   iif(isnull(field15),'Field15',
   iif(isnull(field16),'Field16',NULL)) AS E15to16
   FROM Table1) Sub1;

Open in new window

SELECT
iif(isnull(field1),'Field1',
iif(isnull(field2),'Field2',
iif(isnull(field3),'Field3',
iif(isnull(field4),'Field4',
iif(isnull(field5),'Field5', Sub2.E2))))) AS [Exception], Sub2.*
FROM
   (SELECT Sub1.*,
   iif(isnull(field6),'Field6',
   iif(isnull(field7),'Field7',
   iif(isnull(field8),'Field8',
   iif(isnull(field9),'Field9',
   iif(isnull(field10),'Field10', Sub1.E1))))) As E2
   FROM
      (SELECT Table1.*,
      iif(isnull(field11),'Field11',
      iif(isnull(field12),'Field12',
      iif(isnull(field13),'Field13',
      iif(isnull(field14),'Field14',
      iif(isnull(field15),'Field15',
      iif(isnull(field16),'Field16','')))))) As E1
      FROM Table1) Sub1
   ) Sub2;

Open in new window

0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 35202190
In VBA, in the section where all these controls are, this would be relatively straightforward to code for.
Add an unbound control, txtException

In the format event of the appropriate section add this

select case true
    Case isnull(me.headerA.value)
        me.txtException.value = "HeaderA"
    Case isnull(me.headerB.value)
        me.txtException.value = "HeaderB"


....
    case else
        me.txtException.value = ""
end select


If the the controls really are all HeaderA, HeaderB ect then you can do some looping and have some compact code.
Chr(65) through Chr(90) are A-Z


'---------------
'loop code
for x = 65 to 90
    if isnull(Me.Detail.Controls("Header" & chr(x))) = true then
        me.txtException.value = "Header" & chr(x)
        Goto FoundOne
    end if
next x
FoundOne:


'code done
'------------------

You get the idea.
If you have more than 26 fields you'd need a second iteration, with "HeaderA" instead of "Header"
0
 

Author Comment

by:TicketMan
ID: 35205217
Thank you Nick67.
The results of my query with many those columns is shown as a datasheet within a mainform and not a a subform. The reason is again because of the many columns so a datasheet is more practical to show on screen. So the vba has to be trigerred from within a sql function in the query that is the source of this datasheet (still with me?...).
0
 

Author Closing Comment

by:TicketMan
ID: 35206146
Thank you Experts for providing an SQL approach and a VBA approach.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35207968
<has to be trigerred from within a sql function in the query >
That can probably be done.
You'd need a module and a Public Function
You'd feed in the Primary Key value from the table,and the function would feed out the appropriate string.
Give me a bit and I'll work it up.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35208473
Alright,

I don't have anything with the A,B,C thing going on.
I do have a query with InspectorID, InspectorID2, InspectorID3, InspectorID4, InspectorID5
InspectorID is never null, so that one I am not testing for
First the code in the Public function
JobID is my primary key

In another snippet the SQL for the query.

Wrapper functions, as these are known, can be a handy tool to get something into a query that it would otherwise balk at.
Enjoy.

Nick67
Public Function FirstNullField(myJobID As Long)
Dim db As Database
Dim rs As Recordset
Dim x As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from [qryInspectors1-5] where JobID = " & myJobID & ";", dbOpenDynaset, dbSeeChanges)

For x = 2 To 5
    If IsNull(rs.Fields("InspectorID" & x)) = True Then
        FirstNullField = "InspectorID" & x
        GoTo FoundOne
    End If
Next x
FoundOne:

End Function

Open in new window

SELECT [qryInspectors1-5].JobID, 
[qryInspectors1-5].InspectorID, 
[qryInspectors1-5].InspectorID2, 
[qryInspectors1-5].InspectorID3, 
[qryInspectors1-5].InspectorID4, 
[qryInspectors1-5].InspectorID5, 
FirstNullField([JobID]) AS [Exception]
FROM [qryInspectors1-5];

Open in new window

query.jpg
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35212323
I was think about that, too.
but that will be like multiple access to the same table over and over.

anyway, your code, for author's situation, it can be change to be

' if have IDENTITY field, usually it will be first field, so we start X at 1 instead of 0
For x = 1 To rs.Fields.Count - 1
    ' you can refer to field content by index, not by name.
    If IsNull(rs.Fields(x)) = True Then
        FirstNullField = rs.Fields(x).Name 'we also can refer to fieldname
        GoTo FoundOne
    End If
Next x
FoundOne:

Open in new window


so far, I still can't find the way to pass whole row into function with just 1 parameter (beside your method).
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35212523
It will hitting that table be over and over, true, but it is only pulling a single row, and that off a index primary key, so the cost shouldn't be ridiculous.
The Access query editor is a nicely built piece of software, and specifically optimizes for this sort of thing.
You could build a similar result out of straight T-SQL --but it's a lot more work.
Who knows what the optimizer is doing?

I also didn't go by field index because the author may have had more fields in there than just the ones he was interested in.

:)
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35212543
Understood :)
0
 
LVL 58
ID: 35213142
<<so far, I still can't find the way to pass whole row into function with just 1 parameter (beside your method). >>

  You really can't.  Closest you can come is GetRows().

  That's why I mentioned early on that the best place to do this would be in the report itself rather then in the SQL.  At that point (of printing the detail), the entire record is available without needing to read the record more then once.

JimD.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35213182
The author needed it for forms/subforms.
Unbound controls on continuous details forms are ... hmmm... a learning experience? Shall we say.
So getting into the SQL was probably needful.

Editable recordset may be a bite in the arse luking in the bush, but that's another question :)

And knowing about wrapper functions in the query editor can be handy.
They have their place in the arsenal.
Like when you need to pass in a global or public variable as a parameter :)

Public MyVariable as integer

You can't pass MyVariable in as a criteria

Public function PassMyVariable()
PassMyVariable = myVariable
end function

You can pass in PassMyVariable()

Handy monsters from time to time
:)
0
 
LVL 58
ID: 35213477
<<The author needed it for forms/subforms??>>

Same difference; you still have the entire record to work with and can loop through the fields without re-fetching the record.

JimD.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35216208
The query engine used to be dumber, especially with custom functions.
The query invokes the function.
In Access 97, would it have be dumb enough to hit-and-discard?
Probably.

Now, I think it pulls each row and says "look, how convenient, this is just the data I need for that function"
Part of the optimizations done were to ensure that no matter how the query was written, the same dataset was returned in the same timeframe.
The idea was that Access programmers weren't going to be SQL gurus, and that the optimizer should handle the guts of good performance.

Who knows?

I just know that the code I posted hit a 44000 row table, and returned results faster than I could blink, and was very readable. :)
0
 
LVL 58
ID: 35216415
<<Now, I think it pulls each row and says "look, how convenient, this is just the data I need for that function">>

  Actually, it does that for all versions, but it's not the query execution plan that is responsible, but rather JETs page cache that is at work.  

 Don't get me wrong with this; your solution works and is probably very speedy, it's just not as fast a process as it could be<g>.

  I'm still used to writting apps when I had to live in a single 64kb memory segment, so I'm always looking to avoid double duty on anything.

  And I don't disagree with anything you said.  Lot's of good info in the thread.

JimD.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35216482
:)
I've been assaulted with a mechanical engineer who thinks he can program
I'm always looking for stuff that works well and is fairly self-documenting.

'Network admins get hit by buses everyday'
I try not to be indispensible--that way my vacations don't get interrupted :)

Thanks for the kind words.
I've learned a lot of stuff on this site.

Nick67
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question