CODEWITHSUNDEEP
sqlms-access
shwan
shwan

Reputation: 570

SQL: Selecting between date range

My query returns 1 value if I use the Max(SampleDateTime) or Min( ) on the Date/Time field I want, but it returns no values if I leave out the Max or Min. I want to return ALL the values, but I can't seem to figure this out.

I want all the Quality Samples between the Start and Stop times of a Production Run.

RunSamples: 

Select Max([SampleDateTime]) 
FROM [QualitySamples] AS [GoodSamples] 
WHERE [GoodSamples].[SampleDateTime] >= [ProductionRuns_tbl].[RunStartDate] 
    AND [GoodSamples].[SampleDateTime] <= [ProductionRuns_tbl].[RunEndDate]

ProductionRuns_tbl:
RunStartDate RunEndDate
1/1/2017 12 AM 1/5/17 12 AM
...

QualitySamples Tbl:
ID SampleDateTime
1 1/1/2017 2 am
2 1/1/2017 3 am
...

Here's the full SQL code:

SELECT ProductionRuns_tbl.RunName, ProductionRuns_tbl.RunStartDate, 
ProductionRuns_tbl.RunEndDate,
 (Select Max([SampleDateTime]) 
 FROM [QualitySamples] AS [GoodSamples] 
 WHERE [GoodSamples].[SampleDateTime] >= [ProductionRuns_tbl].[RunStartDate] 
 AND [GoodSamples].[SampleDateTime] <= [ProductionRuns_tbl].[RunEndDate])
AS RunSamples
FROM ProductionRuns_tbl
WHERE (((ProductionRuns_tbl.RunName)=[Forms]![Home]![RunName]));

Upvotes: 1

Views: 71

Answers (3)

Joe
Joe

Reputation: 1157

I'm taking a risk posting right now, because I had to try to read your mind on what you're trying to do (plus, I don't know if this will work in Access, but it will work in SQL server)

Since you want all the data, is this what you're looking for?

SELECT 
    ProductionRuns_tbl.RunName, 
    ProductionRuns_tbl.RunStartDate, 
    ProductionRuns_tbl.RunEndDate,
    [QualitySamples].[SampleDateTime]
FROM
    ProductionRuns_tbl
LEFT JOIN
    [QualitySamples]
ON
    [QualitySamples].[SampleDateTime] >= [ProductionRuns_tbl].[RunStartDate] 
    AND
    [QualitySamples].[SampleDateTime] <= [ProductionRuns_tbl].[RunEndDate]
WHERE 
    (((ProductionRuns_tbl.RunName)=[Forms]![Home]![RunName]));

This should list the RunName, Start and End dates repeated for each individual SampleDateTime. Based on your more specific requirements, you can then refine the results from there.

Upvotes: 0

Robert
Robert

Reputation: 486

Try to use join instead:

SELECT ProductionRuns_tbl.RunName,
       ProductionRuns_tbl.RunStartDate, 
       ProductionRuns_tbl.RunEndDate,
        GoodSamples.SampleDateTime
FROM QualitySamples GoodSamples INNER JOIN ProductionRuns_tbl ON 
    GoodSamples.SampleDateTime >= ProductionRuns_tbl.RunStartDate AND 
    GoodSamples.SampleDateTime <= ProductionRuns_tbl.RunEndDate
WHERE ProductionRuns_tbl.RunName=[Forms]![Home]![RunName]

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 13009

Dont have WHERE, MAX or MIN. Just have the SELECT query.

Select [SampleDateTime]
FROM [QualitySamples] AS [GoodSamples]

Upvotes: 0

Related Questions