Connecting Tech Pros Worldwide Help | Site Map

SQL Query

  #1  
Old November 12th, 2005, 11:39 PM
fred
Guest
 
Posts: n/a
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;
  #2  
Old November 12th, 2005, 11:40 PM
Pavel Romashkin
Guest
 
Posts: n/a

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]
  #3  
Old November 12th, 2005, 11:40 PM
John Winterbottom
Guest
 
Posts: n/a

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.


Closed Thread


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query help AdrianG answers 5 April 4th, 2007 11:05 PM
SQL Query to VBA Syntax p answers 8 December 5th, 2006 01:25 AM
SQL Query Help schoultzy answers 2 May 19th, 2006 10:45 PM
Simple CrystalReport Chart using SQL Query? Don Wash answers 2 November 18th, 2005 03:24 PM
Oracle SQL query by date vnl answers 7 July 19th, 2005 11:09 PM