Prioritizing Data in a Report (1 Viewer)

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
All...

Before I wave the proverbial 'white flag', I wanted to ask the 'experts' for some guidance as I'm stumped. Here goes...

I'm in the process of creating a 'Warranty Database' for tracking customer claims. At the end of every month, a report needs published detailing all of the claims received in that month. I have created a 'Claim Entry' form which is filled out by our Tech Service Department for every claim that they receive. Once complete, the form data is captured in the table 'TblWarranty'. I should note that each claim is given a VBA derived 'Claim Number' at the time that the form is saved. The table does contain the autonumbered 'ID' field but I have this hidden as it serves no purpose to the user.

When it's time to 'run' the monthly report, the user will activate the 'Warranty Report' button on the Main Database Form. This button opens the report 'Defect Count'. The record source of this report is the query 'Monthly Report Frequency Query'. So, once the report is opened, the user is asked to input the mm/yyyy for the time frame that the report needs to capture. After the mm/yyyy has been entered, the report displays the 'Defect' along w/ the number of the specific defect occurred within the time frame.

Defect Total Defects
Defective Part (6)
Assembly Error (6)
Shipping Damage (4)
Unknown (3)

There's then a button on the report to 'Print Warranty Report'. I have the report setup so that each 'Defect' along w/ the number of 'Total Defects' print in the report's header and then the claim w/ claim details, that make up the 'Total Defects' printing out in the Detail of the report. I have the report setup so that the 'Defect' headers are reported in descending order based on the quantity of 'Total Defects'. This works flawlessly.

Of course...someone needs something more. :banghead:

Instead of reporting the 'Defects' in descending order by 'Total Defects', they'd like the ability to 'prioritize' the 'Defect' report headers. So, I need something that will list all of the 'Defects' w/ their 'Total Defects', give the user the ability to move a 'Defect' up/down the priority list and then print the report in the user set priority.

I'd welcome any ideas, advice, examples...anything!!! that will allow my brain to sleep tonight instead of working in my own personal copy of Virtual Access. :D Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
21,364
Hi. To allow the user to "assign" a priority, you will need to store this information with the defect description/title. Once you have something like that, then maybe you could get something out of this demo. Hope it helps...


PS. You then use the "assigned" priority as a sort order in your report. Cheers!
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Hi. To allow the user to "assign" a priority, you will need to store this information with the defect description/title. Hope it helps...

PS. You then use the "assigned" priority as a sort order in your report. Cheers!

Thanks DB! I'm going to play around and see what I can come up utilizing your demo.

Actually, I do have a make table query that generates and stores all of the information needed for the report. What I don't know how to do is to get the query to delete the previously stored info and add the next month's info when the query is activated by doing it 'behind the scenes' and not prompting the user. Currently, it prompts the user to ask if it's ok to delete the table and that's something that I'd like to avoid...having the user receiving this message, assuming it's an error and then blowing up my phone because they need help. :p Can this be done?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
21,364
Thanks DB! I'm going to play around and see what I can come up utilizing your demo.

Actually, I do have a make table query that generates and stores all of the information needed for the report. What I don't know how to do is to get the query to delete the previously stored info and add the next month's info when the query is activated by doing it 'behind the scenes' and not prompting the user. Currently, it prompts the user to ask if it's ok to delete the table and that's something that I'd like to avoid...having the user receiving this message, assuming it's an error and then blowing up my phone because they need help. :p Can this be done?
Yes, to delete records or empty out a table, you could try the following code:


CurrentDb.Execute "DELETE FROM TableName",dbFailOnError
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Hi. You bet. Good luck with your project.

DB...one last question my friend. I now have a form where I can set the priorities just as you explained in your demo. Now, how can I utilize this new priority order on the report? Currently, the report is sorting the 'Defects' by 'Defect Total', highest number of Defects to lowest number of Defects. I need to have the report populate according to the Priorities, obviously, starting w/ the top Priority and so on...

I'm 95% of the way there thanks to you so thank you again! :)
 

June7

AWF VIP
Local time
Today, 14:23
Joined
Mar 9, 2014
Messages
5,426
Assuming Priority value is included in report RecordSource, use Sorting & Grouping settings.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
21,364
Hi TheBigEasy. Did you follow June7's suggestion? Did it work?
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Hi TheBigEasy. Did you follow June7's suggestion? Did it work?

Hi DB...I'm still 'debugging' trying to get it all to work correctly. I'm currently trying to get the data, from a query populated report, into a table so that I can use it to populate the 'Prioritize Report' form that is more or less a replica of your demo button form.

The 'Prioritize Report' form functions exactly as I had hoped and looks awesome! I just need to populate it with new info every month. :eek:
 

June7

AWF VIP
Local time
Today, 14:23
Joined
Mar 9, 2014
Messages
5,426
I get impression this is a split multi-user db and you have a 'temp' table in frontend for users to assign priority. Is this correct?
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
I get impression this is a split multi-user db and you have a 'temp' table in frontend for users to assign priority. Is this correct?

