Criteria for a minimum itself

[hawkins]

I have a list of names, which can have duplicates, another list of dates when the measurements were measured and then the measurements. I have information that needs to be inputted into the first date range, second date range etc.. I tried to create a query that uses the totals minimum function to acquire the minimum value for each date range that I desire. Unfortunately I realized that the criteria portion of the query is not the criteria of where the minimum is based. I used it fine for the first range of dates, 1-1-2010 to 3-30-2010, but if I do the second range, 4-1-2010 to 6-30-2010 it does not show that there are any minimums because each name already has a minimum that is in the first range. Any help will be greatly appreciated!

[June7]

Post the query statement for analysis. Would help to have sample source data.

[hawkins]

Here is the SQL:
SELECT Initial.[FullName], Min(Initial.[Field3]) AS MinOfField3
FROM Initial
GROUP BY Initial.[FullName]
HAVING (((Min(Initial.[Field3])) Between #1/1/2010# And #3/30/2010#));

If i put that second range, 4-1-2010 to 6-30-2010 it will not show up with anything. I figured I would find the minimum dates for each of my ranges and use those minimum dates in a new table to relate back to the initial table to update those records.

[June7]

So you want all records that fall within any of the ranges? That requires an OR operator.
HAVING (((Min(Initial.Field3)) Between #1/1/2010# And #3/30/2010#)) OR (((Min(Initial.Field3)) Between #4/1/2010# And #6/30/2010#));

[hawkins]

I actually want the minimum value for each name for each of the ranges if possible

[June7]

I was afraid you were going there. Will have to do separate queries (then can be included as subqueries in a nested structure) or use DMin(). Give me sample data and I will test. Access file or Excel spreadsheets.

[hawkins]

Here's an example excel sheet of what I am working with. The NameID designates a certain name. This is going to need to work for not only updating the table with new information on the minimum date for a range but also in a report just reporting on that minimum date in the range. I feel if one way is figured out the other will not be too difficult. Thank you for all your help!

[June7]

I am looking at the data you sent. Which is supposed to be Field3? Why commas in the data? Data comes in as text. Min and Max will not work with this text. I assume I can remove commas from the Amps fields and convert to numbers.

Try this:
SELECT NameID, DMin("AmpsA","Example","Dates Between #1/1/2009# AND #3/31/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod1Min, DMin("AmpsA","Example","Dates Between #4/30/2009# AND #6/30/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod2Min, DMin("AmpsA","Example","Dates Between #7/1/2009# AND #9/30/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod3Min, DMin("AmpsA","Example","Dates Between #10/1/2009# AND #12/31/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod4Min
FROM Example
GROUP BY NameID;

[hawkins]

The comma is there because there is supposed to be both amps and kw separated by a comma in that field (230, 50). I tried to compact the number of columns in my table but I can keep them separate until I go to the report if need be. Would you suggest keeping them combined or creating about 18 new columns in the table? I am not very knowledgeable about how the searching works and wasnt too sure which format would be the quickest to search through. Unfortunately, the code doesnt do exactly what I want which was probably caused by my explanation sorry. Instead of finding the minimum values of amps for a given date range, I want it to select the record with the minimum date in a certain range and use the values already for that minimum date. So for example, for NameID=1 and the first range, it would bring back, 1-21-2010, 320, 300, and 310 for the three amp values. I am doing this because I have other information that is stored monthly but I only have information for 4 times a years for that information and only need to report for 4 times a year but they still want as many months as measured. I wanted to add the 4 month data to the example table by adding them to the most recent date record. Sorry for any confusion with my explanation and thanks for your help! I have done alittle work with it but still not really what I want:
SELECT Example.NameID, DMin("Dates","Example","Dates Between #1/1/2009# AND #3/31/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod1Min, DMin("Dates","Example","Dates Between #3/1/2009# AND #6/30/2009# AND NameID='" & [NameID] & "'") AS AmpsAPeriod2Min
FROM Example
GROUP BY Example.NameID;

I was hoping to add the results of AmpsAPeriod2Min to the AmpsAPeriod1Min column so there is a list of all the min dates for 4 times a year in one column.

[June7]

Distinct data should be in separate fields. It is easier to concatenate when needed than to split. Amps and kw are quantitative data and should be numbers instead of text. As I said, Min and Max will not work properly on this text data.

Having multiple similar fields is an indication table structure is not normalized.

I will have to look at this again later.

[hawkins]

I'll set up all the fields that are number fields as number or date fields and create a field for each individual item. So I just talked to my boss and he's put a new little twist on this question. I would still like to know how accomplish what I have asked before but he would like it to be either that specific month or on either side of it. Say for Aprils measurements, he'd prefer april but would take either march or may if the april values are not there. Thanks for the suggestions

[June7]

Okay, let's revisit your original query. Not only do you need to restrict the records to a period, but also want the record with minimum date in that period, for each NameID, and you want it for 4 periods. So Field3 is the date field. Because you want the measurement data, Group By and Min will not work. Here is first period:
SELECT * FROM Example
WHERE Dates=DMin("Dates","Example","NameID='" & [NameID] & "' And Dates Between #1/1/2009# And #3/31/2009#");

Try this WHERE clause for all 4 periods.
WHERE Dates=DMin("Dates","Example","NameID='" & [NameID] & "' And Dates Between #1/1/2009# And #3/31/2009#")
Or Dates=DMin("Dates","Example","NameID='" & [NameID] & "' And Dates Between #4/1/2009# And #6/30/2009#")
Or Dates=DMin("Dates","Example","NameID='" & [NameID] & "' And Dates Between #7/1/2009# And #9/30/2009#")
Or Dates=DMin("Dates","Example","NameID='" & [NameID] & "' And Dates Between #10/1/2009# And #12/31/2009#");

Boss's twist sounds very tricky. So if the current month is April, you want April measurements if there are any. This requires the query to know what the current month is by using Date() function to return current date. Problem with this is that running a report for April on May 1 will not give you results for April. To allow this, would need user input to specify what month to use or hard code the month into the query just as the date range is hard coded in the example above. Here is boss's twist for one period. Using March as the preferred month to work with sample data.
SELECT * FROM Example
WHERE Dates Between #1/1/2009# And #3/31/2009# And Dates=Nz(DMin("Dates","Example","Month(Dates)=3 AND NameID='" & [NameID] & "'"),DMin("Dates","Example","NameID='" & [NameID] & "'"));

Expand the WHERE clause for all 4 periods like in the other example.

Every place where criteria is hard-coded can be replaced with user input. These could be by reference to controls (text, combo, list boxes) on a form.

[hawkins]

for some reason I keep getting a Data type mismatch in criteria expression. I have everything as number types but the date is a date type. I tried switching to different types and even changing the dates from /'s to -'s. Any Suggestions? Thanks for all that help, once I get this data type problem fixed I cant wait to get this to work!

[June7]

Not sure. I fixed the dates in spreadsheet to the / format (sorry, forgot to mention that before) then imported to Access and built queries.

The NameID came in the spreadsheet as a name string, not a numeric ID. Is this field really a number type and you exported the name alias to the sheet? That could be the cause of datatype mismatch. Number values would not have apostrophe delimiters.

[hawkins]

The NameID is a number that is referenced to a static table with a list of feeder names and the corresponding number ID used in the example. How do you import it as a name string? Also, when I try to convert the - to a / in access the number turns into #####, I was wondering how you converted those? Thanks for all the help!