Filtering Queries

[Guest]

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?

Thanks for any ideas!

PatK

 

[Marshall Barton]

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.

 

[Jamie Collins]

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?

Data types? Perhaps something like this:

month_start_date = #2007-09-01 00:00:00#
month_end_date = #2007-09-30 23:59:59#

SELECT *
FROM SupportTickets AS S1
INNER JOIN tblSession AS T1
ON S1.Team = T1.Team
AND (S1.ticket_in_date BETWEEN T1.month_start_date AND
T1.month_end_date)
AND T1.Filter = 'Filter';

Jamie.

--

 

[Guest]

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]

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.

I strongly recommend not using a function if at all
possible. I suggest that you take a shot at using the kind
of query join that Jamie suggested.
Besides, I don't do ADO ;-)