mike60smart
Registered User.
- Local time
- Today, 23:21
- Joined
- Aug 6, 2017
- Messages
- 1,904
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.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
And the multivalue column? I have read multivalue is a bad move in a database, but my need arose from my report viewsHi Alan
Here is how I would deal with Responders
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.I have read multivalue is a bad move in a database, but my need arose from my report views
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
Understood. So i would need to bring the records back together for a clean report view.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 formsAnything 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.
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"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
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.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"
rptRuns and frmsRunsHi Alan
In which Report are you displaying the MVF for Responders?
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.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.
Seems the school of hard knocks wins. This is saying I will have training records to no firefighter?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
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 haveWell, 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