Re: Filtering Queries

Thanks, Marshall and Jamie. Turns out you were right. The lookup field,
which I had set up to return a "Long Name" (ie, Market Products and Services)
for the user to choose, but bind a second field "MPS" to the content was not
working correctly. The ticket detail file had MPS, but the session tbl had
the "long name". Thus, no records found.

During the process, just mucking around with what I have seen out here, I
cobbled together a function, like below, to insert as "criteria" in the
design query. I am using this instead of the "linked table" approach I
started out with:

Function SessionFilterTower()
Dim rs As ADODB.Recordset
Dim sTower As String
Set rs = New ADODB.Recordset
rs.Open "tblSessionFilter", CurrentProject.Connection
rs.Filter = "Filter = 'Filter'"
rs.MoveFirst
sTower = rs(2)

If sTower = "All" Then
SessionFilterTower = "*"
Else
SessionFilterTower = rs(2)
End If
rs.Close
Set rs = Nothing
End Function

This gave me the capability of selecting an "all towers" (towers = support
team) as well, which I could not easily do by linking to just one value. I
have a similar function for the "month" to analyze, as well.

I guess my question is, is this: By inserting this in the "criteria" field,
vs a link to the tblSessionFilter, is my query going to be
slower/faster/same? It may be elementary, due to my desire to have an all or
"*" selection field, but am always looking for an easier, more efficient way.

Anyway, I am working at the moment, and I do thank you folks for getting me
steered in the right direction.

PatK



"Marshall Barton" wrote:

PatK wrote:

I have a database of support tickets, that contain a couple of fields, Group
and YYYY-MM. I have created a table called tblSession, which I use a form to
update this "single row" table, to basically try to create a Session Filter
for all subsequent queries.

The two fields on the tblSession update form are "Support Team" and "Month".
Support refers to the group that provide the support (I have 5 groups).
Month refers to the month the support ticket came in, in YYYY-MM format. I
am able to get this single row table updated properly. After an update, it
might look like this:

Filter (PK) Team Month
"Filter" (contant) MPS 2007-09

Filter contstant is used only by the update form, to force it to always
update just the first record. MPS is one of my support teams.

In my "Support Ticket" data table (about 100K rows), I have both the Support
Team and Month in each detail row.

What I thought I could do was create a query that joins to the Month field,
and have that query return only rows where the Month field in the session
filter table and the detail ticket data, are equal. Unfortunately, it
returns nothing (although I can see/edit the relationship just fine in Design
view).

I am wondering what bonehead thing I am doing. I can see ticket data rows
with the right month...and I can see ticket data rows with the right team. I
have tried making a query to simply link to ONE of the fields, first, but
even that first query returns nothing.

Any ideas (if this makes any sense) where I may have gone wrong? Is there
an easier way to do this (allow a user to set a filter, then apply that
filter (unbeknownst to them) in the background, to subsequent queries I
create for them?


You are going about it in a perfectly logical way.

I suspect that the linking fields are not really the same
values. Remember that a formatted value is not displaying
the entire value in the field. This is especially true for
date fields set using the Now() function. Then there's the
diabolical Lookup field where the value is nothing like what
you see.

Dig into the dields in both tables very deeply to verify
that the linking fields are what you think they are.

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Filtering Queries
    ... to basically try to create a Session Filter ... Support refers to the group that provide the support. ... What I thought I could do was create a query that joins to the Month field, ... filter table and the detail ticket data, ...
    (microsoft.public.access.queries)
  • Re: Filtering Queries
    ... to basically try to create a Session Filter ... Support refers to the group that provide the support. ... What I thought I could do was create a query that joins to the Month field, ... filter table and the detail ticket data, ...
    (microsoft.public.access.queries)
  • Re: Finally which ORM tool?
    ... the session' method. ... they use the same mechanism as Linq to Sql does: ... Also, if you pass a variable to the query, the value the variable ... q is affected if I change foo AFTER this query and BEFORE execution. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... and have the query point to the combo box to get its value. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)
Loading