Results 1 to 4 of 4
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Multiple queries or create a table?

    I have a budget form used to create budgets for each fiscal year. The form includes several different controls displaying data needed to create the budget, by budget subaccount: things like total dollars already obligated (by contract) in the fiscal year, planned purchases not yet obligated but serving as placeholders, etc. This is to ensure that the user budgets at least that much to cover expected expenses for that budget subaccount.



    To populate a couple of the controls (on the continuous form), I have to run a series of queries. The queries are basically teasing apart data for three different types of "orders", doing some calculations on them related to fiscal year amortization, then bringing them back together in a union. The queries work just fine (all seven of them!) to find the appropriate sum, then I use a DSUM lookup for the budget subaccount and department to populate the control.

    However, when I open the form there is a second or two delay in populating the controls. I only have a very small set of test data (116 records) in the database right now. This number will balloon very quickly once I enter ALL the contracts. I am concerned the queries will take so much longer so as to make the form unusable.

    So my questions are these:
    1) Is the speed of a query directly inversely proportional to the volume of data in the database? Or does the growth of data over time only marginally contribute to slowing a query?
    2) If my concern is valid, what are some options (from a generic design point of view)? Budgets are created separately from orders...so would it be better to have the queries run as a cleanup routine after orders are created, then have the query populate a table for the budget form to draw from? It seems drawing directly from a table is MUCH faster than using the query series.

    I've made the queries as efficient as I can (in fact I went too far at one point and broke them) so I don't think there's any value there.
    Appreciate whatever advice you may have to offer.
    Thanks,
    Phil

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're saying you have a union query comprised of 7 other queries and you expect to add more, it strongly suggests that your tables design is all wrong. Usually, a bunch of union statements indicates this, as does slowness of queries with small data sets. Some queries will present records in a snap when hundreds of thousands, if not millions, of records are involved. There are a lot of factors that can enter into play with respect to query speed, but tables designed like spreadsheets is right up near the top (as multiple union statements suggests) along with complex and numerous calculated fields, lack of indexing, too many indexes, etc etc.

    You should probably post a pic of your table relationships if you have created them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Micron,
    Thanks for the reply. A little background about the database/design: I'm building an ITFM database to track budget, contracts/orders, invoices and cost allocation. I am using a single table (Orders) to track ALL the different types of purchases, both current and planned: one-time purchases, monthly services, and contracts over a fixed term. This only requires a couple of additional fields in the table since the basic data is the same across all the different types. This also allows me to use ONE form for data entry (with a couple of option groups) as opposed to having multiple forms to populate multiple tables. It works great from an order/invoice management perspective.

    There is one catch, however; when using that table to project budget, I have to tease them out based on the company's financial rules (which, admittedly, are a bit arcane). One-time purchases are charged in the month they are incurred (easy) as are monthly services. Invoices for which the purchase spans multiple months, however, are charged in equal increments over the number of months in the invoice if that increment is greater than $999.99. So an invoice for $6000 for an annual contract is charged in the month incurred, since $6000/12 months = $500/month (which is less than $999.99). An invoice for a $12000 contract, however, will be charged at the rate of $1000/per month because $12000/12 months = $1000/month (which is greater than $999.99). This makes a huge difference when making fiscal year calculations.

    So the series of queries pulls and totals, for the selected fiscal year, the one-time purchases and monthlies (although they must be different because purchases is a simple sum, while monthlies are multiplied times the number of months in the fiscal year for which the monthly applies). Another series of queries then extracts the multi-month contracts, calculates the monthly increment (invoice/# of months), then sums the ones below $999.99 in total while, for the ones greater, multiplying each times the number of months in the fiscal year for which the invoice applies and then summing that. Finally, the union query brings it all together so a final query can sum all the amounts in the union query and return them by department and subaccount.

    I have to do this for both current orders (because they represent money contractually obligated that overlaps into the fiscal year) as well as planned orders (because they represent orders I want to place). So all those queries have to happen twice.

    As I said in the original post I think my queries are as efficient as they can possibly be. Only the absolute required fields are queried, and every field for which there is a criteria is indexed. There are quite a few calculations inside the query unfortunately; they are unavoidable. Worse yet, these numbers must be displayed in a form (vice report) because I need them to understand the "floor" number for the budget; i.e., for the next year, you HAVE to budget to cover obligations, plus add some for discretionary or contingency budget subaccounts. I display them in a continuous form (see attached). The user filters by fiscal year, and the queries use that as a primary filter. The department is selected in a navigation dashboard, so the controls on the form are using DSUM to sum ALL obligations by Department and SubAccount (for each row in the form).

    Basically, I'm violating all the rules by using DSUM against multiple queries using many calculated fields run in a continuous form. If I change the entire database to fix THAT issue, I'm introducing (from my perspective) much more complication and lost time by having multiple tables, multiple data entry forms and more (although likely simpler) queries.

    The only thing I can think of to economize the query calculation time is to have it calculate it at a different time. The budget screenshot you see is in a tabbed control in a separate Budget Manager form. I have a separate Order Manager form for entering orders. One thought would be to have these queries execute and populate a temporary table when the Order Manager form is closed or loses focus. Then the OPEX Budget subform on the Budget Manager form can just draw from the table (or a single select query based on FY) to populate the controls, vice running all the queries when the form is loaded. Essentially, have the query calculations run when the underlying data is entered, vice when the summary data is needed.

    EDIT: Actually, to make that last idea even simpler...maybe I should just add some fields to the Order table and do the calculations every time an order (contract, purchase, etc.) is updated and populate the appropriate field. Then the budget manager calculated controls would just sum a single field vice running complex calculations. It violates the "never store what you can calculate" rule, but follows the "normalize til it hurts, denormalize til it works" rule.

    Open to any ideas you may have.
    Thanks,
    Phil

    P.S. Using test data right now, so the screenshots may look a little nonsensical...
    The queries support the Obligated Current and Planned columns. Designated means a planned, one-time purchase and Undesignated is basically contingency money (not designated for a specific planned purchase; discretionary).
    Click image for larger version. 

Name:	Relationships.JPG 
Views:	15 
Size:	33.6 KB 
ID:	43186Click image for larger version. 

Name:	BudgetManager.JPG 
Views:	15 
Size:	117.1 KB 
ID:	43187

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Today is our Thanksgiving and we're doing the turkey dinner thing so I may not be able to digest this as well (pun intended) until tomorrow. You might get some feedback from others in the meantime.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-09-2015, 12:06 PM
  2. Replies: 17
    Last Post: 08-09-2015, 09:45 AM
  3. Replies: 1
    Last Post: 05-20-2013, 01:45 PM
  4. Queries with multiple tables to create report
    By Solstice in forum Queries
    Replies: 1
    Last Post: 09-22-2011, 02:23 PM
  5. Using queries to create new table
    By mradel in forum Queries
    Replies: 1
    Last Post: 10-26-2010, 10:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums