Thanks for the replies. The Master record table is simply a collection point for activity records. We work with people having multiple levels of disabilities. Some activity is paid production work, while others are unpaid community events or personal services. Any particular activity is viewed simply as an event. There are separate tables for all the demographics, customer, funder, personal info, family and other data as you have discussed and in the relational discussion that is going on. The master record keeps info that does not change like personal ID and activity ID, but also changeable info specific to the activity: event date, start/end times, work volume, total paid, task, Medicaid billing codes, progress toward a goal, behavior notes, and who entered the data/time/date/computer. Using the personal and activity IDs I can get all the other info.
I am going to look at some preliminary way to review the activity data before it gets stored in the Master activity record. Tom Frederick President/CEO Elm City Center 1314 W Walnut Jacksonville, IL 62650 W- 217-245-9504 F - 217-245-2350 E - [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of A.G. IJntema Sent: Wednesday, February 22, 2012 2:05 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Master Records directly or through temp tables Tom, I agree with Dan about using temp tables. But another solution crossed my mind. Do you have considered to make use of a Temporary View based upon the master table. If the view is based upon one table it is updatable and it can be considered and used as a table If you solve the problem this way you are able to create a temp view per computer / user If the view has the right where clause, rows continue to stay available until all conditions have been fulfilled. Then you could also create a temp view for the final check. In this case the where clause also is crucial. If defined in the right way a row automatically could become available when finished on the local computer. In this way you'll create more or less a workflow without any programming. The nice thing of using this solution is that you make use of the nice things of temporary tables / views , but you are not vulnerable for computer outage. Hope this helps Tony IJntema From: [email protected] [mailto:[email protected]] On Behalf Of Dan Goldberg Sent: dinsdag 21 februari 2012 20:15 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Master Records directly or through temp tables I would have them all go directly into the master record table. You can setup the input form so when the user enters a bill that they can check for duplicate billing before saving. You can also put some sort of approval field, so that you can filter bills that have not been final checked. I would not use temp tables to store data before merging. If there is a computer outage, the data is gone. The only times I use temp tables is to speed up processing for reports/exports, merging different data-sets, etc. Hope this helps. Dan Goldberg From: TFred<mailto:[email protected]> Sent: Tuesday, February 21, 2012 10:55 AM To: RBASE-L Mailing List<mailto:[email protected]> Subject: [RBASE-L] - Master Records directly or through temp tables We are expanding our service billing capability and have become stuck on a good way to do this. Info can come from any number of computers, but most will be through 2-3 office machines. We currently check for typos, billing overlaps, and other errors. Right now everything goes directly to a master record table and then uses a form to ID a date range to check the selected bills. It works. The question becomes: as we allow more sites to enter their billing info is there a best way to do this with the least likelihood of problems? 1. Send all info directly to a master record table. This makes sure all the data is collected and problems are resolved later. Conflicts should be minimal but are likely as existing data is checked while rows get added. Final checker may not always see the problem. 2. Send data to temporary tables at each computer as it is entered and review the temp for problems, then append it up to the master table. The temporary nature of temp tables means stuff could happen before a temp is appended and data gets lost. At the same time, hopefully, the person entering will see a problem long before the final checker. Final checker still has to check for overlaps and errors in the master table. 3. Looked at regular tables for each computer but that seemed like asking for problems that temp tables solve. Any other methods people use? Tom Frederick Jacksonville, IL
