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.

Apr 14 '09 #1

FishVal

Hello, kujito.

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

Regards,
Fish

Apr 14 '09 #2

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

Apr 14 '09 #3

FishVal

Well.

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

Apr 14 '09 #4

kujito

Thanks, I'll give that a whirl.

Apr 14 '09 #5

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.

Apr 14 '09 #6

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

Apr 21 '09 #7

FishVal

Ok, Brian.

Do you have some skills in VBA?

Apr 21 '09 #8

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.

Apr 21 '09 #9

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.
Then it will be invoked in query:
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

Apr 21 '09 #10

NeoPa

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

Apr 21 '09 #11

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. ;)

Apr 21 '09 #12

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.

Apr 21 '09 #13

NeoPa

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

Apr 21 '09 #14

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. :)

Apr 21 '09 #15

FishVal

@kujito
Add the following function to code module.

And use it in your query.

Apr 21 '09 #16

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!

Apr 22 '09 #17

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.

Apr 22 '09 #18

FishVal

Well.
Example worth thousand words.

Apr 22 '09 #19

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.

Apr 22 '09 #20

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
  
  Expand|Select|Wrap|Line Numbers

  1. Public Function YearsNoMO(lngProjectID As Variant) As String
  2. 'lngProjectID is expected to be a Long integer, but since query field
  3. 'could be Null it is declared as Variant
  4.  
  5.     Dim rs As New ADODB.Recordset
  6.  
  7.     If IsNull(lngProjectID) Then Exit Function
  8.  
  9.     With rs
  10.  
  11.         'set properties of Recordset object
  12.         .ActiveConnection = CurrentProject.Connection
  13.         .CursorType = adOpenForwardOnly
  14.         .LockType = adLockReadOnly
  15.         'open source table filtered by ProjectID and ordered by Year
  16.         .Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
  17.             lngProjectID & "AND [Produced]=0;"
  18.  
  19.  
  20.         'iterate recordset
  21.         While Not .EOF
  22.              YearsNoMO = YearsNoMO & intYear & ", "
  23.              .MoveNext
  24.         Wend
  25.  
  26.         'close recordset
  27.         .Close
  28.  
  29.     End With
  30.  
  31.     Set rs = Nothing
  32.  
  33.     'remove trailing comma and space
  34.     If Len(YearsNoMO) <> 0 Then _
  35.         YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
  36.  
  37. End Function
  38.  

Apr 23 '09 #21