Query the brings only 0 value

[g]

Hi Everyone,
I Need a query that brings column that only has 0 (zero) value. Table Name =
Tbl_Loaded with lots of column. The columns are all number fields and I need
a query that shows only column that has 0 Value.

Example:
Tbl_Loaded
RecNum_PK, Col1, Col2, Col3, Col4, Col5
1 0 5 6 8 10
2 5 0 7 12 15
3 6 5 4 5 0


Result should be

RecNumb_Pk,Col1,Col2,Col5
1 0 5 10
2 5 0 15
3 6 5 0

Thank you very much

 

[John Spencer]

I can't do that in a query. You have to specify which columns to return
in the SELECT clause and those columns will always be returned.

You might be able to do this with some VBA to determine which columns to
return and then build the query.

You can determine this for one column at a time using something like:
SELECT RecNum_PK, Col1
FROM tbl_Loaded
WHERE Exists
(SELECT * FROM tbl_Loaded WHERE Col1 = 0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

If I understand your data you have your table as a spreadsheet instead of a
relational database that Access is intented to be.
Use a union query to put data in this form --
RecNum_PK Colm Value
1 1 0
1 2 5
1 3 6
1 4 8
1 5 10
2 1 5
2 2 0
2 3 7
2 4 12
2 5 15

Then use a select query --
SELECT Colm
FROM YourUnionQuery
WHERE Value = 0;

Use that query INNER JOIN in a crosstab query.

 

[Clifford Bass]

Hi,

Nice question. As John mentioned you have to create the query on the
fly with code. However I did it slightly different from how he suggested:

Public Sub CreateLoadedColumnsWithZeroQuery()

Const cstrQueryName As String = "qryLoaded Columns with Zero"

Dim fldLoaded As DAO.Field
Dim qdfTemp As DAO.QueryDef
Dim rstLoaded As DAO.Recordset
Dim strSQL As String

Set rstLoaded = CurrentDb.OpenRecordset("Tbl_Loaded", dbOpenSnapshot, _
dbReadOnly)
If rstLoaded.BOF And rstLoaded.EOF Then
MsgBox "No records found, unable to create query."
Else
strSQL = vbNullString
For Each fldLoaded In rstLoaded.Fields
If fldLoaded.Name <> "RecNum_PK" Then
rstLoaded.FindFirst fldLoaded.Name & " = 0"
If Not rstLoaded.NoMatch Then
If strSQL = vbNullString Then
strSQL = "select RecNum_PK"
End If
strSQL = strSQL & ", " & fldLoaded.Name
End If
End If
Next fldLoaded
If strSQL = vbNullString Then
MsgBox "No records contain a column with zero."
Else
strSQL = strSQL & vbCrLf & "from Tbl_Loaded;"
For Each qdfTemp In CurrentDb.QueryDefs
If qdfTemp.Name = cstrQueryName Then
CurrentDb.QueryDefs.Delete cstrQueryName
Exit For
End If
Next qdfTemp
CurrentDb.CreateQueryDef cstrQueryName, strSQL
RefreshDatabaseWindow
End If
End If
rstLoaded.Close
Set fldLoaded = Nothing
Set qdfTemp = Nothing
Set rstLoaded = Nothing

End Sub

Hope that helps,

Clifford Bass

 

[g]

Hi Everyone,

Thanks for giving some time to think the solution for my problem, It's very
much appreciated. I like to try what Clifford suggested but I need more
assistance as where to put this code & how will you get the result like I
wanted. Do I have to create a module to put in the code and call it some
where, something like that?

Thanks again,

 

[Clifford Bass]

Hi g,

Sorry for the delay. Microsoft's discussion group message notification
is not working.

To answer you question, copy and paste the subroutine into a regular
(not class, form or report) module. When you want to (re)generate your
query, say from a button on a form, simply place it's name in the VBA code
for the button. So if your button's name is cmdRunTheQuery the code might
look something like this:

Private Sub cmdRunTheQuery_Click()

CreateLoadedColumnsWithZeroQuery
DoCmd.OpenQuery "qryLoaded Columns with Zero"

End Sub

Clifford Bass