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
481 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
  • 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
 
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 57
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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 57
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 57
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 57
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now