467,864 Members | 1,820 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,864 developers. It's quick & easy.

Date range query help please.

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
  • viewed: 1747
20 Replies
Expert 2GB
Hello, kujito.

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

Apr 14 '09 #2
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
Expert 2GB

A simple grouping query can do it. Something like the following:
Expand|Select|Wrap|Line Numbers
  1. SELECT ProjectID, Min([Year]) & "-" & Max([Year]) As txtPeriod
  2. FROM Production
  3. GROUP BY ProjectID;
Apr 14 '09 #4
Thanks, I'll give that a whirl.
Apr 14 '09 #5
Expert Mod 16PB
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
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.
Apr 21 '09 #7
Expert 2GB
Ok, Brian.

Do you have some skills in VBA?
Apr 21 '09 #8
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
Expert 2GB

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.
Expand|Select|Wrap|Line Numbers
  1. Public Function YearsOfNoProduction(lngMineID As Long) As String
  2. ........
  3. End Function
Then it will be invoked in query:
Expand|Select|Wrap|Line Numbers
  1. 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
Apr 21 '09 #10
Expert Mod 16PB
I'm not sure, but could
Producing a List from Multiple Records be helpful?
Apr 21 '09 #11
Expert 2GB
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
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
Expert Mod 16PB
OK Fish. I'll leave this in your capables then :)
Apr 21 '09 #14
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
Expert 2GB
Add the following function to code module.

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
  5.     Dim rs As New ADODB.Recordset
  6.     Dim intYear As Integer 'variable to store previous record year
  8.     If IsNull(lngProjectID) Then Exit Function
  10.     With rs
  12.         'set properties of Recordset object
  13.         .ActiveConnection = CurrentProject.Connection
  14.         .CursorType = adOpenForwardOnly
  15.         .LockType = adLockReadOnly
  16.         'open source table filtered by ProjectID and ordered by Year
  17.         .Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
  18.             lngProjectID & " ORDER BY intYear;"
  20.         'if recordset is not empty then start computing
  21.         If Not (.EOF And .BOF) Then intYear = !intYear
  23.         'iterate recordset
  24.         While Not .EOF
  25.             'add non-existing years to output
  26.             While intYear < !intYear - 1
  27.                 intYear = intYear + 1
  28.                 YearsNoMO = YearsNoMO & intYear & ", "
  29.             Wend
  30.             intYear = !intYear
  31.             .MoveNext
  32.         Wend
  34.         'close recordset
  35.         .Close
  37.     End With
  39.     Set rs = Nothing
  41.     'remove trailing comma and space
  42.     If Len(YearsNoMO) <> 0 Then _
  43.         YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
  45. End Function
And use it in your query.
Apr 21 '09 #16
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
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
Expert 2GB
Example worth thousand words.
Attached Files
File Type: zip kujito.zip (13.6 KB, 53 views)
Apr 22 '09 #19
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.

I may have to up it from beers to a nice bottle of booze.
Attached Files
File Type: zip kujito2.zip (35.3 KB, 58 views)
Apr 22 '09 #20
Expert 2GB
  • 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
    5.     Dim rs As New ADODB.Recordset
    7.     If IsNull(lngProjectID) Then Exit Function
    9.     With rs
    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;"
    20.         'iterate recordset
    21.         While Not .EOF
    22.              YearsNoMO = YearsNoMO & intYear & ", "
    23.              .MoveNext
    24.         Wend
    26.         'close recordset
    27.         .Close
    29.     End With
    31.     Set rs = Nothing
    33.     'remove trailing comma and space
    34.     If Len(YearsNoMO) <> 0 Then _
    35.         YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
    37. End Function
Apr 23 '09 #21

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Tony | last post: by
12 posts views Thread by Steve Elliott | last post: by
67 posts views Thread by PC Datasheet | last post: by
2 posts views Thread by sixdeuce62 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.