Date range query help please.

[kujito]

I'm working with mine production data in Access 2007. I'll try to make this make sense and keep it simple (mostly to not confuse myself further).

The range of years operating varies between mines. For example, I have data for one of the mines for the years 1961 through 1991. How can I get a query to report the years of operation, like 1961 - 1991, in a single cell? The Production.Year column of the Production table is formatted as a number and not a year/date. I can't change that(permissions).

The fields should be something like:
ProjectID (from Project.ProjectID)
DepositName (from Project.ProjectName)
Country (from Project.CountryName)
YearsOperating (from Production.Year) This is the one I need.

[FishVal]

Hello, kujito.

Though I didn't understand your question completely, the following thread might give you some clues.
Grouping Query Range

Regards,
Fish

[kujito]

Sorry about the confusion. Take two:
I have a production table which has data for all the years that the mines were/are operating. The Production table (simplified) looks like:
ProductionID(pk), ProjectID(fk, Project table pk), Year, Metal1, Metal2, Produced1, Produced2

Each mine is represented by one record and ProductionID for each year of operation. I'd like to have the range of operating years shown in a single cell, like 1961-1991 for the Climax mine, 1976 - 2006 for the Henderson mine, and so on. I have other data I need in the query, but I have been able to get those.
I'm trying for something like this:
P-ID | Name | Country | YearsOP |
31086 | Climax | USA | 1961-1991 |
28919 | Henderson | USA | 1076-2006

[FishVal]

Well.

A simple grouping query can do it. Something like the following:

SELECT ProjectID, Min([Year]) & "-" & Max([Year]) As txtPeriod
FROM Production
GROUP BY ProjectID;

[kujito]

Thanks, I'll give that a whirl.

[NeoPa]

That link may give you a full answer, but basically, you need a GROUP BY clause to exclude the years. Aggregate functions can be used to return the Min() & Max() of the years to display.

[kujito]

Thanks FishVal! I got it to work with only minor tweeking!
It's been a few days, and now there's a new requirement (of course). I now need an additional field which selects the years for which there was no production.
Continuing with the theme from above:
P-ID | Name | Country | YearsOP | YearsNoMO
31086 | Climax | USA | 1961-1991 | 1983, 1988
28919 | Henderson | USA | 1976-2006 | 1975, 1983
104004 | Endakko | CAN | 1965-2007 | 1999, 2000, 2001, 2002, 2006

The years without production values vary, both in year and number of years, by mine.
Can I select multiple values like that to a single cell? If so, how?
I'm still looking around, but this seems like the place for real answers.
TIA,
Brian

[FishVal]

Ok, Brian.

Do you have some skills in VBA?

[kujito]

Some, at a novice level. I did take an 'Intro to Visual Basic' class. I've not had to do much with it, but I'm not totally unfamiliar with how the scripts look/work as ArcGIS (just got my BS in GIS) uses VB for some things.
Why, what do you have in mind? I'm willing to try/learn. Is it something that's already part of Access? I don't have Visual Studio here.

I don't have to have this out right away, so I've got some time to make it work.
I really appreciate the help btw.

[FishVal]

Good.

Access allows to call public functions written in VBA from query. This gives a very suitable method to get calculated field in context of each record fetched by query.
Let us say, in your case it will be function getting [P-ID] and returning comma separated list of "no production years".

The function has to be placed in public code module.

Public Function YearsOfNoProduction(lngMineID As Long) As String
........
End Function
 

Then it will be invoked in query:

SELECT ....., YearsOfNoProduction([P-ID]) AS YearsNoMO FROM .....
 

The function code do the following:

• opens a recordset from your source table filtered by [P-ID] and ordered by year
• iterates the recordset
• if next record year is not current record year + 1 then add to output string sequential years until it is

[NeoPa]

I'm not sure, but could
Producing a List from Multiple Records be helpful?

[FishVal]

@NeoPa
This requires non-existing year/mine combinations to be fetched as records.
It could be achieved by holding auxiliary table with sequential years list. This table is joined to produce cartesian product with distinct list of mines, and thus obtained dataset is filtered with outer join with OP's table. And it is only a half of the story. ;)

[kujito]

Ya, I looked at the help menu stuff about multivalued cells. It got me nowhere.

A little fuzzy on the VBA stuff you posted FishVal.
Where in Access would I do this? Not sure of the syntax either.

[NeoPa]

@FishVal
OK Fish. I'll leave this in your capables then :)

[kujito]

Found the 'VB' window in the 'Macro' tab. Need to go home and brush up on my vb syntax tonight. Hopefully I can figure this out, I'll be back asking questions again tomorrow if not.
Any hints are welcome in the mean time. :)

