So I created a database

[isladogs]

Posts #8 and #14 imply the opposite of each other. The First says it is a copy with confidential data removed. The second says it is live data.
If the latter is true you have two major problems.

Whilst most forum members won't download files from external sources such as Google drive, putting your live database there and providing a link is a very bad idea in terms of data security. Suggest you remove the link and upload a zipped copy to this thread.

Furthermore it is also a very bad idea to save a shared database on an online location such as Google Drive, One Drive or Dropbox. If anyone has a network interruption whilst writing data, you will get a corrupted database. You need to rethink where your data is stored

 

[Falcone203]

Hi Alan

OK So tblRuns is dealing with Details of a Callout to an Incident
You then have a field in tblRuns called Responded which you have set as a Multi Value lookup with tblFF (So tblFF is tblAvailableFirefighters)

This is not the recommended method of dealing with this scenario.

You should have your Main Form based on thlRuns with a Subform that deals with the many Firefighters who responded.

I will give you an example in a while

I'm at work so I can't look to get it straight. I believe it is missed named it runs off a query of available ff against the contactid and a null termination date. I had a available ff in a sub form and couldn't get the results I needed for a report. I believe I am still carrying the both. Select the edit option for a d select a run. When you edit responders follow the code, messy but I got what I was looking for in the end.

 

[Falcone203]

Hi Alan

Here is how I would deal with Responders

And the multivalue column? I have read multivalue is a bad move in a database, but my need arose from my report views

 

[MajP]

I have read multivalue is a bad move in a database, but my need arose from my report views

Anything that can be done in a MVF can be done without it. I would strongly advise against it, unless you have the knowledge to weigh the down stream effects. It may seem as an easy solution up front, but can be problematic in the long run. I would only do it if you are simply storing a single value. If you are instead storing a primary key to other related data, then it gets extremely confusing. Understand you will never be able to convert to SQL Server as your backend.

 

[isladogs]

Agree with MajP. MVFs are definitely a very bad idea for a multitude of reasons.
See Multivalued fields ...and why you really shouldn't use them

 

[Falcone203]

And the multivalue column? I have read multivalue is a bad move in a database, but my need arose from my report views
View attachment 84005

Anything that can be done in a MVF can be done without it. I would strongly advise against it, unless you have the knowledge to weigh the down stream effects. It may seem as an easy solution up front, but can be problematic in the long run. I would only do it if you are simply storing a single value. If you are instead storing a primary key to other related data, then it gets extremely confusing. Understand you will never be able to convert to SQL Server as your backend.

Understood. So i would need to bring the records back together for a clean report view.

 

[Isaac]

Those "responded" values should be individual child records in the child table of a parent/child, one-to-many relationship. Not stuffed together in an overloaded attribute.
If what you're saying is you ARE doing that and want to bring them back together as a delimited string in a certain case, then have a look at dbGuy's SimpleCSV function

 

[Falcone203]

Anything that can be done in a MVF can be done without it. I would strongly advise against it, unless you have the knowledge to weigh the down stream effects. It may seem as an easy solution up front, but can be problematic in the long run. I would only do it if you are simply storing a single value. If you are instead storing a primary key to other related data, then it gets extremely confusing. Understand you will never be able to convert to SQL Server as your backend.

The data comes from google forms

 

[mike60smart]

Hi Alan

In which Report are you displaying the MVF for Responders?

 

[Falcone203]

Those "responded" values should be individual child records in the child table of a parent/child, one-to-many relationship. Not stuffed together in an overloaded attribute.
If what you're saying is you ARE doing that and want to bring them back together as a delimited string in a certain case, then have a look at dbGuy's SimpleCSV function

Well, in my mess of messes, I believe I am doing both. MVF and " individual child records in the child table of a parent/child, one-to-many relationship"

 

[Isaac]

Well, in my mess of messes, I believe I am doing both. MVF and " individual child records in the child table of a parent/child, one-to-many relationship"

I see. Well...all else being equal, so to speak, if you have them properly as records in a table, and that table has a numerical column which holds the value of the related parent record's primary key, then you should be good, and it seems you could just leave the MVF behind.

 

[MajP]

As Isaac pointed out there are several concatenate methods. In other cases you may be able to use a subform or subreport to show child records.

 

[Falcone203]

Hi Alan

In which Report are you displaying the MVF for Responders?

rptRuns and frmsRuns
rptRuns, which is the same as frmRuns for editing, frmruns I use to add/remove mutual aid, apparatus and/or responders

 

[mike60smart]

Hi Alan

I don't want to be the bearer of bad news but one of your other major problems is that you have created Orphaned Records in the related Child Tables on more than one occasion.

For example if you try to set Referential Integrity on the Join between tblFF and tblTrg you will get the error indicated in the attached.
This means you have created Child records in tblTrg when you do not have a Parent Record in tblFF

 

[Falcone203]

As Isaac pointed out there are several concatenate methods. In other cases you may be able to use a subform or subreport to show child records.

Yes, I believe I have done that in one of my roster reports. It show available unit numbers that can be assigned to new personnel.
This is a subreport

So you could do the same with responders in a "clean report" as I am calling it

 

[Falcone203]

Hi Alan

I don't want to be the bearer of bad news but one of your other major problems is that you have created Orphaned Records in the related Child Tables on more than one occasion.

For example if you try to set Referential Integrity on the Join between tblFF and tblTrg you will get the error indicated in the attached.
This means you have created Child records in tblTrg when you do not have a Parent Record in tblFF

Seems the school of hard knocks wins. This is saying I will have training records to no firefighter?

 

[mike60smart]

Hi Alan

Yes that is correct

 

[MajP]

Well, in my mess of messes, I believe I am doing both. MVF and " individual child records in the child table of a parent/child, one-to-many relationship"

I am guessing, but I do not think that is what is happening. I am guessing you need a Junction Table to create a many to many. Imagine you want to assign a group of fire fighters to a Run. Since any inidividual FF can go on multiple runs and each run can be assigned multiple FF you have a many to many. To do a junction you would have

tblRuns_FireFighters
-- RunID_FK ' foreign key to table Runs
-- FF_ID_FK ' foreign key to table FF

the data would look like
1 1
1 3
1 5
2 1
2 7

so FF 1, 3, 5 went on run 1 and FFs 1,7 went on run 2

 

[Falcone203]

I am guessing, but I do not think that is what is happening. I am guessing you need a Junction Table to create a many to many. Imagine you want to assign a group of fire fighters to a Run. Since any inidividual FF can go on multiple runs and each run can be assigned multiple FF you have a many to many. To do a junction you would have

tblRuns_FireFighters
-- RunID_FK ' foreign key to table Runs
-- FF_ID_FK ' foreign key to table FF

the data would look like
1 1
1 3
1 5
2 1
2 7

so FF 1, 3, 5 went on run 1 and FFs 1,7 went on run 2

??

??

 

[MajP]

Makes sense now. But makes even more sense for not needing the MVF