Extracting data from fields

[daveone23]

I have 5 records with 6 fields. Each field contains text. I would like to print a report showing which records have the same text no matter what field its in. How do i do that?

[June7]

Exactly what do you mean by "the same text" - is this some search criteria input by a user?

[madpiet]

If you have the same text in multiple fields in the same table, that's nearly always a sign of incorrect table design. Can you post some sample data?

[daveone23]

I wouldnt say is was a bad table design, i would say you dont understand my application. Table1 had 6 fields. Each field may contain a number from 1 to 100. Then i want to say how many records have the number 76 regardless of the field and it produces a list. Seems like a simple request for a data base.

[Welshgasman]

i would say you dont understand my application

And we would probably say you do not understand normalisation.

The fact that you are asking this question infers that. You will be forever looking for workarounds for bad design?

[June7]

Agree, this is probably non-normalized structure but can be dealt with.

Options:

1. build query with OR operators to check for data match in every field: WHERE field1 = 76 OR field2 = 76 OR field3 = 76 OR field4 = 76 OR field5 = 76 OR field6 = 76

2. use a UNION query to rearrange fields to normalized dataset and do search/filter on that

[Gicu]

I'll leave the design issue alone, another option for the current structure would be to concatenate the values in a calculated field along with some delimiter:

AllFields:"|" & [Field1] & "|"& [Field2] & "|" & [Field3] & "|" & [Field4] & "|" & [Field5] & "|" & [Field6] & "|"

Now you can use InString([AllFields],"76")=True to show the target records.

Cheers,

[madpiet]

I wouldnt say is was a bad table design, i would say you dont understand my application. Table1 had 6 fields. Each field may contain a number from 1 to 100. Then i want to say how many records have the number 76 regardless of the field and it produces a list. Seems like a simple request for a data base.

Please post your table design and a few records and then explain what you want the query to do. Can't fix what I can't see. But I've been doing Access etc for like 25 years, so after a while, you get a nose for non-standard designs.

Okay, How many records have the number 76? Given a design like this

CREATE TABLE SomeTable(ParentID INT NOT NULL, SomeINT INT NOT NULL);

SELECT COUNT(DISTINCT(ParentID))
FROM Sometable
WHERE SomeINT = 76;

The problem with having tons of columns is that it's a nightmare to summarize. And what do you do if someone wants to store "just one more bit of information"? add another column? That's the definition of an unstable design.

If your table is something like this, you should be fine...
CREATE TABLE SomeTable (RecordID Integer Autonumber PRIMARY KEY, ValueType String NOT NULL, Value)

and the "76" or whatever goes into the Value column, everything is easy. I have inherited databases where they had the same information in multiple columns and it was an absolute nightmare to summarize. I ended up resorting to stacking UNION ALL queries, so performance was abysmal. All that to say that a little planning up front will save you lots of time and aggravation later on.

[madpiet]

The other way is to grab the data from the database using Excel, and then using Transpose or Unpivot. =) Just pointy-clicky stuff.

[June7]

Nice idea, Vlad. However, that would match on 176, 7666, etc.

If that is not desired, include separator characters in the search.

InString([AllFields],"|76|")=True

or

InString([AllFields],"|76|")>0

[Gicu]

Yes of course, I missed the separator characters in the search, after all that's the whole point in using them...

And I usually would use:
SearchValueFound: InString([AllFields],"|76|")>0 in the top row of the query designer (for the calculated field) and True in the Criteria row

Cheers,

[moke123]

Function CountMe(SearchValue As Long, TableName As String) As String

    Dim db As DAO.Database
    Dim rs  As DAO.Recordset
    Dim SQL_Select  As String
    Dim fld As Field
    Dim i As Integer
    Dim strOut As String

    SQL_Select = "Select * from " & TableName

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(SQL_Select)
     
    If rs.BOF And rs.EOF Then GoTo MyExit

    Do Until rs.EOF

        For i = 1 To rs.Fields.Count - 1
            
            If rs.Fields(i).Value = SearchValue Then strOut = strOut & "PKey= " & rs.Fields(0) & ", " & "Field Name: " & rs.Fields(i).Name & ", " & "Value " & rs.Fields(i).Value & vbNewLine
        
        Next i

        rs.MoveNext
    Loop
    
    CountMe = strOut
    
MyExit:

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Function

Sub Test()
Debug.Print CountMe(77, "tblA")
End Sub

Result:
PKey= 1, Field Name: Txt5, Value 77
PKey= 2, Field Name: Txt2, Value 77
PKey= 2, Field Name: Txt4, Value 77
PKey= 3, Field Name: Txt3, Value 77
PKey= 4, Field Name: Txt1, Value 77

[daveone23]

Why is this such a complicated code issue for a simple table. Say you wanted to know who had a blood pressure of 145. Would you have to code the query?


name JAN FEB MAR APRIL
TOM 90 80 88 145
MARK 120 90 113 123
JANE 140 100 123 101

[June7]

Definitely a non-normalized data structure (spreadsheetitis strikes again). As we said - headachy but treatable.

Options:

1. yes, change query for hard-coded search value

2. user input by popup input prompt or reference textbox on form

3. VBA to build filter criteria (referencing control on form for user input) and apply filter to form or report, like:
Forms!formname.Filter = "InStr('|' & [Field1] & '|' & [Field2] & '|' & [Field3] & '|' & [Field4] & '|' & [Field5] & '|' & [Field6] & '|', '"|'" & Me.textboxname & "'|"') > 0"
Forms!formname.FilterOn = True
That will filter form or report that has RecordSource based on table and no calculated field.
Could do calc in query (or even table calculated field) to concatenate fields then with that query or table as RecordSource, reference calculated field in code.
Forms!formname.Filter = "InStr(AllFields, '|" & Me.textboxname & "|')>0"
Forms!formname.FilterOn = True

My preference is some version of option 3.

[madpiet]

SELECT name
FROM MyTable
WHERE Jan = 145 OR Feb = 145 OR Mar=145 OR April=145;

If your table were like this, it would be simpler still:
name, <date>, <blood pressure>
Tom, 01-05-24, 90
Tom, 02-05-24, 80
etc

Then you could just do

SELECT *
FROM MyTable
WHERE BP = 145;

A whole lot of what makes Access complicated is what's not "on the tin"/ on the label. I've inherited databases that had crazy structures, and they're an absolute nightmare to summarize. (And if you're not going to summarize and/or filter it, why are you using a database to begin with?) "Correct" design makes it fairly trivial and fast. Incorrect design makes it slow or impossible.

Say we were using the original table layout. How would you answer a question like "What was Tom's average blood pressure (and lowest and highest) between February and April?" With a table design that works with the way SQL works, it's pretty trivial.

SELECT Person, AVG([bp]) As AvgBP, MIN([bp]) AS LowestBP, MAX([bp] AS HighestBP
FROM Readings
WHERE Person = 'Tom'
AND ReadingDate>=#02/01/2024# AND ReadingDate<#05/01/2024#
GROUP BY Person;

"Use the Force, Luke!"