Solved

create a timeline dynamically in access 2007

Posted on 2010-09-23
8
1,063 Views
Last Modified: 2012-06-27
I would like to create a timeline table in access using a beginning and end date provided by the user. For example, a query prompts the user for two inputs, he types in 10/18/2008 and 10/20/2008. The query then creates a table which contains every hour between those two date:


DATE      HOUR
10/18/2008      0
10/18/2008      1
10/18/2008      2
10/18/2008      3
10/18/2008      4
10/18/2008      5
10/18/2008      6
10/18/2008      7
10/18/2008      8
10/18/2008      9
10/18/2008      10
10/18/2008      11
10/18/2008      12
10/18/2008      13
10/18/2008      14
10/18/2008      15
10/18/2008      16
10/18/2008      17
10/18/2008      18
10/18/2008      19
10/18/2008      20
10/18/2008      21
10/18/2008      22
10/18/2008      23
10/19/2008      0
10/19/2008      1
10/19/2008      2
10/19/2008      3
10/19/2008      4
10/19/2008      5
10/19/2008      6
10/19/2008      7
10/19/2008      8
10/19/2008      9
10/19/2008      10
10/19/2008      11
10/19/2008      12
10/19/2008      13
10/19/2008      14
10/19/2008      15
10/19/2008      16
10/19/2008      17
10/19/2008      18
10/19/2008      19
10/19/2008      20
10/19/2008      21
10/19/2008      22
10/19/2008      23
10/20/2008      0
10/20/2008      1
10/20/2008      2
10/20/2008      3
10/20/2008      4
10/20/2008      5
10/20/2008      6
10/20/2008      7
10/20/2008      8
10/20/2008      9
10/20/2008      10
10/20/2008      11
10/20/2008      12
10/20/2008      13
10/20/2008      14
10/20/2008      15
10/20/2008      16
10/20/2008      17
10/20/2008      18
10/20/2008      19
10/20/2008      20
10/20/2008      21
10/20/2008      22
10/20/2008      23

any thoughts on how to create this make table query?
0
Comment
Question by:Forensicon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:CRJ2000
ID: 33751064
The easiest method would be to use VBA. However, if you want to do it with a query, I would approach it in this manner:

1. Create a table (CALENDAR_DATE, for example) that has a list of all possible dates that the user could choose. Populate this table with one row per date.

2. Create a table (HOUR_OF_DAY, for example) that has the numbers 0 through 23 -- one row per hour.

3. Create a prompted Make Table query that includes both of those tables (without a join). The prompt should limit the dates on the CALENDAR_DATE table based on the user's input. By not joining the two tables together, you'll actually have a cartesian product, which will give you one row for each hour in each day returned in the query.

Chris
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 500 total points
ID: 33752916
I use a table and a query to generate a series of numbers, then use that query and the dateadd function to generate the Dates and Times.  See the attached.

I use tbl_Numbers and qry_Numbers or qry_Numbers_to_99 whenever I need a sequene of dates.
DateGenerator.mdb
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33753163
*laughing* so do I...

A dynamic query based on numbers to generate all dates / hours from [my_start_date] through to [my_end_date] :

SELECT dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)) AS The_date
FROM numbers AS days, (SELECT n FROM numbers WHERE n between 0 and 23) AS hours
WHERE days.n<=datediff("d",My_Start_Date,My_End_Date)
ORDER BY days.n, hours.n;


and "numbers" is a table with one column "n" of integers starting from 0 and going to how ever many days, or the down arrow gets too boring to hold down. You only really need as many days out as they can specify a date range for...

I use the numbers table for a wide range of activities... So, generally have a few thousand numbers in there...

Dynamic-Calendar-Query.mdb
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:Forensicon
ID: 33756351
Great responses guys! Fyed's db is a little closer to what i'm looking for. I would like to use military times, however, fyed's number goes 1-24, when there is no 24th hour, so it should actually go 0-23. Not quite sure how to make this change. Mark's solution seems a little more elegant and simple, but it's not in military time which is how all my data is presented. Also, in Mark's db, when it gets to midnight on a day, it doesn't display a time, just the date.

Both samples are really very good. And i hope one of you wouldn't mind making these necessary changes.

Thanks!
0
 

Author Comment

by:Forensicon
ID: 33756410
Oh, forgot to mention, vba is fine, actually that is encouraged because my db is all run through vba.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 33756794
just change the criteria statement to:

BETWEEN 0 and 23
0
 

Author Closing Comment

by:Forensicon
ID: 33756854
Great Solution! Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33758129
It was generating a datetime column, so really was displaying just the default datetime style.

I did notice your requirement had TIME as a seperate column, but thought if you were ever going to do any date type arithmetic, then it really is better as a datetime.

Anyway, just for "completeness" have a look at some of the options available :

SELECT  dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)) AS The_date_time
    ,  format(dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)),"yyyy-MM-dd hh:mm") as The_Formatted_Date_Time
    ,  dateadd("d",days.n,my_Start_date) As The_Date
    ,  hours.n As The_Hour
FROM numbers AS days, (SELECT n FROM numbers WHERE n between 0 and 23) AS hours
WHERE days.n<=datediff("d",My_Start_Date,My_End_Date)
ORDER BY days.n, hours.n;


or if you created just that original bit as a qry, then you could also do things like present the data as you want it, when you need to (because formatting of a datetime is normally done at the "presentation" layer not necessarily the "raw" data) and notice the ease with which we can do things like datediff (please type in 01 Sep 2010 to 31 Dec 2011 as your date range):

SELECT  format(The_date,"MM/dd/yyyy") as [DATE], datepart("h",The_Date) as [HOUR], datediff("h",The_Date,now()) as age_in_hours
FROM    qCalendar
WHERE   The_Date between now() and dateadd("h",-48,now())


Anyway, hope that does complete the picture for you, and yes I know that this thread has already been closed - just wanting to be "complete"
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question