By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,082 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,082 IT Pros & Developers. It's quick & easy.

Date range query help please.

P: 19
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
Share this Question
Share on Google+
20 Replies


FishVal
Expert 2.5K+
P: 2,653
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

P: 19
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
Expert 2.5K+
P: 2,653
Well.

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

P: 19
Thanks, I'll give that a whirl.
Apr 14 '09 #5

NeoPa
Expert Mod 15k+
P: 31,494
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

P: 19
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
Expert 2.5K+
P: 2,653
Ok, Brian.

Do you have some skills in VBA?
Apr 21 '09 #8

P: 19
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
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function YearsOfNoProduction(lngMineID As Long) As String
  2. ........
  3. End Function
  4.  
Then it will be invoked in query:
Expand|Select|Wrap|Line Numbers
  1. SELECT ....., YearsOfNoProduction([P-ID]) AS YearsNoMO FROM .....
  2.  
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
Expert Mod 15k+
P: 31,494
I'm not sure, but could
Producing a List from Multiple Records be helpful?
Apr 21 '09 #11

FishVal
Expert 2.5K+
P: 2,653
@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

P: 19
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
Expert Mod 15k+
P: 31,494
@FishVal
OK Fish. I'll leave this in your capables then :)
Apr 21 '09 #14

P: 19
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
Expert 2.5K+
P: 2,653
@kujito
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
  4.  
  5.     Dim rs As New ADODB.Recordset
  6.     Dim intYear As Integer 'variable to store previous record year
  7.  
  8.     If IsNull(lngProjectID) Then Exit Function
  9.  
  10.     With rs
  11.  
  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;"
  19.  
  20.         'if recordset is not empty then start computing
  21.         If Not (.EOF And .BOF) Then intYear = !intYear
  22.  
  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
  33.  
  34.         'close recordset
  35.         .Close
  36.  
  37.     End With
  38.  
  39.     Set rs = Nothing
  40.  
  41.     'remove trailing comma and space
  42.     If Len(YearsNoMO) <> 0 Then _
  43.         YearsNoMO = Left(YearsNoMO, Len(YearsNoMO) - 2)
  44.  
  45. End Function
  46.  
And use it in your query.
Apr 21 '09 #16

P: 19
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

P: 19
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
Expert 2.5K+
P: 2,653
Well.
Example worth thousand words.
Attached Files
File Type: zip kujito.zip (13.6 KB, 40 views)
Apr 22 '09 #19

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

p.s.
I may have to up it from beers to a nice bottle of booze.
Attached Files
File Type: zip kujito2.zip (35.3 KB, 46 views)
Apr 22 '09 #20

FishVal
Expert 2.5K+
P: 2,653
  • 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

Post your reply

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