sub category nulls in a report?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
 
SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


KARL DEWEY said:
Post your query.

Joshua K Briley said:
I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
 
You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


Joshua K Briley said:
SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


KARL DEWEY said:
Post your query.

Joshua K Briley said:
I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


KARL DEWEY said:
You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


Joshua K Briley said:
SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


KARL DEWEY said:
Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


Joshua K Briley said:
I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


KARL DEWEY said:
You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


Joshua K Briley said:
SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


KARL DEWEY said:
Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


Joshua K Briley said:
I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


KARL DEWEY said:
You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

Joshua K Briley said:
Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


KARL DEWEY said:
Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


Joshua K Briley said:
I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
from the original post:
"The query supporting the report is based on a date range in which the test
was conducted."

From the Query:
....AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
....And ([queryfromtesttable].[Collected Date])<=[Ending Date])


--
Somecallmejosh


KARL DEWEY said:
You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

Joshua K Briley said:
Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


KARL DEWEY said:
Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


:

I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Did you try to enter a test record that you know will match the criteria?

Joshua K Briley said:
from the original post:
"The query supporting the report is based on a date range in which the test
was conducted."

From the Query:
...AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
...And ([queryfromtesttable].[Collected Date])<=[Ending Date])


--
Somecallmejosh


KARL DEWEY said:
You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

Joshua K Briley said:
Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


:

Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


:

I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Yes, but the locations get duplicated in the report. The test criteria is a
sub category of the locations. One location may have multiple test results.

I've created a make table query, as to eliminate the duplicates, and then
used the results of that query in a sub query, but it still doesn't work.
I've nested queries in many different fashions, hoping to stumble upon a
solution... still to no avail.

Still scratching my head on this one....
--
Somecallmejosh


KARL DEWEY said:
Did you try to enter a test record that you know will match the criteria?

Joshua K Briley said:
from the original post:
"The query supporting the report is based on a date range in which the test
was conducted."

From the Query:
...AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
...And ([queryfromtesttable].[Collected Date])<=[Ending Date])


--
Somecallmejosh


KARL DEWEY said:
You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

:

Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


:

Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


:

I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
You can change the report object property to hide duplicates.

Joshua K Briley said:
Yes, but the locations get duplicated in the report. The test criteria is a
sub category of the locations. One location may have multiple test results.

I've created a make table query, as to eliminate the duplicates, and then
used the results of that query in a sub query, but it still doesn't work.
I've nested queries in many different fashions, hoping to stumble upon a
solution... still to no avail.

Still scratching my head on this one....
--
Somecallmejosh


KARL DEWEY said:
Did you try to enter a test record that you know will match the criteria?

Joshua K Briley said:
from the original post:
"The query supporting the report is based on a date range in which the test
was conducted."

From the Query:
...AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
...And ([queryfromtesttable].[Collected Date])<=[Ending Date])


--
Somecallmejosh


:

You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

:

Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


:

Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


:

I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I'm not sure that we're singing the same tune here. I appreciate
your efforts.
--
Somecallmejosh


KARL DEWEY said:
You can change the report object property to hide duplicates.

Joshua K Briley said:
Yes, but the locations get duplicated in the report. The test criteria is a
sub category of the locations. One location may have multiple test results.

I've created a make table query, as to eliminate the duplicates, and then
used the results of that query in a sub query, but it still doesn't work.
I've nested queries in many different fashions, hoping to stumble upon a
solution... still to no avail.

Still scratching my head on this one....
--
Somecallmejosh


KARL DEWEY said:
Did you try to enter a test record that you know will match the criteria?

:

from the original post:
"The query supporting the report is based on a date range in which the test
was conducted."

From the Query:
...AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
...And ([queryfromtesttable].[Collected Date])<=[Ending Date])


--
Somecallmejosh


:

You never mention a problem with dates.

Build a record that you know will fit all the criteria and run.

:

Karl,

Thanks for the response. Because the criteria is based on a date range, the
locations still aren't pulling into the report. Is it possible that the
queries could be nested to support the "pulling in" of locations that don't
have test restults based on the date range? If so, do you have any
recommendations on how this can be accomplished? Thanks in advance.
--
Somecallmejosh


:

Ok, this allows your nulls to come through. If there is a criteria for a
field, even when using Like [Xxxx] & "*" and the field is null you must add
Or Is Null.

It also has your field labels pretty.

SELECT queryfromtesttable.ClientName AS [Client Name],
queryfromtesttable.CollectedDate AS [Collected Date],
queryfromtesttable.location, queryfromtesttable.sample_number AS [Sample
Number], queryfromtesttable.TestType AS [Test Type],
queryfromtesttable.KitReturned AS [Kit Returned],
queryfromtesttable.DateofTest AS [Date of Test],
queryfromtesttable.testresult AS [Test Result],
queryfromtesttable.Averagetestresult AS [Average test result],
queryfromtesttable.sample_Tempertature AS [Sample Tempertature]
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*" Or
(queryfromtesttable.ClientName) Is Null) AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*" Or
(queryfromtesttable.TestType) Is Null));


:

I've changed the names of some of the fields so they would make sense to the
reader. The spelling of the field names can be overlooked. Can this be
rewritten as to show the location, even though there are no corresponding
test results? Or is that something that needs to be handled in the report
itself?
--
Somecallmejosh


:

You had typos. Field names must match exactly. One missing opening [.

SELECT queryfromtesttable.ClientName, queryfromtesttable.CollectedDate,
queryfromtesttable.location, queryfromtesttable.sample_number,
queryfromtesttable.TestType, queryfromtesttable.KitReturned,
queryfromtesttable.DateofTest, queryfromtesttable.testresult,
queryfromtesttable.Averagetestresult, queryfromtesttable.sample_Tempertature
FROM queryfromtesttable
WHERE (((queryfromtesttable.ClientName) Like [Enter client name] & "*") AND
((queryfromtesttable.CollectedDate)>=[Beginning Date] And
(queryfromtesttable.CollectedDate)<=[Ending Date]) AND
((queryfromtesttable.TestType) Like [Test type sought] & "*"));


:

SELECT [Client Name],
[location],
[Collected Date],
[sample_number],
[Test Type],
[Kit Returned],
[Date of Test],
[testresult],
[Averagetestresult],
[sample_Tempertature]


FROM [queryfromtesttable]

WHERE ((([queryfromtesttable].[Client Name]) Like "clientname*")
AND (([queryfromtesttable].[Collected Date])>=[Beginning Date]
And ([queryfromtesttable].[Collected Date])<=[Ending Date])
AND (([queryfromtesttable].[Test Type]) Like "test type sought"
Or([queryfromtesttable].Test Type]) Like "different test type sought"));

--
Somecallmejosh


:

Post your query.

:

I have a report that includes [test_results] which are subcategorized by
client [location]. I'd like the report to show the [location], even if there
are no [test_results] recorded for that [location]. The query supporting the
report is based on a date range in which the test was conducted.

I hope I've given enough information for this to make sense. Is it possible
to pull in these [locations] without [test_results]? Any advice will be
greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top