Queries vs filters Results


On one form of my database, I use around 50 different queries. These handle searches displayed in about 8 different text boxes on the same form depending on my search options. I was told that queries were better to use than filters because users can turn filters off (http://www.access-programmers.co.uk/...threadid=60192). With no data in the database, it is around 8.58MB. When people put more data in, it seems to grow at an unusual rate. When there is around 50 entries, the database is around 25MB... Is this normal? Does it have anything to do with the fact that I am using queries? Does Microsoft Access copy (obviously not by reference!) the relevant parts of the main table into each query???

Could somebody let me know if this is normal and if there are any obvious ways I could decrease the size of my database please?




Users would like to do query based on dates. I have been using a filter by form, which works fine. However, when it comes to sorting based on dates, keying in parameters may get time-consuming as users would have to click on "selection" at the top and pick "between..." and specify dates there.

If there is a way to have a form where users can just simply put in date parameter so that they can see a list of facilities who got opened in the last 6 months. So far, filter by form is very limiting with this respect.

Is this possible at all?

what would the query look like for searching dates in the last 6 months?


I'm wondering if I've been going about this the wrong way...

So I'm working with 7 databases of parcel information (meaning...these are huge databases) to query up some general information. I thought this would be a relatively simple task, which, it has turned out not to be.

The information I'm trying to extract is # Owners (which means I need to do a "Group By" criteria of the "OWNER NAMES" to create a table which combines all the owners who have the same name hence getting # of owners)...like so...

...and # of Parcels (which means, since this is a parcel database, each record = 1 parcel....so basically this is a simple sorting task)

....and Sum of records (which means a "Group By" criteria query of the "OWNER CLASS" columns with a "Sum" of the "ACRES" column)...

Simple? Right?

Well, so the tables have come out like so:

See the problem?

There is no way on this PLANET, that in this particular table (Plumas) that the # of Parcels should MATCH the # of Owners (see Family =>10 acres row). Absolutely no way. I've run these queries six times and same answer (not human error).

I'm also getting totals in the # Parcels column that DO NOT MATCH how many records I have in the entire database (and I'm finding that I'm off from anywhere to 10 to 100 order's of magnitude).

It also gets weirder. The "FAMILY =>10" and the "FAMILY

So I'm maintaining a database that stores employee info. When an employee leaves there info no longer needs to show up in many of the forms/reports etc.

However some reports and such refer back to the past when that employee was still there (the Employee ID is still referenced)

Up till now they have been deleting old records and losing all that data, but now they want to move that data to a separate table to Archive it.

I have a VB script to do this, but I'm wondering would it be more efficient to make a Yes/No field for Active/Inactive and then use a simple filter for all my other objects rather than the union query I have now to join Current and Archived Records

I apologize for the caffeine not kicking in...

I have a form based on a query that filters a series of vacation records. There's a separate field that tells the database what year to charge the vacation to (just in case people use 2011 vacation hours in 2012 or vice versa). What I want to do is use a check box to let people filter only the records charged to the current year vs all records from past years. As you might suspect, it isn't working. The check box, when on, seems to work. But when it's unchecked, no records show at all. The criteria is...

	IIf([Forms]![frmEnterEmployeeVacationTime]![filteron]=True,DatePart("yyyy",Date()),([tblVACATION LOG].[Charged to What
Year]) Like "*")

Any suggestions? Thanks

Dear Assorted Access Gods:

I'm (maddeningly) slowly working on my first split FE/BE project. This will be a distributed .mde FE that hooks to an Access BE stored on a web server (via, I THINK, providing the FE with linked tables that point at the server--but I haven't gotten around to splitting the thing yet).

I know that I should base all forms & reports on queries, rather than the actual tables. No problem there. However, I have a lot of VBA code to generate all sorts of reports, export Word docs, send e-mails, etc. Most of these start from a selected record--a booking, in this case. Some use that selected record to look up various bits of info in related tables (venues, contacts, performance dates & times, etc.) to work their mojo.

