Answer: Excel Export, Query Defs, SQL IN criteria
in [Help]
|
Prev: fDialog - Opens at a User defined Folder
Next: Running different queries contained in a single query object from a form
From: Douglas J. Steele on 30 Jun 2010 15:07 While you say "one field tables", how many rows are they typically going to have in them? Whether you delete the table and recreate it, or delete the data from the table and append to it makes no difference from the point of view of bloating. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) <christianlott1(a)yahoo.com> wrote in message news:bf0e3e59-8f31-4e6f-bd91-f65d5d95d471(a)5g2000yqz.googlegroups.com... On Jun 30, 1:28 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Any time you delete rows from tables and repopulate them, you're going to > get bloat. That's because Access doesn't actually return the space that > was > used unless you do a Compact and Repair of the database. > > How much bloat you'll get depends on how large the tables are and how > frequently you perform the deletions and repopulations. Then maybe I should delete and create the tables each time I open the form? They are simple one field tables. Will this bloat as well? Thanks.
From: christianlott1 on 30 Jun 2010 15:21 On Jun 30, 2:07 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > While you say "one field tables", how many rows are they typically going to > have in them? 5 or 6 more or less. County names or just 1-16 (numbers) for regions. The tblExcelExport will have about 25 columns and four or five hundred rows at a time. Maybe there's someway I can compact and repair when I close the form? > Whether you delete the table and recreate it, or delete the data from the > table and append to it makes no difference from the point of view of > bloating. Ah. OK. Thanks. I originally wanted to filter with qdf.parameters but it gave errors I think because the [parameter] was wrapped with an In() clause. Either way I'd still have tblExcelExport to deal with.. Thanks.
From: Douglas J. Steele on 30 Jun 2010 15:36 Why not just use a temporary database? It's far easier than trying to kludge together some way to force a compact. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) <christianlott1(a)yahoo.com> wrote in message news:7f39d402-7023-4e4b-8570-ce786d85e2ca(a)w12g2000yqj.googlegroups.com... On Jun 30, 2:07 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > While you say "one field tables", how many rows are they typically going > to > have in them? 5 or 6 more or less. County names or just 1-16 (numbers) for regions. The tblExcelExport will have about 25 columns and four or five hundred rows at a time. Maybe there's someway I can compact and repair when I close the form? > Whether you delete the table and recreate it, or delete the data from the > table and append to it makes no difference from the point of view of > bloating. Ah. OK. Thanks. I originally wanted to filter with qdf.parameters but it gave errors I think because the [parameter] was wrapped with an In() clause. Either way I'd still have tblExcelExport to deal with.. Thanks.
From: Bob Barrows on 30 Jun 2010 16:03 christianlott1(a)yahoo.com wrote: > On Jun 30, 2:07 pm, "Douglas J. Steele" > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: >> While you say "one field tables", how many rows are they typically > I originally wanted to filter with qdf.parameters but it gave errors I > think because the [parameter] was wrapped with an In() clause. > While I'm of the opinion that from a performance standpoint, you've already chosen the best method, there are other ways to parameterize the IN clause, which I will list at the end of this. Here is a way to avoid creating and deleting tables: Given that there are likely to be fixed sets of counties and regions from which the user can select, I would suggest creating tables for each entity, populating them with the entire lists of possible counties and regions, and adding a [Selected] column to each. Then bind continuous subforms to each table (instead of using listboxes), and allow the user to make the selections that get saved in the tables. In the export query, filter on the [Selected] records. At the end, run update queries to clear the selections (and/or clear the selections at the beginning of the process as well). Now that I think of it, counties and regions could be related couldn't they? Perhaps a cascading selection scheme is called for: i.e., the user selects a region and is presented with the list of counties within that region from which to select. Does your database design support this? Here is a compilation of posts about how to deal with this issue: There are two solutions for this problem listed in the following KB article (Q210530 - ACC2000: How to Create a Parameter In() Statement), http://support.microsoft.com/kb/210530/ The first solution uses Instr() to test the field values against the list in the parameter. The second involves dynamically creating a SQL statement in code. Thanks to Paul Overway, here is a third solution, using the Eval function: WHERE (((Eval([Table]![Field] & " In(" & [Forms]![Formname]![textboxname] & ")"))=True)) or, using a prompted parameter: WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True)) Thanks to Jeffrey A. Williams, here's a 4th solution: If you don't mind adding a table to your database, and you're comfortable dealing with possible multi-user issues, this will perform better than either of the solutions that involve running a function (Instr or Eval) on every row of your table: Create a new table with two fields: tblCriteria: Criteria text Selected boolean (yes/no) Populate the table with your values and select a couple of items. Now you can use this table in your query as such: Select * from table1 inner join tblcriteria on table1.[your criteria field] = tblcriteria.criteria where tblcriteria.selected = true You can easily setup a form (or subform) that is bound to tblCriteria and allow the users the ability of selecting which values they want. Thanks to Michel Walsh, here's yet another way: SELECT Table3.ConName, Table3.State, Table3.Zip FROM Table3 WHERE "," &[list] & "," LIKE "*," & [ConName] & ",*" with [param] some string like: '1,4,5,7' note that there is no space after the comas. It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE '*,45,*' returns false. If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*' returns true. So, you have, in effect, an IN( ) where the list is a parameter. -- HTH, Bob Barrows
From: christianlott1 on 30 Jun 2010 17:45
On Jun 30, 3:03 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote: > adding a [Selected] column to each. Then bind continuous > subforms to each table (instead of using listboxes), and allow the user > to make the selections that get saved in the tables. In the export > query, filter on the [Selected] records. At the end, run update queries > to clear the selections (and/or clear the selections at the beginning of > the process as well). Yes. This is a better solution. Thanks :) > Now that I think of it, counties and regions could be related couldn't > they? Perhaps a cascading selection scheme is called for: i.e., the user > selects a region and is presented with the list of counties within that > region from which to select. Does your database design support this? Many counties belong to each region. They want to be able to select a few regions and a few counties outside those regions. My original design was either county or region, not both. The new design I just finished can do both but also uses a union query. I can redesign to not use those two jtRegion/jtCounty tables but the table that loads the most rows is the tblExcelExport. Too bad DoCmd.TransferSpreadsheet won't accept a query as a source instead of needing a table :( Thanks for the ideas Bob. |