Question Is it possible for access to link associates together

[RussellDeano]

Good evening,

Firstly i apologize for my ignorance i am relatively new to Access.

If i had a field in my table called "Associates" for example and within the cells i had the below (each line contained within one cell separated by a comma)

Four example cells:

1 - "Jake, Tom, Harry"
2 - "Tom, Mike, Nathan"
3 - "Mike, Chris"
4 - "Harry, David"

Is there a way in Access to identify Jake, Harry, Mike and Nathan as a group based on Tom being in both fields? Likewise David being linked to the same group as Harry is a mutual associate?

I hope this makes sense

Thank you in advance

 

[MarkK]

In a database one row represents one unique thing. As a result, you would never have Jake, Tom, and Harry in the same row, let alone the same field.

In a database you might have a parent row in one table with related rows in a child table, and in that case, returning the parent rows where "Tom" is a child would be a trivial matter with SQL like...

SELECT tParentTable.* 
FROM tParentTable INNER JOIN tChildTable 
   ON tParentTable.ParentID = tChildTable.ParentID
WHERE tChildTable.FirstName = "Tom"

See how that selects the parent rows where the name of the parent row's children is "Tom?"

Note that how data is stored in a correctly designed database is not the same as a spreadsheet. Search for 'database normalization' for more theory on how to store data in a database in order to make the kinds of connections you are talking about.

hth
Mark

 

[RussellDeano]

Hello Mark,

Thank you for your response, yes that makes complete sense.

I probably used a poor example but basically what my actual database relates to is graffiti attacks on our company property. The "Names" field is actually a field we populate with the graffiti tags.

So each row is an incident and the row fields are similar to:

Id - Date - Location - Time - Police Ref - Tags used

And under the "Tags used" field i have been inputting it like "ZINC, MASTA, GRAFFER" etc

And what i was hoping to do was build up a bit of an intelligence picture as what "crew" members work together across different incidents based on their tags

Again hope this makes sense

 

[MarkK]

OK, so like I said, in a database one row represents at most one single discrete thing. So if you have one incident, and you have multiple tags, then you need two tables...

tIncident
IncidentID
Location (maybe LocationID)
DateTime
PoliceRef

tTag
TagID
IncidentID
Tag

So for one added row in tIncident you will add multiple rows to tTag, one for each tag, with a link, namely IncidentID, which points back to the incident that tag belongs to. In this way you create a structure in your data that mimics the structure of the real-world scenario, which is a one-to-many relationship between incidents and tags; one incident, one or more tags. This will allow you to make the kind of association you are talking about, where you search for tags like "Tom" and return a list of incidents, and, from that list, the list of tags associated with Tom.

hth
Mark