Netflix-like Queue for Form?

[tkepongo]

This is a cross-post from dbforums.

My boss wants a Netflix-like queue feature on a continuous form. The user will select items and add it to a 'checklist'. This 'checklist' is a continuous form. The user should be able to sort the continuous form results by adding a rank to each result (like Netflix). I was thinking of having the continuous form look like:

Rank***Audit_ID***Question
___ ***5222212*** Blah
___ ***5222212*** Herp
___ ***5222212*** Derp


The user would just type a number in the "Rank" column and the continuous form will re-query and sort by Rank Ascending. The Access Report should also sort by Rank. My only question is, how do I code it so that you can't type in the same number twice and is there any better way to do this?

Thanks!

 

[vbaInet]

I don't know what Netflix is and I'm sure I'm not alone on this

Is this a multi-user database?

 

[tkepongo]

I don't know what Netflix is and I'm sure I'm not alone on this

Is this a multi-user database?

Haha sorry about that. Netflix is an online streaming website for movies and tv shows. Here's an image of what their queue list looks like.

Yes, this is a multi-user database but at most three people at a time editing their own audit information so the likelihood of write conflicts is very unlikely.

 

[vbaInet]

How may records are you looking to sort?

 

[tkepongo]

How may records are you looking to sort?

I will have to say at most 20 per audit.

 

[tkepongo]

The business process is:
1. User selects an audit (#123)
2. A form opens and allows the user to add questions to audit #123 from a list of questions in the db
3. As each question is added, a continuous form at the bottom requeries and displays the questions assigned to audit #123
4. My boss wants that continuous form to have the queue sorter thingy

 

[vbaInet]

Not bad. It should be manageable.

I posted a sample database on how to do somethins similar to your request some months back but can't find it on here. I can get it to you in about 2 hours time.

There are three options:

* Create a local temp table on each user's front end. This will fetch data from the back end table. This will allow the user perform any sorting without any locking issues.

* Use a Dictionary object - this was my approach, no local temp table required.

* Create the 20 records for each user on a new table and filter by User ID and sort by that required field.

 

[tkepongo]

Not bad. It should be manageable.

I posted a sample database on how to do somethins similar to your request some months back but can't find it on here. I can get it to you in about 2 hours time.

Great! Thank you very much

 

[vbaInet]

I added some more info above

 

[tkepongo]

Not bad. It should be manageable.

I posted a sample database on how to do somethins similar to your request some months back but can't find it on here. I can get it to you in about 2 hours time.

There are three options:

* Create a local temp table on each user's front end. This will fetch data from the back end table. This will allow the user perform any sorting without any locking issues.

* Use a Dictionary object - this was my approach, no local temp table required.

* Create the 20 records for each user on a new table and filter by User ID and sort by that required field.

Hmm...the queue order needs to remain permanent for the auditor to use weeks later. I think that eliminates option 1 and 2.

For option 3, we don't need to have a custom sort by each user; just a general rank that will remain the same for all users. So...what do you suggest?

 

[vbaInet]

Another table to hold the ID of the records, the AuditorID and the SortOrder. This will require that you perform updates, deletes and inserts manually to this table anytime a record gets updated, deleted or added. Or if enforce the Cascade Update/Delete you're left with a manual intervention on the inserts.

 

[tkepongo]

Another table to hold the ID of the records, the AuditorID and the SortOrder. This will require that you perform updates, deletes and inserts manually to this table anytime a record gets updated, deleted or added. Or if enforce the Cascade Update/Delete you're left with a manual intervention on the inserts.

Sorry, I've been a little busy...

So, I don't really understand why the table has to have the AuditorID. If we don't need to have custom sorts by AuditorID, wouldn't the current table with SortOrder column be sufficient?

 

[vbaInet]

How do you relate each Auditor to their sorts?

 

[tkepongo]

How do you relate each Auditor to their sorts?

I don't. All the questions are stored in a QuestionAudit junction table that holds Audit_ID and Question_ID. We only need the sorts to be related to the Audit_ID.

 

[vbaInet]

You've answered your question. The Sort table needs to have Auditor_ID to allow you relate the sorts to the Auditor.

 

[tkepongo]

You've answered your question. The Sort table needs to have Auditor_ID to allow you relate the sorts to the Auditor.

I don't understand why I need the sort table though. I think we're on different pages here.

My current junction table looks like
Audit_ID***Question_ID
***1*******101*****
***1*******102*****
***1*******103*****
***1*******104*****

If I want to keep track of the SortOrder by Audit_ID (not Auditor_ID) wouldn't adding a SortOrder column be sufficient?

Audit_ID***Question_ID***SortOrder
***1*******101*********1*****
***1*******102*********2*****
***1*******103*********3*****
***2*******101*********1*****
***2*******102*********2*****
***2*******103*********3*****

I don't see why creating a Sort table would be necessary if I can just add a column. I understand that creating the extra table would allow each auditor to have their custom sort order, but my boss doesn't care about that (for now at least).

 

[vbaInet]

It's up to you. If you want to use that junction table or if you want to split it further and create another Sort table which will be used exclusively for sorting.

 

[tkepongo]

It's up to you. If you want to use that junction table or if you want to split it further and create another Sort table which will be used exclusively for sorting.

Just spoke to the boss and they want to go with the junction table because there's only one auditor per audit, so having the extra table wouldn't be necessary.

If I made a continuous form for Audit#123 that displays the questions associated with it, is there a way to allow the user to put in a number in the SortOrder but will overwrite the SortOrder of a Question with the same SortOrder?

For example, if Question 101 has a SortOrder of 1 and then I put 1 in SortOrder for Question 102, then Question 101 will have SortOrder 2 and Question 102 will have SortOrder 1. And then somehow give Question 103 SortOrder 3.

*Question_ID***SortOrder
***101*********_2_*****
***102*********_1_*****
***103*********_3_*****

Another alternative is to allow the user to just put in duplicate SortOrders and then let them manually adjust it. Less coding

 

[vbaInet]

It is possible but you are going to complicate things. Your auditors will get confused and be like -->

 

[tkepongo]

Hmm...what do you suggest? LOL I don't know how to approach this problem anymore!