Slaine2000
New member
- Local time
- Today, 01:26
- Joined
- Sep 9, 2021
- Messages
- 24
Hiya I am looking for some help please. I been looking over the form to see if I can sort it out. They have been a great help and have I think got be here, ie thinking using an IIF with LIKE in an unbound text box might be a solution. I think I am close but my inexperience is just not letting spot where I am going wrong
Background
I have a DB (Users) with 700 for each users there is a separate DB(logs) of their logs. This DB has 4 fields USER/SECTION/TIME/EVENT. At the moment I have to research the EVENT field which is a LONG TEXT and contains a mix of text strings like AAA 123 or AAB 123, BB 111, CC 222 or Success or Failure. So at the moment I am doing the analysis by creating individual queries and letting the display tell me the total. So I want count the number of occurrances of AAA. I am using the query design (not too familiar with SQL) so I can use the LIKE "AAA*" select run and it works and shows me the number of occurances. The Problem is the are approx 10 searches for each user. So for the moment I think if I use a report I can create the count total for these 10 search criteria.
What I have so far
So create a report in the Report footer have inserted a textbox call Total AAA once I get it working can copy and chance for AAB, AAC, BB etc. Here what I got so far, bearing in mind I have pieced this together from this forum (Thanks)
=Count(IIF[Event] LIKE "AAA*", 1, 0))
When I preview the report it shows the total events (so the wildcard did not work) of 1077 but the answer should be 180 AAA ###
Hopefully this makes sense and soory for the long winded explaination. Thanks again. S
Background
I have a DB (Users) with 700 for each users there is a separate DB(logs) of their logs. This DB has 4 fields USER/SECTION/TIME/EVENT. At the moment I have to research the EVENT field which is a LONG TEXT and contains a mix of text strings like AAA 123 or AAB 123, BB 111, CC 222 or Success or Failure. So at the moment I am doing the analysis by creating individual queries and letting the display tell me the total. So I want count the number of occurrances of AAA. I am using the query design (not too familiar with SQL) so I can use the LIKE "AAA*" select run and it works and shows me the number of occurances. The Problem is the are approx 10 searches for each user. So for the moment I think if I use a report I can create the count total for these 10 search criteria.
What I have so far
So create a report in the Report footer have inserted a textbox call Total AAA once I get it working can copy and chance for AAB, AAC, BB etc. Here what I got so far, bearing in mind I have pieced this together from this forum (Thanks)
=Count(IIF[Event] LIKE "AAA*", 1, 0))
When I preview the report it shows the total events (so the wildcard did not work) of 1077 but the answer should be 180 AAA ###
Hopefully this makes sense and soory for the long winded explaination. Thanks again. S