A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date help please



 
 
Thread Tools Display Modes
  #1  
Old April 8th, 2009, 02:42 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 8th, 2009, 03:22 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old April 8th, 2009, 04:02 PM posted to microsoft.public.access.queries
Noëlla Gabriël
external usenet poster
 
Posts: 79
Default 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  
Old April 8th, 2009, 04:06 PM posted to microsoft.public.access.queries
John Spencer MVP
external usenet poster
 
Posts: 533
Default 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  
Old April 8th, 2009, 04:50 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.