If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date help please
I asked this question yesterday, but everything I have tried still isn't
getting me what I want. I have 7 columns of dates that are dates that mark stages in a project. Project ID# is my key. I need to know how many projects were there that had no dates entered on Jan. 1, 2009. In other words, the project had not advanced at all before 1/1/2009. There were no contracts, no agreements, no plans drawn, no nothing for these 7 columns. I have tried is null or 1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria fields but I am just not getting the expected results. What else can I try, I just can't get my head around it. I know we have 378 projects with about 170 that have never started and only 6 or 7 that have started since 1/1/2009 but I just can't figure it out. Thanks, so much!!!! |
#2
|
|||
|
|||
Date help please
I would first normalize the table structure so that each date created a new
record in a related table. This would convert your spreadsheet to a relational database. If you can't do that, consider creating a normalizing UNION query like: === quniStageDates ====== SELECT ProjectID, "Start" as Stage, [StartDate] as StageDate FROM tblProject UNION ALL SELECT ProjectID, "Agreement", [AgreeDate] FROM tblProject UNION ALL SELECT ProjectID, "Plans", [PlansDate] FROM tblProject UNION ALL -- etc -- SELECT ProjectID, "Contract", [ContractDate] FROM tblProject; You can then create a query like: SELECT ProjectID, Max(StageDate) as MaxDate FROM quniStageDates Having Max(StageDate)#1/1/2009#; -- Duane Hookom Microsoft Access MVP "golfinray" wrote: I asked this question yesterday, but everything I have tried still isn't getting me what I want. I have 7 columns of dates that are dates that mark stages in a project. Project ID# is my key. I need to know how many projects were there that had no dates entered on Jan. 1, 2009. In other words, the project had not advanced at all before 1/1/2009. There were no contracts, no agreements, no plans drawn, no nothing for these 7 columns. I have tried is null or 1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria fields but I am just not getting the expected results. What else can I try, I just can't get my head around it. I know we have 378 projects with about 170 that have never started and only 6 or 7 that have started since 1/1/2009 but I just can't figure it out. Thanks, so much!!!! |
#3
|
|||
|
|||
Date help please
Hi,
without redesigning your database you could use the NZ function. example: select ProjectID from tblProjects where NZ([Plandate], #12-31-2008#) #1-1-2009#) and NZ([ContractDate],#12-31-2008#) #1-1-2009#) .......... The nz function will replace the empty values with the given date, in this cas 12-31-2008. If the filed contains a value, this date will be returned. -- Kind regards Noëlla "Duane Hookom" wrote: I would first normalize the table structure so that each date created a new record in a related table. This would convert your spreadsheet to a relational database. If you can't do that, consider creating a normalizing UNION query like: === quniStageDates ====== SELECT ProjectID, "Start" as Stage, [StartDate] as StageDate FROM tblProject UNION ALL SELECT ProjectID, "Agreement", [AgreeDate] FROM tblProject UNION ALL SELECT ProjectID, "Plans", [PlansDate] FROM tblProject UNION ALL -- etc -- SELECT ProjectID, "Contract", [ContractDate] FROM tblProject; You can then create a query like: SELECT ProjectID, Max(StageDate) as MaxDate FROM quniStageDates Having Max(StageDate)#1/1/2009#; -- Duane Hookom Microsoft Access MVP "golfinray" wrote: I asked this question yesterday, but everything I have tried still isn't getting me what I want. I have 7 columns of dates that are dates that mark stages in a project. Project ID# is my key. I need to know how many projects were there that had no dates entered on Jan. 1, 2009. In other words, the project had not advanced at all before 1/1/2009. There were no contracts, no agreements, no plans drawn, no nothing for these 7 columns. I have tried is null or 1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria fields but I am just not getting the expected results. What else can I try, I just can't get my head around it. I know we have 378 projects with about 170 that have never started and only 6 or 7 that have started since 1/1/2009 but I just can't figure it out. Thanks, so much!!!! |
#4
|
|||
|
|||
Date help please
SELECT [Project ID#] FROM SomeTable WHERE (Date1 is Null or Date1 #1/1/2009) AND (Date2 is Null or Date2 #1/1/2009) AND (Date3 is Null or Date3 #1/1/2009) AND (Date4 is Null or Date4 #1/1/2009 If this does not work for you, post the SQL that you have and perhaps we can suggest a modification. Duane Hookom is correct that your data structure is incorrect and should be changed if at all possible. I would make one modification to his query to catch those projects where all the dates are null for the project are null SELECT ProjectID, Max(StageDate) as MaxDate FROM quniStageDates Having Max(StageDate)#1/1/2009# OR Max(StageDate) is Null -- John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County golfinray wrote: I asked this question yesterday, but everything I have tried still isn't getting me what I want. I have 7 columns of dates that are dates that mark stages in a project. Project ID# is my key. I need to know how many projects were there that had no dates entered on Jan. 1, 2009. In other words, the project had not advanced at all before 1/1/2009. There were no contracts, no agreements, no plans drawn, no nothing for these 7 columns. I have tried is null or 1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria fields but I am just not getting the expected results. What else can I try, I just can't get my head around it. I know we have 378 projects with about 170 that have never started and only 6 or 7 that have started since 1/1/2009 but I just can't figure it out. Thanks, so much!!!! |
#5
|
|||
|
|||
Date help please
Thanks so much, guys! I will normalize this spreadsheet I imported and then
everything will be easier. But for now, with the boss wanting it right this minute, you got me through. Thanks Again!!! "golfinray" wrote: I asked this question yesterday, but everything I have tried still isn't getting me what I want. I have 7 columns of dates that are dates that mark stages in a project. Project ID# is my key. I need to know how many projects were there that had no dates entered on Jan. 1, 2009. In other words, the project had not advanced at all before 1/1/2009. There were no contracts, no agreements, no plans drawn, no nothing for these 7 columns. I have tried is null or 1/1/2009, I have tried Between 1/1/2000 and 1/1/2009 in all criteria fields but I am just not getting the expected results. What else can I try, I just can't get my head around it. I know we have 378 projects with about 170 that have never started and only 6 or 7 that have started since 1/1/2009 but I just can't figure it out. Thanks, so much!!!! |
Thread Tools | |
Display Modes | |
|
|