AWirsching
New member
- Local time
- Today, 00:32
- Joined
- Oct 26, 2021
- Messages
- 23
No worries. I just keep trying it. I'm grateful for all/any help!
Multiple queries using one date range
- Thread starter legendv
- Start date
- Local time
- Today, 00:32
- Joined
- Feb 19, 2002
- Messages
- 47,674
You sure didn't make this easy for us.
These are the two subreports with errors.
Injured/Killed by Police subreport1
Number Arrested subreport
Hope that works. I live in Connecticut and I'm all for law and order so keep up the good work
These are the two subreports with errors.
Injured/Killed by Police subreport1
Number Arrested subreport
Hope that works. I live in Connecticut and I'm all for law and order so keep up the good work
- Local time
- Today, 00:32
- Joined
- Feb 19, 2002
- Messages
- 47,674
Sadly I did, I downloaded the db but had to run out for a few hours and forgot to refresh before I got back to it.
AWirsching
New member
- Local time
- Today, 00:32
- Joined
- Oct 26, 2021
- Messages
- 23
Hey guys, thank you both for the help. yesterday I was super busy but I'm back at it today. I'll let you know how it goes. Thank you both so much for the help. This is actually my first Access Report I've ever done...
AWirsching
New member
- Local time
- Today, 00:32
- Joined
- Oct 26, 2021
- Messages
- 23
Good afternoon,
I have started back at it again and am a little confused and need help understanding.
So dat the beginning I tried and tested various text boxes and combo boxes which then supplied data to my table with an ID number. Well, at times I've had to delete rows and with multiple tables now, the ID numbers do not all match on some.
(I've included the file for reference)
The suspects/Occupants table has ID numbers that do not match up with the Operational AAR/Summary table (main table). I figure this was because of a relationship issue. I've tried "ID" to "ID", "incident number" to "related incident number" and nothing seems to change. I really need (or think I need?) the date field in this table for the query.
I think this may be why I'm having issues with the date parameters....
I have started back at it again and am a little confused and need help understanding.
So dat the beginning I tried and tested various text boxes and combo boxes which then supplied data to my table with an ID number. Well, at times I've had to delete rows and with multiple tables now, the ID numbers do not all match on some.
(I've included the file for reference)
The suspects/Occupants table has ID numbers that do not match up with the Operational AAR/Summary table (main table). I figure this was because of a relationship issue. I've tried "ID" to "ID", "incident number" to "related incident number" and nothing seems to change. I really need (or think I need?) the date field in this table for the query.
I think this may be why I'm having issues with the date parameters....
- Local time
- Today, 00:32
- Joined
- Feb 19, 2002
- Messages
- 47,674
The problems are the way your relationships are defined. A relationship is ALWAYS FK to PK So if you want to relate a suspect to an Operation, the FK in the suspects table MUST match the autonumber (which is the PK in the Operational table. And in order to ensure that you never have orphans as you do now, you MUST enforce RI (Referential Integrity) which you can ONLY do if the relationships are proper and there are no current orphans.
If Incident is unique, you could use that as the PK for the Operational table and delete the autonumber. The only reason to ever have an autonumber in a table is because you need a unique primary key and your data doesn't contain one. Incident is actually two fields mushed together so technically if you were to use this as a PK you would have a field named IncidentYR and a second named IncidentSeq. Even though you use two separate columns to define the Incident doesn't mean you can't print t the way it currently looks as year concatenated to a fixed length sequence number. Since you really don't care about the actual sequence number part, accountants get blue in the face if they have a gap in check numbers but this isn't that type of data. If you had a gap, the world wouldn't come to an end and you wouldn't have to send out a search party to find the missing number. SO. I would use the Autonumber as the sequence number part and I would use the year from OpDate as the prefix whenever I printed the number or displayed it anywhere.
Select Year(OpDate) & Format(IncidentID, "00000000") As Incident, ... - it is really poor practice to name all your autonumbers "ID")
The number of zeros in the format controls the length of the string so that it is always 8 characters with however many leading zeros are necessary. So if the number were 12345, the string would return 00012345 to come to 8 characters.
You will then need to do some manipulation when the user enters 202100012345 to strip off the leading 4 digits and just look for 12345 as the autonumber field. You could store the concatenated value instead of creating it on the fly in the query. Technically, this is a violation of normalization rules because you don't want to save the same data twice. But this data isn't changeable since it is the PK of the record so as long as you carefully protect against that, storing the duplicate data is manageable.
Once the relationships are corrected so that you are using the correct field as the foreign key, then you can enforce RI and that will prevent orphans. If you try to delete a record from Operational and it has child records in Suspects, RI will prevent the delete since that would orphan the record in Suspects. If your business rules allow deletes, you can specify Cascade Delete in the RI options and that will silently delete any related Suspect records if their parent Operational record is deleted. As you can imagine, this is fraught with danger but absolutely valid and I do it all the time. Usually there are rules about what can be deleted so for example, if an order has been shipped, you can't delete the order. It is too late, the product is on its way to the customer. Usually, we don't like to allow deletions of high level entity data but I would need to know more about your business rules to tell you how to handle that. The solution if you can't delete a record like the ones in the Operational table, is to add a DeletedDT and a DeletedReason field. ALL your queries except the ones for the edit form would need to select only non-deleted records:
Select ... From ... Where DeletedDT Is Null
There are lots of other changes I would recommend to the database but they have more to do with best practices rather than bugs. Best Practices are a little like defensive driving and looking both ways before you cross the street. You might not get killed if you ignore them but you're taking unnecessary chances.
Because you are the police and I am a resident of Connecticut, I would be willing to donate a few hours to the cause. PM me if you are interested.
If Incident is unique, you could use that as the PK for the Operational table and delete the autonumber. The only reason to ever have an autonumber in a table is because you need a unique primary key and your data doesn't contain one. Incident is actually two fields mushed together so technically if you were to use this as a PK you would have a field named IncidentYR and a second named IncidentSeq. Even though you use two separate columns to define the Incident doesn't mean you can't print t the way it currently looks as year concatenated to a fixed length sequence number. Since you really don't care about the actual sequence number part, accountants get blue in the face if they have a gap in check numbers but this isn't that type of data. If you had a gap, the world wouldn't come to an end and you wouldn't have to send out a search party to find the missing number. SO. I would use the Autonumber as the sequence number part and I would use the year from OpDate as the prefix whenever I printed the number or displayed it anywhere.
Select Year(OpDate) & Format(IncidentID, "00000000") As Incident, ... - it is really poor practice to name all your autonumbers "ID")
The number of zeros in the format controls the length of the string so that it is always 8 characters with however many leading zeros are necessary. So if the number were 12345, the string would return 00012345 to come to 8 characters.
You will then need to do some manipulation when the user enters 202100012345 to strip off the leading 4 digits and just look for 12345 as the autonumber field. You could store the concatenated value instead of creating it on the fly in the query. Technically, this is a violation of normalization rules because you don't want to save the same data twice. But this data isn't changeable since it is the PK of the record so as long as you carefully protect against that, storing the duplicate data is manageable.
Once the relationships are corrected so that you are using the correct field as the foreign key, then you can enforce RI and that will prevent orphans. If you try to delete a record from Operational and it has child records in Suspects, RI will prevent the delete since that would orphan the record in Suspects. If your business rules allow deletes, you can specify Cascade Delete in the RI options and that will silently delete any related Suspect records if their parent Operational record is deleted. As you can imagine, this is fraught with danger but absolutely valid and I do it all the time. Usually there are rules about what can be deleted so for example, if an order has been shipped, you can't delete the order. It is too late, the product is on its way to the customer. Usually, we don't like to allow deletions of high level entity data but I would need to know more about your business rules to tell you how to handle that. The solution if you can't delete a record like the ones in the Operational table, is to add a DeletedDT and a DeletedReason field. ALL your queries except the ones for the edit form would need to select only non-deleted records:
Select ... From ... Where DeletedDT Is Null
There are lots of other changes I would recommend to the database but they have more to do with best practices rather than bugs. Best Practices are a little like defensive driving and looking both ways before you cross the street. You might not get killed if you ignore them but you're taking unnecessary chances.
Because you are the police and I am a resident of Connecticut, I would be willing to donate a few hours to the cause. PM me if you are interested.
Last edited:
AWirsching
New member
- Local time
- Today, 00:32
- Joined
- Oct 26, 2021
- Messages
- 23
I've made a few adjustments...
- Local time
- Today, 00:32
- Joined
- Feb 19, 2002
- Messages
- 47,674
Here's a mini-app to look at. You have at least 7 lookup lists that I can see. Possibly more. A very long time ago I came up with a way of handling all lookup tables that were simply, PK, SequenceNum, shortDesc, LongDesc, ActiveYN using one set of forms/reports. It also includes security if you want to use it so that the users can add items to some of the lists or change their descriptions. Generally, I would only allow "admins" to update these tables because you don't want someone who doesn't understand the importance messing around. So, all seven existing lists plus new ones would be added to these tables. It makes it easy for you since you don't have to keep making new tables and figuring out for each of them how to get the values in and how to manage them should there need to be additions or changes. The data in the tables is from an insurance application.
If you decide to use this method, you would delete the Drug, legal, investigation, and operation tables and remove the embedded lists from the other lookups.
You need to remove the lookups from the tables. That is just a crutch. Lookups should only be used on forms.
You also need to remove the multi-value fields. These are extremely difficult to work with once you get to programming and analysis, plus if you ever needed to upsize the app to SQL Server, there is no conversion path so you would have to change the app to do it the old way any way.
Your names are still not good. Names should contain only letters (upper or lower case), numbers (0-9) and the underscore (_). NO other characters or embedded spaces are allowed. The second problem is that you need to avoid the names of properties or functions. "Name" is the only one I see but that one is a doozey because EVERY property has a name and Access will have trouble knowing if Me.Name is referring to the name property of the form/report the code is in or if it refers to a control or field in the RecordSource. Other common problem names are Year, Month, and Day. Regarding name, you probably don't want to mush names together. It makes searching and grouping ever so much more difficult. At a minimum, you
If you decide to use this method, you would delete the Drug, legal, investigation, and operation tables and remove the embedded lists from the other lookups.
You need to remove the lookups from the tables. That is just a crutch. Lookups should only be used on forms.
You also need to remove the multi-value fields. These are extremely difficult to work with once you get to programming and analysis, plus if you ever needed to upsize the app to SQL Server, there is no conversion path so you would have to change the app to do it the old way any way.
Your names are still not good. Names should contain only letters (upper or lower case), numbers (0-9) and the underscore (_). NO other characters or embedded spaces are allowed. The second problem is that you need to avoid the names of properties or functions. "Name" is the only one I see but that one is a doozey because EVERY property has a name and Access will have trouble knowing if Me.Name is referring to the name property of the form/report the code is in or if it refers to a control or field in the RecordSource. Other common problem names are Year, Month, and Day. Regarding name, you probably don't want to mush names together. It makes searching and grouping ever so much more difficult. At a minimum, you
