Create a query table name from combobox

lone_rider15

Registered User.
Local time
Today, 19:57
Joined
Nov 6, 2016
Messages
32
Hello Everyone,

I have 7 tables named with 7 days of week but has same data structure. I can't put them all in a single table because for example in Monday (22/11/16)'s file I have data from Sunday(20/11/16) and Monday (21/11/16), On Tuesday's file I have data from Tuesday (22/11/16) and Monday (21/11/16) and so on. I want to make a query which will take the table name from the selection of a combo box "cboWorkingDay" and return random 5% of the data. And then make a table named with the selection of another combo box "cboUserId". If the table already exists then just add the data.

In a nutshell:
Step -1: Select 5% FROM cboWorkingDay Where UserID=cboUserId
Step - 2: Then make table or copy data to the table named CboUserId
I have attached my sample database.

Hope someone can point me to the direction.

Thanks in advance.
 

Attachments

Last edited:
Hello Everyone,

I have 7 tables named with 7 days of week but has same data structure. I can't put them all in a single table because for example in Monday (22/11/16)'s file I have data from Sunday(20/11/16) and Monday (21/11/16), On Tuesday's file I have data from Tuesday (22/11/16) and Monday (21/11/16) and so on. I want to make a query which will take the table name from the selection of a combo box "cboWorkingDay" and return random 5% of the data. And then make a table named with the selection of another combo box "cboUserId". If the table already exists then just add the data.

In a nutshell:
Step -1: Select 5% FROM cboWorkingDay Where UserID=cboUserId
Step - 2: Then make table or copy data to the table named CboUserId
I have attached my sample database.

Hope someone can point me to the direction.

Thanks in advance.
Sorry - but that is a completely wrong setup and against the rule of a database - only store the same data once. And your argument for having 7 tables convinces nobody who works with databases

If you want to look at/compare the data for one day with another day, the use a query to pick out the two days in question.

For what use do you want to create and fill the table CboUserId with data you already have once?

Sorry that my response sound negative.
Explain where you've problems, then we'll help you.
 
Sorry - but that is a completely wrong setup and against the rule of a database - only store the same data once. And your argument for having 7 tables convinces nobody who works with databases

If you want to look at/compare the data for one day with another day, the use a query to pick out the two days in question.

For what use do you want to create and fill the table CboUserId with data you already have once?

Sorry that my response sound negative.
Explain where you've problems, then we'll help you.

Thank you for your valuable input. I have decided to go with a single table for the data. I was able to get 5% sample for a user in a day. In a day there might be 30 to 100 users for which I have to run the query. And for each user the query has to run twice based on 2 criteria. So for a day the query has to run from 60 to 200 times. In the sample database in January 1, 2017 there are 13 users. Is there any way to do this automatically. I have updated my sample database in the attachment with my progress.

Hope someone can take a look into my database and point me to the direction.

Thanks in advance.
 
.. I was able to get 5% sample for a user in a day.
Is the data in the table "tblInquiries" the 5%, if not, where can I see the 5%?
..
In a day there might be 30 to 100 users for which I have to run the query. And for each user the query has to run twice based on 2 criteria. So for a day the query has to run from 60 to 200 times. In the sample database in January 1, 2017 there are 13 users. Is there any way to do this automatically. I have updated my sample database in the attachment with my progress.
Which query need to be run for each user, and why does it need to be run twice? What is the criteria?
Where does the result for each query run get stored?
Maybe, you're over complicate things, I say only maybe, then I don't know what result you want to get, but immediately there is nothing in your description that not can be done with one or at most two queries (runs).
Could you show the result you want, from the data you already have in the database?
 
Is the data in the table "tblInquiries" the 5%, if not, where can I see the 5%?

Which query need to be run for each user, and why does it need to be run twice? What is the criteria?
Where does the result for each query run get stored?
Maybe, you're over complicate things, I say only maybe, then I don't know what result you want to get, but immediately there is nothing in your description that not can be done with one or at most two queries (runs).
Could you show the result you want, from the data you already have in the database?

