Solved

create a timeline dynamically in access 2007

Posted on 2010-09-23
8
1,042 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
  • 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 47

Accepted Solution

by:
Dale Fye (Access MVP) 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

785 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