Re: Filtering Queries
- From: Jamie Collins <jamiecollins@xxxxxxxxxx>
- Date: Tue, 02 Oct 2007 03:51:25 -0700
On Oct 1, 6:16 pm, PatK <P...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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?
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.
--
.
- Prev by Date: Re: Not enough space on temporary disk
- Next by Date: Re: countif Access 2000 - counting the number of values above 0
- Previous by thread: Re: Filtering Queries
- Next by thread: Re: What is Subscript out of range in MSACCESS?
- Index(es):
Relevant Pages
|
