SQL Query

I'm using the following Query to gather data in a database I'm working
with. The Query works fine and gives most of the data I need. What I
now need to do is have the Query display data from tblProblemTypes
that is not in a given query.

For example. There are several problem types predesignated in
tblProblemTypes. Not every Problem type has been selected in a given
date range, thus, some problem types are omitted from the Query. I
was thinking I could use some sort of join query, but I've not been
able to get the results I'm looking for.

Below is the Query I'm using:

SELECT tblProblemTypes.ProblemTypes, tblProblemSubType.ProblemSubType,
Count(tblProblemSubType.ProblemSubType) AS CountOfProblemSubType1

FROM (tblCases INNER JOIN tblProblemTypes ON tblCases.ProblemType =
tblProblemTypes.ProblemTypesJapanese) INNER JOIN tblProblemSubType ON
(tblProblemTypes.ProblemTypeId = tblProblemSubType.ProblemTypeID) AND
(tblCases.ProblemSubType = tblProblemSubType.ProblemSubTypeJapanese)

WHERE (((tblCases.DateCreated) Between [Enter a Starting Date to
Search (mm/dd/yyyy)] And [Enter an Ending Date to Search
(mm/dd/yyyy)]))

GROUP BY tblProblemTypes.ProblemTypes,
tblProblemSubType.ProblemSubType;

re: SQL Query

I would create a new query that will perform an outer join between the
query you list below and tblProblemTypes. I couldn't see a convenient
way to weave in the outer join into the WHERE-limited query you have. I
think it would be best to save this one, then make another query like

SELECT tblProblemTypes.ProblemTypes FROM tblProblemTypes LEFT OUTER JOIN
SavedQuery ON tblProblemTypes.ProblemTypes = SavedQuery.ProblemTypes
WHERE IsNull(SavedQuery.ProblemTypes)

Good luck,
Pavel

fred wrote:[color=blue]
>
> I'm using the following Query to gather data in a database I'm working
> with. The Query works fine and gives most of the data I need. What I
> now need to do is have the Query display data from tblProblemTypes
> that is not in a given query.
>
> For example. There are several problem types predesignated in
> tblProblemTypes. Not every Problem type has been selected in a given
> date range, thus, some problem types are omitted from the Query. I
> was thinking I could use some sort of join query, but I've not been
> able to get the results I'm looking for.
>
> Below is the Query I'm using:
>
> SELECT tblProblemTypes.ProblemTypes, tblProblemSubType.ProblemSubType,
> Count(tblProblemSubType.ProblemSubType) AS CountOfProblemSubType1
>
> FROM (tblCases INNER JOIN tblProblemTypes ON tblCases.ProblemType =
> tblProblemTypes.ProblemTypesJapanese) INNER JOIN tblProblemSubType ON
> (tblProblemTypes.ProblemTypeId = tblProblemSubType.ProblemTypeID) AND
> (tblCases.ProblemSubType = tblProblemSubType.ProblemSubTypeJapanese)
>
> WHERE (((tblCases.DateCreated) Between [Enter a Starting Date to
> Search (mm/dd/yyyy)] And [Enter an Ending Date to Search
> (mm/dd/yyyy)]))
>
> GROUP BY tblProblemTypes.ProblemTypes,
> tblProblemSubType.ProblemSubType;[/color]

re: SQL Query

"fred" <fred@besttechsolution.com> wrote in message
news:cc913dbb.0405091450.1a4c3929@posting.google.c om...[color=blue]
> I'm using the following Query to gather data in a database I'm working
> with. The Query works fine and gives most of the data I need. What I
> now need to do is have the Query display data from tblProblemTypes
> that is not in a given query.
>
> For example. There are several problem types predesignated in
> tblProblemTypes. Not every Problem type has been selected in a given
> date range, thus, some problem types are omitted from the Query. I
> was thinking I could use some sort of join query, but I've not been
> able to get the results I'm looking for.
>[/color]

without knowing anything about your table structures it's not possible to
answer the question. Post some information about the tables, (columns, keys,
datatypes) with some sample data and the output you need and someone will be
able to write the query for you.