Now I already know that I should avoid DLookup(), since the number of records and concurrent users will (someday) be moderately high--given Pat H.'s many firm opinions on the matter, I try to avoid DLookup anyway()! Likewise, I know Pat's penchant for stored QueryDef's. My own proclivity, however, has usually run towards building an SQL statement within VBA and using that as the condition on my .Open method. However, Pat's continuing argument that stored queries are more efficient because they do much of their work when compiled, rather than at call time, is persuasive.

So, here's my question (at last!) to my betters: What is the "best" method for retreiving one or a small set of records from one or more tables in a multi-user FE/BE setup? I want to factor in speed, efficiency, record-locking issues, and ease of coding (also, I try to avoid DAO whenever possible, on the grounds that ADO is "the future" - HA)

Some methods I'm using now:Saved queries where the conditions get all their values from (hidden or not) forms. Built-from-scratch SQL WHERE clauses applied to .Open method. A "temp table" - when the user selects record "X" for the above described ministrations, a VBA function uses multiple SQL WHERE'D recordsets to gather info from all the various tables, then writes it all as the single record in my temp table. The ministrations then work off that single record and delete it after. In the FE/BE environment, this temp table would be one of only two "local" tables not stored on the server. Some methods I'm investigating:Saved Paramter queries where values are supplied from opening call. Hidden form based on a long chain of saved queries to get the individual pieces of info in one place (then VBA ministrations pull their data from there). QBF techniques. Saved queries which then have filters applied (example: save query returns all the venues, filter then narrows it to venue related to selected booking).
Any comments, ideas, examples, what have you, VERY welcome!

--Curious Mac

