Local data for query in split Access database (1 Viewer)

[dlambert]

Hello all,
I have the following situation within my database:
tblJobs is my list of job numbers
tblWorkHours is my list of hours worked by the various employees of the company, these work hours are linked to a job number number from tblJobs

I have set-up a query that effectively acts as a filter so i can display the work hours for a set of selected job number (not just one job number at a time).
The way i am doing this is in the tblJobs table i have a field called 'ShowInList' which is a Yes/No field, this field is modified through a form. If a job number is set to Yes, it
will display in my query.

This is working OK up to now, but my problem arises when i split the database in the local office network so it can be used my multiple users.
Of course what is happening now is that if two users try to use the filter form they are forced to have the same job number selection as this is done directly in the tblJobs table which is common to all users accessing the data...

Would anyone have any advice on how i can get around this problem?
So i would like to be able to select multiple job numbers from my tblJobs list (so that all work hours from tblWorkHours that matches the selected job numbers in tblJobs can be displayed in a query), but in such a way that each user of the split database can have their own job number selection.

 

[Minty]

Your approach isn't suitable for a multi-user environment as you have discovered.
You should do the Job number filtering locally from a form, maybe by date or some other field so that the job list is created locally to the person using the local front end.
Is a job assigned to a specific user ? You could filter locally to the user?

It might be handy to see your tables and their relationships to see if we can "see" a better technique.

 

[Gasman]

Change the field to hold username and select off that?

 

[The_Doc_Man]

We talk about having to split the DB but there are cases where you can legitimately "unsplit" a table or two - make it one table per user - by putting it back into the front end. The problem THEN becomes that you cannot establish a relationship between tables in FE and BE because relationships must be in the same file to be recognized as such. It turns out that this situation leads to an easy means of filtration. This is not for everyone, but it worked really well for me as a way to reduce the complexity of the queries.

The method I used was I had a local (FE) table that was almost amorphous. It had a few fields in it but its content was strictly temporary. I would do whatever required selection of something (as you describe as your "set of job numbers" case). In my case, I would put project numbers in this temp table and then have my query do a

SELECT mumble-fratz,  project, more-junk FROM main-table WHERE project IN (SELECT Fld1 FROM TempTable) ;

In this case, TempTable was in the FE and main-table was in the BE. But the "IN (sub-query)" solution doesn't require a relationship. And since TempTable was USUALLY no more than three or four project IDs (out of a possible 80 at any given time), the time required was practically nil. It just took that extra step of erasing and populating the selection list.

Now, the downside of this is that it would lead to FE bloat. BUT if you used the method of automatically downloading a fresh copy of the FE each time you launched the app, the bloated old copy would go away to be replaced by a clean copy each time. No harm, no foul. You would search this forum for a discussion of the Auto-Download of the FE because it is a popular topic.

 

[Isaac]

I have generally solved this by encouraging the users to have me build a workflow for users that involves assignments and queuing. Have users (or mgrs) assign work to specific people, then have them look in their queue for that work. Most users will immediately recognize and acquiesce to the concept that if work is not assigned per user, there will be people barking up the same tree.
This backs up to a higher level and solves the problem - it solves a lot of problems, actually.
May not be able to do that, but I wanted to throw in the idea to get you thinking.

 

[Pat Hartman]

I have created workflows similar to what Isaac suggested. In my case, as a user selected an account from the ToDo list, I updated the account record with the date and that user's ID. That allowed other people to still view the record but no one else could "select" it for processing until the first person released it. As people logged in, they were presented with a form showing all currently "selected" accounts and urged to clear them so they could be released. Supervisors were allowed to clear "selections" if necessary.

We can't tell if you want only a single person to be allowed to select any given record at one time or you want to allow multiple people to select multiple records. In the M-M case, you need a separate table anyway so there is no reason to create a local table. Create a table with

SelectedID (PK field 1)
UserID (PK field 2)
SelectDT

This allows the ID to be selected only once for any given user. It also lays the ground work for a delete query that clears the selections for a specific user.

To show the selected data, join to this table in your query and include criteria that references UserID.

If only a single person can select any given record, just use the UserID and Date as someone already suggested. You will need UserID as criteria when selecting records marked by a user. You will also need an update query so you can have an easy way to unmark records.