[FishVal]

@kujito
Add the following function to code module.

Public Function YearsNoMO(lngProjectID As Variant) As String
'lngProjectID is expected to be a Long integer, but since query field
'could be Null it is declared as Variant
 
    Dim rs As New ADODB.Recordset
    Dim intYear As Integer 'variable to store previous record year
 
    If IsNull(lngProjectID) Then Exit Function
 
    With rs
 
        'set properties of Recordset object
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        'open source table filtered by ProjectID and ordered by Year
        .Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
            lngProjectID & " ORDER BY intYear;"
 
        'if recordset is not empty then start computing
        If Not (.EOF And .BOF) Then intYear = !intYear
 
        'iterate recordset
        While Not .EOF
            'add non-existing years to output
            While intYear < !intYear - 1
                intYear = intYear + 1
                YearsNoMO = YearsNoMO & intYear & ", "
            Wend
            intYear = !intYear
            .MoveNext
        Wend
 
        'close recordset
        .Close
 
    End With
 
    Set rs = Nothing
 
    'remove trailing comma and space
    If Len(YearsNoMO) <> 0 Then _
        YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
 
End Function
 

And use it in your query.

[kujito]

Wow! Thanks again! If you weren't so far away, I'd take you out for some beers (if you're ever in/around Denver, CO, USA ...).
I probably wouldn't have been able to come up with all that in a decent amount of time. I'll have to play around with that code and see what I can do.

Thanks again! He doesn't know it yet, but you just made my boss very happy!

[kujito]

Sorry for the length on this one.
OK, so I finally got to it.
All I changed from the code you gave me is from this:

.Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
lngProjectID & " ORDER BY intYear;"

The bold 'tbl' was changed to Production (the table containing the year data, ProjectID is fk). I also tried it with Project (the table with ProjectID as pk).

My query looks like this:

SELECT Project.ProjectID, Project.ProjectName, YearsNoMO([Production.ProjectID]) AS NoMO
FROM Project INNER JOIN Production ON Project.ProjectID = Production.ProjectID
WHERE Project.PrimaryMO = 1;

The 'PrimaryMO' simply restricts it to Primary MO producers. Once I get this straight, I will be running one for 'PrimaryMO = 2'; bi-product producers.

The query results in an error message:

Run-time error '-2147217904 (80040e10)'
No value given for one or more required parameters.

Clicking "Debug" results in this bit being highlighted:
.Open "SELECT intYear FROM Production WHERE lngProjectID=" & _ lngProjectID & " ORDER BY intYear;"

This is where I changed the original 'tbl' to 'Production' or 'Project' (tried both).
Not sure what to do now. The debugger seems to have locked me out of/frozen everything else. When I try to close the VB window, I get a message that doing this will stop the debugger. Click 'OK' and I get the same error message as above.

[FishVal]

Well.
Example worth thousand words.

[kujito]

Ah, see, my Production table is not MISSING the years where there is no MO produced. The no production years have a 0 (zero) entry for Production.Produced1 (for Project.PrimaryMO = 1, it would be a 0 for Production.Produced2 for Project.PrimaryMO = 2)

I've added my (trimmed down) Project and Production tables to the dbse you sent me. I've also queried out the Projects and Productions I'm concerned with at the moment. Maybe that will help clear things up? I'll see if I can't figure something out with what you've sent me so far.

There's nothing that's not publicly available in the tables. I did a lot of web a library research to find it, but it's nothing proprietary or any such concerns.

p.s.
I may have to up it from beers to a nice bottle of booze.

[FishVal]

• It makes method suggested by NeoPa in post #11 quite feasible.
• The records with 0 production could be filtered out and the code will work as they never were.
• Actually, this case the code could be significantly simplified
  

  Public Function YearsNoMO(lngProjectID As Variant) As String
  'lngProjectID is expected to be a Long integer, but since query field
  'could be Null it is declared as Variant
   
      Dim rs As New ADODB.Recordset
   
      If IsNull(lngProjectID) Then Exit Function
   
      With rs
   
          'set properties of Recordset object
          .ActiveConnection = CurrentProject.Connection
          .CursorType = adOpenForwardOnly
          .LockType = adLockReadOnly
          'open source table filtered by ProjectID and ordered by Year
          .Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
              lngProjectID & "AND [Produced]=0;"
   
   
          'iterate recordset
          While Not .EOF
               YearsNoMO = YearsNoMO & intYear & ", "
               .MoveNext
          Wend
   
          'close recordset
          .Close
   
      End With
   
      Set rs = Nothing
   
      'remove trailing comma and space
      If Len(YearsNoMO) <> 0 Then _
          YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
   
  End Function