I have created a make table via a parameter query. The parameters are defined correctly and I am prompted each time to enter them. The [DeptNo] does work as my query is filtered based on a value entered, but when I enter the tablename parameter, the make table query always saves the table as @tablename, and not any text i enter for the @tablename like "mynewtable" or something. Here is the SQL code.
Any ideas? Ignore the complexity of the query, my main concern is being able to loop through some VBA code and continually change the tablename each time (along with the DeptNo. I think this will save me time vs creating a new sql statement each time and doing a docmd.runsql ...

PARAMETERS DeptNo Text ( 255 ), [@TableName] Text ( 255 );
SELECT AUMaster.Lawson, ChgMstr.DEPT, ChgMstr.[CHG CD], ChgMstr.[REVENUE DESCRIPTION], [Prior and Curr Year Revenue - Qty].Price, ChargeCodeProfile.[Count per Charge] AS Weight, [Prior and Curr Year Revenue - Qty].[2006 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2006 Projected Revenue], [Prior and Curr Year Revenue - Qty].[2007 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2007 Projected Revenue] INTO [@TableName]
FROM ((ChgMstr LEFT JOIN [Prior and Curr Year Revenue - Qty] ON ChgMstr.[CHG CD] = [Prior and Curr Year Revenue - Qty].ChargeCode) LEFT JOIN ChargeCodeProfile ON ChgMstr.[CHG CD] = ChargeCodeProfile.[CHG CD]) LEFT JOIN AUMaster ON ChgMstr.DEPT = AUMaster.Affinity
WHERE (((ChgMstr.DEPT)=[DeptNo]));

I have a database of invoices from the past two years. Each record contains: invoice#, customer name, sales date, sales amount($). I want to generate a report that compares each Customer's total sales this year vs. last year. I would like each line to display: Customer Name, Total 2002 Sales, Total 2001 Sales. (I would actually like the report to be able to compare any two time periods).
I have tried building two queries, each filtering the date for a specific year, and displaying the summary break for each Customer with the total sales fields from each respective query, but that doesn't seem to work. Can someone please show me the correct technique?

We use MS Access 2010 with Linked SQL Server 2008 tables. I have few reports working 99% of what Management wants/needs. I am currently testing to get reports to run if user(s) has no data.

When I declare function arguments as Strings, the MLM returns #Error for Medians and the Level2 returns a "blank" value (not sure if empty string or Null) for Medians. I thought wrapping Nz() around median calls would help but I get same results.

When I declare function arguments as Variants, I get a reversed results. The MLM returns a "blank" value whereas the Level2 returns #Error. Again, wrapping Nz() function around both median calls gives same result.

Why? What am I missing. How can I fix this behavior so both calls to the Median function return the "blank" value? I'm assuming I need to include some kind of check within the VBA function code, but I don't know VBA good enough to figure it out. Can someone help me out please?

Only thing different between the two function call querries is that the MLM uses additonal NUM_LINES optional argument whereas the Level2 query call does not. They both use optional PERFORMER argument.

Here is SQL for MLM call:

	SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES,
AS UserLineMedian, Sum(qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES) AS TotalUserLines
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES;

Here is SQL for Level2 call:

	SELECT qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER,
Nz(DMedian("qry_Level2StatsInvoiceTimeDurationDetail","SECONDS",[qry_Level2StatsInvoiceTimeDurationDetail].[PERFORMER])) AS
UserInvoiceMedian, Count(qry_Level2StatsInvoiceTimeDurationDetail.DOC_ID) AS TotalUserInvoices
FROM qry_Level2StatsInvoiceTimeDurationDetail
GROUP BY qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER;

Here is the VBA DMedian function code:

	'changed String arguments to Variant to test #Error if user(s) have no data
Public Function DMedian(strDomain As Variant, strField As Variant, Optional strGroup1 As Variant, Optional strGroup2 As
Variant) As Variant
'Public Function DMedian(strDomain As String, strField As String, Optional strGroup1 As String, Optional strGroup2 As String)
As Variant
    'Purpose:   Return median value for a field in a table or query recordset
    'Inputs:    strField: the field
    '           strDomain: the table or query
    '           strCriteria: an optional WHERE clause to apply to the table or query
    '           strGroup1: an optional GROUP BY clause to apply to the table or query
    '           strGroup2: an optional GROUP BY clause to apply to the table or query
    'Calling:   DMedian("numericfieldnametogetmedian", "tableorqueryname", "stringforwhereclause") 
    'Output:    Returns median, if successful; Otherwise, an Error value
    Dim Db As DAO.Database
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Variant
    Dim intFieldType As Integer
    Dim intRecords As Integer
    Const errAppTypeError = 3169
    On Error GoTo HandleErr
    Set Db = CurrentDb()
    ' Initialize return value
    varMedian = Null
    ' Build SQL string for recordset
    strSQL = "SELECT " & strField & " FROM " & strDomain
    ' Only include group1 filter in WHERE clause if one is passed in
    If Len(strGroup1) > 0 Then
        strSQL = strSQL & " WHERE PERFORMER = '" & strGroup1 & "'"
        'use group2 as another filter in WHERE clause if one is passed in
        If Len(strGroup2) > 0 Then
            strSQL = strSQL & " AND NUM_LINES = " & strGroup2 & ""
        End If
    End If
    'added to see if it would help with slowness before deciding to use a MakeTable
    'strSQL = strSQL & " AND APS_DATE Between GetStartDate() And GetEndDate()"
    strSQL = strSQL & " ORDER BY " & strField
    'for displaying strSQL in Immediate Window for troubleshooting
    'Debug.Print strSQL
    Set rstDomain = Db.OpenRecordset(strSQL, dbOpenSnapshot)
    ' Check the data type of the median field
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field
        If Not rstDomain.EOF Then
            intRecords = rstDomain.RecordCount
            ' Start from the first record
            If (intRecords Mod 2) = 0 Then
                ' Even number of records
                ' No middle record, so move to the
                ' record right before the middle
                rstDomain.Move ((intRecords  2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle
                ' And average the two values
                varMedian = (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
                ' Odd number or records
                ' Move to the middle record and return its value
                rstDomain.Move ((intRecords  2))
                varMedian = rstDomain.Fields(strField)
            End If
            ' No records; return Null
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error
        Err.Raise errAppTypeError
    End Select
    DMedian = varMedian
    On Error Resume Next
    Set rstDomain = Nothing
    Exit Function
    ' Return an error value
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function

I have a number of Excel worksheets to keep some basic data in, mainly because it's just easier and nicer to maintain data in Excel rather than going into Access tables, I use the filter/sort/formulas of Excel to maintain it, and also because there are people who will have to edit it that aren't familiar with Access.

At the moment I've been linking to these worksheets as that seems to be the simplest way, and any changes made in Excel are automatically reflected in the queries Access is running. But some of my queries are very slow, which I'm thinking is down to the linking, as I don't think it's possible to index linked tables and Access is constantly having to go out and fetch the data.

When generally is it best to import an Excel sheet, rather than linking to it? And if I did that, how would I make sure I always had the latest version available to Access?


I wanted to take an informal poll about what others thought and implemented when it comes to drop-down boxes. Although (one of) the main purpose for the drop-down box is for data integrity, there is never much dialogue about the maintenance of drop-down boxes.

An example would be a drop-down boxes for categories, types, or other such information we might use to pigeon-hole data for segregating and counting purposes. Although the quantity of list might hardly ever change sometimes higher-ups want to add another to the list.

I personally use a tblValueList that holds any sort of data for my drop down boxes. It has 3 Fields (ID, ListType, ListDescription). For the drop down box, let's say it's a Category, I use "Category" in the ListType to filter the query and ListDescription is stored into that data field.

Hence, when I update, I just add the appropriate ListType and the new item in ListDescription. This way I can 'outsource' maintenance by allowing a priviledge user access to make these updates through a form.

Is my method normal? or do most use the static list in the properties of the combo box?


I am creating an employee database and I am having trouble filtering employees by department number. I have 11 different groups that utilize over 270 different department numbers and I want to filter these departments so that only the Electrical group shows up in a form. I also want to use one form for this instead of a form for each of the 11 groups.
Currently I am using separate queries for each group and this works but then I'd have to make a from for every query which I'd rather not do.

Is there any way around creating a form for each group based off queries?


I read that you can improve performance by using a saved Query instead of a sql Query.

Rookie Question...how do I make a Query I created using the Wizard a Saved Query?

My problem....
When my table has 7000 and I use a Query to bring it in the form, the speed of going to the next record is less than a second.

I added 80,000 records and it now takes about 4 seconds to go to each record.

Even when I filter the records to less than 2000 records, it responds like it's searching through 87,000 records.

If "saved query" is the right term. How do I make my query a "saved query"?


I'm very new to access 2003 and learning about it slowly.

I've got a few tables set up one with an entire list of staff, and two other tables for two training courses that we run listing names of delegates and dates attended. I want access to produce an entire staff list and filter in training each member of staff has completed even if they haven''t completed any.

Am I asking too much?

(I have searched this forum and found other answers to the error message i'm getting, but none of the other users' issues seem to be similar to mine)

I retrieve data from a text file that has date/time information in text form.

I use a query to grab the revelant data from the text field (right, left, mid, etc.) and then use FinalDate=DateSerial(yearcolumn, monthcolumn, datecolumn) and FinalTime=TimeSerial(hourcolumn, minutecolumn, secondcolumn) to get the Time.

Then I add the two together to get my FinalDateTime value in a proper "time" formatted column.

Now I need to select dates from this field that fall between a certain start date and certain end date. These values are stored in TempVars.

On either the FinalDate or FinalDateTime columns, i have tried Criteria = Between [TempVars]![StartDate] and [tempVars]![EndDate], but get that old nasty error "expression typed incorrectly etc."

I have added a comparison column and say "CheckMe: IIF ([FinalDate] > [tempVars]![StartDate] and [FinalDate] < [TempVars]![EndDate], "Yes", "No")
Then i try to filter THAT column on "Yes" to show me only those that fall in the range.

Without anything in the Critieria field, the data populates perfectly. All of the entries that should fall between the dates show Yes and the ones that do not, show No.

But the query WILL NOT accept anything in the Criteria column. In the expression, I have tried IIF (x, 0, 1) or IIF (x, 1, 0), i have tried IIF (x, true, false), even IIF (x, "Yes", ""). All of the equations work perfectly but the daggum query still will not filter.

I removed anything from the Critieria row but left the expression. Then i created a new query based on this one, trying to select only where CheckMe = "Yes." No luck. same message.

I gave up on the TempVars and tried putting the dates into a table, to be absolutely positive i was comparing date vs date. Even if I used [datetable]![StartDateTable] instead of [tempVars]![StartDate], same doggone thing. results are perfect, cannot filter on it.

"The expression is typed incorrectly, or it is too complex to be evaluated." etc.

How can Access put a result in a field perfectly, but not let me sort on that same field??

I'm going nuts!

Could anyone tell me if there's a hardcoded maximum number of fields/columns in a query?

I ask, because through some experimentation and the urging of others on the boards, I've finally taken a look at the SQL view of a database and found it rather easy to understand. There, I found I had more flexibility filtering a Query via SQL in comparison to the query design window. (unlimited "Or" vs. 8).

So, even though there's a definite number of columns in the Query Design window, can I ignore than and just enter what I need in the SQL view?

Hello Guys,

I'm fairly "new" to access, however, developing and learning as i go... I have a table which returns the following fieldS:

> Company
> Product
> ID
> Production Date.

I want to create a query which would output hte results as follows. Basically, it compares "today's date" (current datE) to the production date, and then groups the results as below:

Name | Product | 1-5 Days | 6-10 Days | > 11 Days |

I created the query which "groups" the above according to Company / Product, and then provides a count ignoring the dates - but now i need it to further filter according to the dates above?

Any advice?

I have 2 tables. OrderInfo and Inventory. I created a one to many relationship with Order ID being the linking field. In OrderInfo, I can have up to 6 distinct items being ordered but some fields will always remain the same. Example:

20 | Cotton | White (will be the same) and then T-shits | Camisoles | Tank Tops will be sample order details.

In the inventory table I have several listings of each T-shirts, Camisoles and Tank tops with their own Item#.

I am attempting to see when I get an order, what I have on hand and what I need to make. When I try to create my query, I must be doing something wrong with the join or something else because I get too many results. I know I only have 19 tank tops on hand (I can filter them from the Inventory table) but the query returns 589.

I do not hav sample of the code because I got so frustrated that I scrapped it (I have been staring at this for a few days now). Could someone please offer a link to a query tutorial r any advice here pleasse. I am not sure where to even go from here anymore.

So I currently have a simple form, in which you select a persons name from a combobox, click OK, and it brings up all their records with a query. Shows up in datasheet view and it works just fine. However, it would be much more user friendly/easy to see if I could have it present that information in form view (the form is called frmFindFeedback). I tried the OpenForm macro with the query as a filter, but then you have to type in the parameters, rather than selecting it from a combobox.
I'm pretty sure this can be accomplished with VBA. here's the code that I have on my name selection form:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmSearchforfeedback"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qryfeedback", acViewNormal, acReadOnly
DoCmd.Close acForm, "frmSearchforfeedback"
End Sub

Pretty simple, and I just can't quite make the connection for how to get it to show it in a form.

Thanks for all your help!

I have a form that has a requery button and a separate form filter button. The form has two unbound fields that are referenced in the forms Query criteria. When I first enter the form without applying filters, the requery button accurately refreshes the form based on the values entered into the unbound fields.

The code behind the Requery button is:

Private Sub Refresh_Click()

If Me.FilterOn = True Then
Me.FilterOn = False
DoCmd.RunCommand acCmdRemoveFilterSort
End If

DoCmd.GoToRecord acDataForm, "TRIPHeader", acLast

End Sub

After applying the filter button (which executes some specific filtering)

Private Sub Qlikview_Filter_Click()

Me.Filter = "TRIP_ID in (SELECT TRIP_ID FROM QlikviewList)"
DoCmd.RunCommand acCmdApplyFilterSort

End Sub

I then want to go back to the Requery button the Query criteria (stored in the Query itself) does not take effective. All possible rows from the form query are returned even though my unbound fields in the form are referenced in the query criteria. I have tried all sorts of combinations of removing the filter but I still seems that any time a filter is applied and then after it is removed the Forms Query Criteria is no longer functioning properly. Any help would be creatly appreciated on this.

Additionally I have also found that if i click on the Remove Filter icon on the Filter/Sort toolbar the above Requery Button works fine so it appears that there is something different happening when I use the toolbar Remove Filter vs when i use the DoCmd.RunCommand acCmdRemoveFilterSort orMe.FilterOn = False commands

Not finding an answer? Try a Google search.