Prioritizing Data in a Report

[TheBigEasy]

Hope that worked!!!

 

[TheBigEasy]

I know someone here can do a solid and help this guy out! Please!?

 

[June7]

Why don't you provide the db for analysis.

 

[TheBigEasy]

Why don't you provide the db for analysis.

Hi June...I'm shooting you a PM.

 

[TheBigEasy]

Need to make one more post in order to PM so here it is!

 

[Pat Hartman]

Actually, I do have a make table query that generates and stores all of the information needed for the report.

It is extremely unlikely that you actually need to do this. Making temp tables rather than binding a form/report to a query just bloats the database.

If you want to sort the data by priority and that priority is defined by defect, then add priority to the defect definition table. Left join to this table to pull in priority. Chang your sort (using sorting and grouping options NOT your query) to sort first by priority and second by count of defects. You want the two field sort so that items with the same priority or "no" priority will still sort by count.

To give yourself some flexibility, number the priority by 100's to allow gaps for you to adjust the sequence without having to renumber the entire list. Make the default,9999 or something higher than the higlest actual value. You will have to run an update query first to set the 9999 values before you set the field to required and update the default value. Then change whichever defects you want to assign priority's to.

 

[TheBigEasy]

It is extremely unlikely that you actually need to do this. Making temp tables rather than binding a form/report to a query just bloats the database.

If you want to sort the data by priority and that priority is defined by defect, then add priority to the defect definition table. Left join to this table to pull in priority. Chang your sort (using sorting and grouping options NOT your query) to sort first by priority and second by count of defects. You want the two field sort so that items with the same priority or "no" priority will still sort by count.

To give yourself some flexibility, number the priority by 100's to allow gaps for you to adjust the sequence without having to renumber the entire list. Make the default,9999 or something higher than the higlest actual value. You will have to run an update query first to set the 9999 values before you set the field to required and update the default value. Then change whichever defects you want to assign priority's to.

Pat,

Thanks for the reply! I'm going to do some experimenting and I'll report back if I have any questions. Since I posted my latest question, I actually did add a priority column to the tblDefectCount table. I've been playing around trying to make this method work. Glad that you confirmed I was heading in the right direction.

Thank you!

 

[June7]

This sounds good if you want standardized prioritization. I had understanding you wanted users to assign priority on case-by-case basis, using their judgment. Ideally, those judgments would be saved into a dependent table as part of data entry process so that report could be recreated on demand.

 

[NauticalGent]

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.

Concur with June7's assessment. DB management comes easier to some than others. You seem to have a knack AND the gumption to see it through.

Best of luck going forward

 

[Pat Hartman]

You're welcome.

 

[TheBigEasy]

This sounds good if you want standardized prioritization. I had understanding you wanted users to assign priority on case-by-case basis, using their judgment. Ideally, those judgments would be saved into a dependent table as part of data entry process so that report could be recreated on demand.

June,

Sorry for the late reply as I took a much needed break from Access over the holidays. Looking back, you are correct in stating that I'd like to have the users assign a priority on a case-by-case basis. My apologies.

I'm still struggling with how to get the claims to print in the priority order that the user sets. All that the temp table contains is the 'Defect', the total number of each 'Defect' and the Priority Ranking. Now, each separate 'Defect' has a Claim associated w/ it. The details within the Claim are what needs populated into the report in the order set by the user. After the priorities are set, I'm not sure how to go about breaking out each 'Defect' so that it's pulling in the correct claim info into the report and ordering it correctly.

Halfway there! If anyone has any ideas///I'm all ears!

Happy 2020 to all!