UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Refresh One Query For Several Subforms, Access 2010    
 
   
ZippyThePinhead
post Dec 27 2017, 11:17 PM
Post#1



Posts: 45
Joined: 11-December 17



Main screen continually displays three subforms that present very similar information taken from the same set of tables. Records move back and forth among the three tables based upon a few criteria. Right now, the forms have three separate queries, and all three need to run every time a record moves from one subform to another. My server is slow and the three separate queries are bogging things down.

I want to try using the same query for all three and then use filters to move the records back and forth. That should allow me to use a front end refresh rather than a series of back end queries whenever a record moves. So far, so good, and things do seem to be much faster. I'm able to issue a single command, forms!mainform.form.refresh, and almost instantly show the rearrangement of records with that single event.

Here's the question: how do I handle the addition or deletion of a record to this arrangement? I know I have to requery rather than refresh, but I'm not sure how to do it. Could I requery just one of the forms (thereby updating the background query to add or delete the record) and then have my refresh command take care of the other two? Is there a way to have all of this happen with a VBA command so I don't have to single out one form for a requery (which I think might be confusing when I look back at the code at some future date)?
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 07:58 AM
Post#2


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


I'm not clear on the work flow here.

What does it mean to say that the records "move back and forth" between subforms? How? Why?

--------------------
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 09:38 AM
Post#3



Posts: 45
Joined: 11-December 17



it's basically a before, during, and after listing of work to be done. records for the date start in the before list, then move to the during, then the after. basic ID information is shown in all three tables but each also has some additional information relevant to that part of the task. In the past, moving a record from one form to the other was done by requerying all three, but since much of the info in each is the same, I thought that running all three from the same query and then using filters to move from one to the next would be faster. Turns out is is, but I still don't know the most efficient way to add an entirely new record to the group.
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 10:22 AM
Post#4


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Okay. You have three tables, all containing data related to "work".

Is there actually a "Work" table, a master table of all projects, or work to be done? And then tables for "Before", "During" and "After" processing?

If so, good. If not, we may want to rethink some things.

First, the master project table contains fields only for attributes pertinent to any and all work that comes in.

Then, you assign the Primary Key for each Work item as a Foreign Key in the "Before" work table where fields are available for attributes pertinent only to work in the "Before" state. ONLY that Foreign Key is required in this child table, no "basic ID information" beyond that is needed.

Next, there is a "During" work table, which also contains ONLY the Foreign Key relevant to each Work Item, along with the fields for attributes pertinent only to work in its "During" state. As with the first child table, copying any other "basic ID information" beyond the Foreign Key would be redundant.

And finally, there is an "After" work table, again containing ONLY the Foreign Key relevant to each Work Item, along with the fields for attributes pertinent only to work in its "After" state.

So, you do NOT "move" records from one table to another in database terms, although in the business world, the items physically might move from one location to another, or from one state to another, the database tracks only their progress and does so by adding new records to each child table as appropriate. No records are--or rather should not be--deleted from a prior table and "moved" to the next.

Actually, despite the slightly different terms of the description, this may well be what you are currently doing, but to be clear I'm reviewing the normal approach.

So, the main form is bound to the master work table. Subform one is bound to the "Before" work table. The master-child linking field here would be the Primary Key from the master "Work" table and the Foreign Key from the child "Before" table. However, to ensure that no records are displayed in that subform after they have been "moved along" the process, the query which provides records for this subform should join the "Before" table to the other two processing step tables like this:

