So I created a database (1 Viewer)

mike60smart

Registered User.
Local time
Today, 09:05
Joined
Aug 6, 2017
Messages
1,899
Hi Alan

Here is how I would deal with Responders
 

Attachments

  • Responders.JPG
    Responders.JPG
    42.1 KB · Views: 124

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
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

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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
1596484293327.png
1596484569027.png
 

Attachments

  • 1596484020715.png
    1596484020715.png
    52.4 KB · Views: 132

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
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.
 

Falcone203

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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.
1596485401523.png
 

Isaac

Lifelong Learner
Local time
Today, 02:05
Joined
Mar 14, 2017
Messages
8,738
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

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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
1596486314326.png
 

mike60smart

Registered User.
Local time
Today, 09:05
Joined
Aug 6, 2017
Messages
1,899
Hi Alan

In which Report are you displaying the MVF for Responders?
 

Falcone203

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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

Lifelong Learner
Local time
Today, 02:05
Joined
Mar 14, 2017
Messages
8,738
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

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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

Registered User.
Local time
Today, 09:05
Joined
Aug 6, 2017
Messages
1,899
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
 

Attachments

  • Child.JPG
    Child.JPG
    73 KB · Views: 129

Falcone203

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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
1596487299067.png

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

Falcone203

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 04:05
Joined
Dec 5, 2019
Messages
40
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
??
1596490360527.png
??
 

Users who are viewing this thread

Top Bottom