Storing Multiple Hashtags for filtering data in a Table

[Its_Bing___Chandler_Bing]

Hello Everyone.


This is my first post here in this forum.
Some background info.... uni grad, engineer, practically a noobie to data management of any kind (includes all kinds of programming), basically a scrub in my office, job is to assist my superiors in field inspections and provide data as when needed, currently majority of the data is in papers and its not that great tbh...

Here's want I want to do... Filter the table Actual List of Inspected Companies based on the attending officers.


The number of attending offices for inspection varies a lot. There have been cases where the attending offices are not from my department, therefore a fixed list of attending officers is not possible.


Someone please explain and teach me on how to do this in access or excel.
Thanks in advance.

[mike60smart]

Hi

The storing of Multiple Attending Officers in 1 field is wrong. What you should
do is store the Attending Officers as Records in a related Table.

Are you able to upload the database?

[CJ_London]

your table structure should be along these lines

tblCompanies
CompanyPK
CompanyName
...

tblInspections
InspectionPK
CompanyFK
InspectionDate
....

tblOfficers
OfficerPK
OfficerName
...
...


tblOfficerAttending
OffAttendPK
InspectionFK
OfficerFK

[Its_Bing___Chandler_Bing]

Don't have one...

[Its_Bing___Chandler_Bing]

Hi

The storing of Multiple Attending Officers in 1 field is wrong. What you should
do is store the Attending Officers as Records in a related Table.

Are you able to upload the database?

Don't have one yet.

[mike60smart]

Hi
Can you upload a copy of the Excel file with Confidential data removed?

[Its_Bing___Chandler_Bing]

your table structure should be along these lines

tblCompanies
CompanyPK
CompanyName
...

tblInspections
InspectionPK
CompanyFK
InspectionDate
....

tblOfficers
OfficerPK
OfficerName
...
...


tblOfficerAttending
OffAttendPK
InspectionFK
OfficerFK

I can see how this might work... but how to set up a query so as to get the details of all attending officers in one row?

[mike60smart]

Hi
You could modify the attached Code to suit your requirements:-

CSV for Emails
 
Query
 
SELECT TOP 1 tblGroupPersonnel.GroupPersonnelID, SimpleCSV("SELECT Email FROM tblGroupPersonnel",";") AS AllEmail
FROM tblGroupPersonnel;
 
Module
 
Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With
 
'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)
 
Set rs = Nothing
Set db = Nothing
 
End Function

[Its_Bing___Chandler_Bing]

Don't have one...

I also have doubts understanding how I'll be able to implement this in a form.... because all i know is to create forms for just a single table.
Someone please direct me to some tutorial or a video so I can implement this.

[mike60smart]

Hi

You would create a Module from the Module element that I posted.

Then create a query that gives you your CSV Values.

All fieldnames would match those in your tables

[mike60smart]

It would also help if you create the tables suggested and add all relevant data.

Then upload what you have created.

Someone will then give you a hand

[Its_Bing___Chandler_Bing]

Hi
You could modify the attached Code to suit your requirements:-

CSV for Emails
 
Query
 
SELECT TOP 1 tblGroupPersonnel.GroupPersonnelID, SimpleCSV("SELECT Email FROM tblGroupPersonnel",";") AS AllEmail
FROM tblGroupPersonnel;
 
Module
 
Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With
 
'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)
 
Set rs = Nothing
Set db = Nothing
 
End Function

Thanks for your time. I'm sorry but I don't understand how the code works (because I don't know code....). Apart from the code itself I'm not sure of the example being used.

[mike60smart]

Hi

We can explain the Code in more detail when you have some tables to work with.

[CJ_London]

When designing databases you start with the tables, then the queries and finally forms and reports.

It is important you understand the concept of database normalisation. Bing it to find out more, the concept is not difficult to understand.

If you have an excel background then you need to forget all of that as it works in a completely different way. Excel combines data and presentation in one view. Databases store data in tables and uses forms and reports for presentation.

Your concern about displaying officers in one row is easily resolved either in a query or a form or report. But until you have got the basics right the solution will not make sense. But if you want to give it a go, bing ‘concatrelated’, you will find solutions similar to the one provided by Mike

With regards forms, the standard rule is one form, one table. But you can include sub forms to display data from other tables.

So create some tables along the lines I suggested, populate with some data then we can look at the next steps

[Its_Bing___Chandler_Bing]

Hi

The storing of Multiple Attending Officers in 1 field is wrong. What you should
do is store the Attending Officers as Records in a related Table.

Are you able to upload the database?

Drive link with some data (have used Lookup Field With multiple Values) : https://drive.google.com/file/d/1oQJt_JI8FBs5-kBeDB2UaXZis8QfpuSr/view?usp=sharing
Drive link with some date (have not used Lookup field with multiple values) : https://drive.google.com/file/d/1Rxx...ew?usp=sharing

Personally, I feel that feeding data using lookup with multiple values would be a lot easier using forms (from a form filling perspective).

Thanks for waiting.