That's the idea. I have a query that pulls all of the 'Warranty Claims' within a user inputted mm/yyyy. I then have another query that takes all of the 'Warranty Claims' in that set month, organizes them into 'Defect' categories and then sums each specific defect occurrence. I then would like to use the results of this query in the 'Priority' form so that the user can specify the report order that they deem to be in 'Priority' order.

I have a table which lists all possible 'Defects' (Assembly Error, Defective Part, Order Entry Error, etc.) a column for the sum of each specific defect and a column for the 'Priority' going from 1 to 25. I somehow need to develop a new query that will take the info from the 'Priority' query and have it populate the table column for the sum of each specific defect. The query is definitely pulling all of the correct info because it's populating a report (Defect Count) with all of the defects that occurred during the month along with the total number of each defect.

Hope I didn't confuse you! I've been working on this for a good 7 straight hours and my eyes are now seeing four monitors when they should only be seeing two. :D
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Oh, please keep in mind that less than three months ago, I had never once even opened Access let alone created a database from scratch. I'm just learning as I go, reading as much as I can and using the advice of fine folks such as you guys.
 

June7

AWF VIP
Local time
Today, 14:23
Joined
Mar 9, 2014
Messages
5,426
I commend you for progress you have achieved in such short time. These are rather advanced techniques you have employed. Understanding the need for temp table in frontend to prevent user conflicts is a big one.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
21,364
Oh, please keep in mind that less than three months ago, I had never once even opened Access let alone created a database from scratch. I'm just learning as I go, reading as much as I can and using the advice of fine folks such as you guys.
Good job and good luck with your project.
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Thanks guys for the help and words of encouragement. If I run into another brick wall, I know where to go to get the help that I'll need.
 

AccessBlaster

Registered User.
Local time
Today, 15:23
Joined
May 22, 2010
Messages
5,832
I like to start with the report wizard, apply the criteria such as sorting and grouping then tweak it in design view.

HTH, good luck with you project!
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Hey guys,

I'm stumped and I'm hoping that you can 'un-stump' me. :p

So far, I have a Make-Table Query which, when executed, asks the user to input a mm/yyyy, makes the 'tblDefects' table & then pulls in the info required to perform the 'SortOrder' action on a form which utilizes DB's drop-down menu method. This is working flawlessly. From the form, I'm able to change the 'SortOrder' and watch the 'Make-Table Query' table update in real-time. Here's where I'm stumped...I have (2) questions:

  1. Now, whenever this 'Make-Table Query' is run, it asks me if I'd like to delete 'tblDefects'. Since I have added a column for the 'SortOrder', I would like to just update the table, w/ the user inputted mm/yyyy, rather than completely deleting the entire table. And since there's no benefit in just adding the new info, I just would like to have the info updated and I'm not sure how to go about doing this. Any advice?
  2. Lastly, once I get the above working correctly, how can I populate the 'Defect' report by utilizing the 'SortOrder', user set on form 'frmPrioritizeDefects', and populate with the record details which reside in the table 'tblWarranty'?

So not to confuse anyone:

Main Claim Entry Form - 'frmWarrantyClaim' (Where user inputs all records related to claim. Once saved, each claim is given a 'ClaimNumber' which is more or less used in replace of the table ID w/o using an autonumber. These records are saved in the table 'tblWarranty'. At the end of the month, the user opens the 'frmPrintReport' form and selects a button to prioritize that months claims. Upon pressing the button, the make-table query 'qryPriority' is run and the user is asked to input a mm/yyyy. The query then, I'm hoping' will just update the table 'tblDefects' with a list of all Defects that claims were filed under and then the sum of claims filed under a specific defect. These details are then used to populate the form 'frmPrioritizeDefects' where the user sees a list of Defects, the total number of claims for each defect and Priority/SortOrder sequential numbers starting w/ 1. The user will then sort each Defect in an order that they would like to see the 'WarrantReport' created in. For the 'WarrantyReport', each GroupHeader notes the Priority #1 Defect and also the total claims for that defect. Under the GroupHeader, the Detail will list each claim, filed under the Defect noted in the GroupHeader, along w/ all of the records in that claim which are stored in 'tblWarranty'. So, if there were (5) claims filed under the Defect 'Defective Part', the GroupHeader would display 'Defective Part' and then the number '5'. In the detail, below the GroupHeader, each 'Defective Part' claim will be populated starting w/ #1 & then finishing w/ the #5. Then the next Defect, set on the SortOrder form will populate the report and so on until all defects within the set mm/yyyy have been reported.

Phew!!! lol Thanks in advance for the help my friends! :eek:
 

TheBigEasy

Registered User.
Local time
Today, 18:23
Joined
Dec 12, 2019
Messages
19
Screen shots of a few items to help in understanding what I'm trying to do...

EDIT - Guess I need to post some more in order to be able to upload images. So sorry for the links! :)

Edit 2 - See below!
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:23
Joined
Jan 14, 2017
Messages
18,189
Please can you re-upload the images here instead of externally

For info, you could upload images from the start as long as they were zipped.
Now you have 10 posts, you can upload your images here without zipping
 

Users who are viewing this thread

Top Bottom