Get records with a negative value anywhere in the table.

[j.t.w]

Hello Everyone,

I have a table which has about 150 fields (columns). About 140 fields
contain numbers. Note: This is not a normalized table as the data was
imported from Excel. I need query or vba code to return a recordset
where there are any negative numbers in any of the 140 fields.

Can anyone point me in the right direction?

Thanks,
j.t.w

 

[Dirk Goldgar]

in message

Hello Everyone,

I have a table which has about 150 fields (columns). About 140 fields
contain numbers. Note: This is not a normalized table as the data was
imported from Excel. I need query or vba code to return a recordset
where there are any negative numbers in any of the 140 fields.

Yuck, what a mess!

Does this table have a primary key? Do the field names that have to be
checked have similar names, such that code can loop through them by name
without having to enumerate every field to be checked? Or are they the only
fields of the same data type in the table?

 

[John W. Vinson]

Hello Everyone,

I have a table which has about 150 fields (columns). About 140 fields
contain numbers. Note: This is not a normalized table

THAT'S for sure!!!! ouch...

as the data was
imported from Excel. I need query or vba code to return a recordset
where there are any negative numbers in any of the 140 fields.

Can anyone point me in the right direction?

If you'll be working with this data longterm I'd really suggest setting up
some code or queries to recast it into normalized for, as it's a right pain to
deal with this kind of sheet data in Access. I doubt that a (tedious but
straightforward) query with 140 criteria

< 0

on 140 separate lines

will work; it's probably going to give you "query too complex". Nor will
multiplying all 140 fields and seeing if you get a negative product work -
there might be a zero in there.

A VBA solution might use a recordset selecting the 140 fields, and use a
function which returns True if there's a negative amongst them; this function
could be called from a query selecting the ID and the number fields, where
you'ld pass the primary key of the record:

Public Function IsNegative(lngID As Long) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM yourquery WHERE ID = " & lngID, _
dbOpenDynaset)
IsNegative = False
For Each fld In rs.Fields
If fld.Value < 0 Then
IsNegative = True
Exit For
End If
Next fld
rs.Close
Set rs = Nothing
End Function

 

[j.t.w]

in message




Yuck, what a mess!

Does this table have a primary key?  Do the field names that have to be
checked have similar names, such that code can loop through them by name
without having to enumerate every field to be checked?  Or are they theonly
fields of the same data type in the table?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk,

Thanks for your quick response.

1) This table does not have a primary key field but a combination of 5
fields make the record unique.

2) The names of the numeric fields are similar in groups. (eg.
Year1_Maint, Year1_Maint_Days, Year1_Maint_Cost, Year2_Maint,
Year2_Maint_Days, Year2_Maint_Cost, Year3_Maint, Year3_Maint_Days,
Year3_Maint_Cost, etc.).

3) The fields where the values need to be checked are all Double data
types. All other fields are Text.

I think you you're right as I'll probably need to loop through a
recordset, loop through the fields where the data type is Double, if
it finds a negative, add the record to a temp table, and continue.
What do you think?

Thanks,
j.t.w

 

[Dirk Goldgar]

A VBA solution might use a recordset selecting the 140 fields, and use a
function which returns True if there's a negative amongst them; this
function
could be called from a query selecting the ID and the number fields, where
you'ld pass the primary key of the record:

Public Function IsNegative(lngID As Long) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM yourquery WHERE ID = " & lngID, _
dbOpenDynaset)
IsNegative = False
For Each fld In rs.Fields
If fld.Value < 0 Then
IsNegative = True
Exit For
End If
Next fld
rs.Close
Set rs = Nothing
End Function

That's the sort of thing I was thinking of, John. It would make a huge
difference to the efficiency of the procedure, though, if instead of:

Set db = CurrentDb

.... you use:

Set db = DBEngine.Workspaces(0)(0)

 

[Dirk Goldgar]

1) This table does not have a primary key field but a combination of 5
fields make the record unique.

That's too bad, because it makes it impractical to open a separate recordset
on each specific record, as John Vinson and I were thinking. Unless, of
course, you have or can create a unique index on that combination of fields.

2) The names of the numeric fields are similar in groups. (eg.
Year1_Maint, Year1_Maint_Days, Year1_Maint_Cost, Year2_Maint,
Year2_Maint_Days, Year2_Maint_Cost, Year3_Maint, Year3_Maint_Days,
Year3_Maint_Cost, etc.).

3) The fields where the values need to be checked are all Double data
types. All other fields are Text.

That's good, because it relieves you of the need to enumerate the fields in
code.

I think you you're right as I'll probably need to loop through a
recordset, loop through the fields where the data type is Double, if

it finds a negative, add the record to a temp table, and continue. What do
you think?

I think that's probably the best way to go. You can either add the whole
record to this temp table, or if you just need a list of the records, you
could add only the distinguishing keys to this table, and then use the table
in other queries, joined to the original table to identify the records you
care about.

 

[j.t.w]

That's too bad, because it makes it impractical to open a separate recordset
on each specific record, as John Vinson and I were thinking.  Unless, of
course, you have or can create a unique index on that combination of fields.



That's good, because it relieves you of the need to enumerate the fields in
code.


it finds a negative, add the record to a temp table, and continue. What do
you think?

I think that's probably the best way to go.  You can either add the whole
record to this temp table, or if you just need a list of the records, you
could add only the distinguishing keys to this table, and then use the table
in other queries, joined to the original table to identify the records you
care about.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk and John,

Thanks for setting me in the "right" direction. I'll work on it and
post what I come up with (or if I have any more questions).

Thanks again.
j.t.w

 

[Jeff Boyce]

As Dirk & John have suggested, working with this data set using the tools
provided by Access would require you and Access to work much less if the
data were in a structure that Access is optimized to deal with, i.e.,
well-normalized.

Please note ... even IF the data comes from Excel, you are NOT required to
maintain the structure in Access. Have you looked into using queries to
"parse" the data into a well-normalized structure? It would sure make your
query (?any negative value) much simpler!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

1) This table does not have a primary key field but a combination of 5
fields make the record unique.

2) The names of the numeric fields are similar in groups. (eg.
Year1_Maint, Year1_Maint_Days, Year1_Maint_Cost, Year2_Maint,
Year2_Maint_Days, Year2_Maint_Cost, Year3_Maint, Year3_Maint_Days,
Year3_Maint_Cost, etc.).

Sounds like you could benefit (perhaps a lot) from constructing a normalized
table and running a Normalizing Union Query to populate it:

SELECT 2001 AS TheYear, Year1_Maint AS Maint, Year1_Maint_Days AS Maint_Days,
Year1_Maint_Cost AS Maint_Cost
WHERE Year1_Maint IS NOT NULL <or other appropriate criteria>
UNION ALL
SELECT 2002 AS TheYear, Year2_Maint AS Maint, Year2_Maint_Days AS Maint_Days,
Year2_Maint_Cost AS Maint_Cost
WHERE Year2_Maint IS NOT NULL
UNION ALL <etc etc>

I'd use Long Integer fields for the integer values, Currency for the costs;
create the above query and base an Append query on it to populate the
tall-thin table. You could then get your negative numbers with a trivially
easy query (and have a properly normalized table to work with to boot).