The data in the table "tblInquiries" are the population and the sample 5% will also be there but "AuditStatus" field will be "Check". I took a sample for the first user (b_d_amtanna") only. The user has 257 inquiries. Out of these 117 are "II" SearchType and 140 are "MI" SearchType. So the sample size for "II" should be 6 and "MI" should be 7. You will see the User "b_d_amtanna" has 13 inquiries as "AuditStatus" filed "Check".
The update query "qrySampleToCheck" has to be run twice for each user because of the criteria "II" and "MI". One for "II" and one for "MI" The query will make the AuditStatus of the sample "Check". These samples will be the record source of a form to audit the samples. Once the quality check is done the AuditStatus will be "Complete". When all of the "Check" get status "Complete", I'll delete the null AuditStatus inquiries. Then only the samples quality checked will remain in the table. Based on that I'll make a quality scorecard for the user.

Hope this makes a better sense.
Thanks again for looking into this.
 
I've made a sample for you in the attached database.
Open the form "JHBPickSample" put in a date, and click the "Run Query" button.
But there is a problem when the 5% sample to be taken is marked, and if you run the query again, further 5% is marked. You need to mark all for dates which has already has 5% samples marked.
For the testing I've made a query which set AuditStatus back to null "JHBClearAuditStatus"
 

Attachments

I've made a sample for you in the attached database.
Open the form "JHBPickSample" put in a date, and click the "Run Query" button.
But there is a problem when the 5% sample to be taken is marked, and if you run the query again, further 5% is marked. You need to mark all for dates which has already has 5% samples marked.
For the testing I've made a query which set AuditStatus back to null "JHBClearAuditStatus"

Sir, I can't thank you enough for your help. Without your help I couldn't have come so far with this.
I have created a navigation form that will load with Dashboard open. In Dashboard in a report I marked the dates from which samples have been picked. So I won't pick samples more than once for the same date.
May be I'm asking too much but I need your help further. I have 5 associates (User Name: b_d_auditor1 to b_d_auditor5 with password abc-123)who will use the system. I have created a login form.
I don't want to assign any records to any user. What I want is that when a user logs in, a record will get assigned to that user and Audit status might be "Assigned" so that the same record does not get assigned to more that one user. When the user clicks on "Begin Review" he will get that record through the associated form. After he is done with the record he will click "Complete" button of that form and get a new record. When the user logs off it'll make his assigned record's audit status "Check" so that it can be assigned to another user.
My updated sample database is attached.
 

Attachments

...What I want is that when a user logs in, a record will get assigned to that user and Audit status might be "Assigned" so that the same record does not get assigned to more that one user.
I don't quiet understand that part, does he get assigned one record from all records which is marked "Check" or ???
Will it be a random record he will be assigned and if yes, how do you "find" a random record?
...
When the user clicks on "Begin Review" he will get that record through the associated form. After he is done with the record he will click "Complete" button of that form and get a new record. When the user logs off it'll make his assigned record's audit status "Check" so that it can be assigned to another user.
Have you done any thoughts about how you'll manage which record is assigned to which user?
 
I don't quiet understand that part, does he get assigned one record from all records which is marked "Check" or ???
Will it be a random record he will be assigned and if yes, how do you "find" a random record?

The user gets assigned one record from all records which is marked "Check".

Have you done any thoughts about how you'll manage which record is assigned to which user?

May be the first in the queue or last. I'm open to any suggestions.

Thanks again.
 
But how will you find the particular record, the first from the top or how?

I don't think that would work, how would you keep track on which user is the first, the second ... the last?

Can't you use the field "Assigned To"?

I have created a query "qrySelectToAssign" and then another update query to assign the selected record to the user.
Code:
SELECT TOP 1 tblInquiries.InquiryMatchId, tblInquiries.AuditStatus, tblInquiries.AssignedTo
FROM tblInquiries
WHERE (((tblInquiries.AuditStatus)="Check") AND ((tblInquiries.AssignedTo) Is Null));

Code:
UPDATE  qrySelectToAssign SET qrySelectToAssign.AuditStatus = "Assigned",  qrySelectToAssign.AssignedTo = [Forms]![frmLogin]![txtUserID];

I have added the update query to the form's on load event on condition that it will run if there is no record in the record source. It is working fine.
I have also added the update query to the on click event of the button "Complete" of the form.

Problem: 1 - After I change AuditStatus to "Complete" and click "Complete" button the record gets saved but I have to click again to get the next record.
How can i solve this?
Problem:2 - If more than one user complete at the same moment there is a chance that they will be assigned to the same record.
Is there any way to solve this?
 

Attachments

Replace what you've with the below:
Code:
Private Sub AssignReview()
    Dim rst As Object
    Set rst = Me.RecordsetClone
    If rst.EOF Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryAssignReview"
        DoCmd.SetWarnings True
        Me.Requery
    Else
        Me.Requery
    End If
End Sub
Look at the "Record Locks" I think you can use that for problem 2.
You are aware of you need to split your database in a Front-end (one for each user) and a Back-end database, are you?
 
Look at the "Record Locks" I think you can use that for problem 2.
You are aware of you need to split your database in a Front-end (one for each user) and a Back-end database, are you?

Thank you sir. That code solved the first problem.
Yes, I am aware of splitting the database.
Can you please tell me a bit more about the "Record Locks"? How should I proceed to solve the second problem?
 
Look at the "Record Locks" I think you can use that for problem 2.

I got the idea of RecordLocks Property. If I set the RecordLocks Property to Edited Record, I guess my qrySelectToAssign will get locked since a page of records will be locked and qrySelectToAssign will try to select 1 record from top. How do I tell programmatically to look for the first record which is not locked?
 
Last edited:
Thank you sir. That code solved the first problem.

But now I see another problem. If I log off from the database while working on a record and log back in and complete the record, I do not get another record until I move to another form and back to "Begin Review":banghead:. How can I solve this?

Thanks in advance.
 
But now I see another problem. If I log off from the database while working on a record and log back in and complete the record, I do not get another record until I move to another form and back to "Begin Review":banghead:. How can I solve this?

Thanks in advance.
But in post #7 you wrote:
When the user logs off it'll make his assigned record's audit status "Check" so that it can be assigned to another user.
So how does that harmonizes with that you log in again and complete the record?
 
Quote:
When the user logs off it'll make his assigned record's audit status "Check" so that it can be assigned to another user.

But in post #7 you wrote:
So how does that harmonizes with that you log in again and complete the record?

I'm sorry, I completely forgot to mention that I did not make this happen to get a user's assigned record's audit status "Check" while logging off. I wanted to give users a chance to take a break and resume working in the same record after break. I'll make an update query to change the Audit Status to "Check" if a user is done working for the day.
Can we solve the newly arisen problem?
Thank you.
 
Can we solve the newly arisen problem?
Can you, (I can)? :D
Code:
Private Sub AssignReview()
    Dim rst As Object
    
    Set rst = Me.RecordsetClone
    Me.Requery
    If rst.EOF Or rst.RecordCount = 0 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryAssignReview"
        DoCmd.SetWarnings True
        Me.Requery
    End If
End Sub
 

Users who are viewing this thread

Back
Top Bottom