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?
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?
Exactly what do you mean by "the same text" - is this some search criteria input by a user?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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?
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.
And we would probably say you do not understand normalisation.i would say you dont understand my application
The fact that you are asking this question infers that. You will be forever looking for workarounds for bad design?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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,
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.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.
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.
Last edited by madpiet; 02-04-2024 at 04:32 PM.
The other way is to grab the data from the database using Excel, and then using Transpose or Unpivot. =) Just pointy-clicky stuff.
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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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,
Code: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 FunctionCode: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
If this helped, please click the star * at the bottom left and add to my reputation- Thanks
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
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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!"