SQL
SELECT PrimaryKey, WorkID, FieldOne, FieldTWO
FROM tblBeforeWork WHERE (tblBeforeWork.WorkID Not In (Select WorkID FROM [tblDuringWork])) And (tblBeforeWork.WorkID Not In (Select WorkID FROM [tblAfterWork ))


The second, "During" work subform's recordsource would be:


SQL
SELECT PrimaryKey, WorkID, FieldOne, FieldTWO
FROM tblDuringWork WHERE tblDuringWork.WorkID Not In (Select WorkID FROM [tblAfterWork )


The third, "After" work subform's recordsource would be:


SQL
SELECT PrimaryKey, WorkID, FieldOne, FieldTWO
FROM tblAfterWork


Using this syntax allows you to add new records to the second, "During" work subform and to the third, "After" work subform.

Requerying them, or refreshing them, keeps them in synch.

Note that I haven't allowed for the potential problem of someone trying to add a record to the "During" or "After" tables out of sequence. That can be handled with Referential Integrity at the table level.

--------------------
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 11:29 AM
Post#5



Posts: 45
Joined: 11-December 17



No, there are no tables related to the before/during/and after state. Nor is there a single table that shows a given day's work that could be divided up as you describe. Instead I have a table that lists all appointments for all days and it links to other tables that hold client data and some specific details about individual jobs. TJobs is many to one with TAppointments, which is many to one with TClients. I should also clarify that the three forms are actually NOT subforms in the usual sense. They are actually separate forms, with separate queries, that display simultaneously. They are located on a main form, but there is no master/child relationship. The main form just has some button controls and an unbound field holding the date in question. The forms are populated by queries that use that date as their main criterion, and then use a before/during/after criterion to separate the records. That's one of my concerns: "moving" a record requires three queries of the same large table (TAppointments), and that series needs to be run for each user who's logged in. One additional important fact is that records in the TJobs table are created as the record moves from the during to the after status.

Now that we're talking about it, I guess what makes more sense is to redesign things such that there is, in fact, a table for daily work that is populated over time as the work is scheduled, rather than relying on a query that runs later, on the fly, when a given date is displayed. That table would include the date and a foreign key identifying the appointment. Then I could use that table as the recordsource for the main form and make the other forms into true subforms with the appointment identifier as the master/child field. Am I correct that I could then move records through the before/during/after sequence by changing the status criterion and refreshing the subforms, and only requery the main form when a new record is added in real time to the day's schedule? Again my goal is to reduce the number of backend queries because that's what kills my speed.
This post has been edited by ZippyThePinhead: Dec 28 2017, 11:36 AM
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 11:51 AM
Post#6


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Sorry, I'm not so good with visualizing concrete structure from abstracted descriptions.

So, "moving" a work item involves what, exactly?

Is it possible to upload a copy of the current accdb, with just enough sample data to see how this all works? Remove any sensitive or confidential data if you can upload it. Compact and Repair the cut-down copy, compress it into a ZIP file and upload it.

Thanks.


--------------------
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 12:07 PM
Post#7



Posts: 45
Joined: 11-December 17



I'll see what I can do about uploading a copy.

Moving just means changing the status such that the record appears first on the before form (along with information appropriate to the before status), then on the during table, (showing information that's relevant there, such as arrival time), and then finally on the after form when the visit is over. It's essentially the same data set, but each form shows different information that applies to the current before/during/after status. What I'm trying to do is to accomplish that change from one form to the next without having to do a requery of each.
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 12:18 PM
Post#8


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


That sounds more and more like a wide, flat table with multiple columns having to do with the different statuses.

--------------------
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 07:17 PM
Post#9



Posts: 45
Joined: 11-December 17



Yes. It's a wide table, which is why I want to limit queries. I can't really normalize it much from where it is.
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 09:14 PM
Post#10


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Why would you not normalize as much as possible? It's the way relational databases like Access work best. You're here with this problem involving three subforms based on different sections of a non-normalized table. That ought to be a strong signal that fixing the table design problem is good idea.

I outlined what I thought the tables were--and should be. Why not?

--------------------
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 10:27 PM
Post#11



Posts: 45
Joined: 11-December 17



The problem is that I can't really separate the before, during, and after data. It's acquired as the client moves through the visit, and displayed as it's collected. The main difference between forms--other than the changing status--is that each form has more progressively more information. The only table that can realistically be pulled out is TJobs, because that info is created at the very end, but that's already a separate table. The main reason I've used separate queries is so that I don't pull a bunch of blank fields for the before and (to a lesser extent) the during forms.

Example: Before includes client name, the date requested for a visit, and the jobs requested. During includes the same information but adds the scheduled visit day and time, the room to be used, the staff assigned, and some similar data; After includes all of the information from the other two forms, and also shows the time the visit ended, the jobs actually done, and a few more wrap-up items.
Go to the top of the page
 
ZippyThePinhead
post Dec 28 2017, 10:38 PM
Post#12



Posts: 45
Joined: 11-December 17



I could certainly create a new table that holds the date and client identifier, use that as the recordsource for the main form, and then populate the three forms--which would then be true subforms-- using a master/child setting based on the client ID. I guess my question is whether that would be any quicker than populating the three forms using separate queries like I do now. In other words, could I then use a refresh to reflect changes in the status on the subforms or would I still have to do separate requeries?
Go to the top of the page
 
ZippyThePinhead
post Dec 29 2017, 02:52 PM
Post#13



Posts: 45
Joined: 11-December 17



Okay, I tried my own suggestion and it didn't work because (duh) the main form only selects the date, not the client, so the master/child field was of no use. And using date in the master/child form is really no different from what I'm doing now, which is to run my queries using date as the selection criterion.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 10:25 PM