Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

create a timeline dynamically in access 2007

Posted on 2010-09-23
8
Medium Priority
?
1,071 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:Yaniv Schiff
  • 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 49

Accepted Solution

by:
Dale Fye earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Yaniv Schiff
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:Yaniv Schiff
ID: 33756410
Oh, forgot to mention, vba is fine, actually that is encouraged because my db is all run through vba.
0
 
LVL 49

Expert Comment

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

BETWEEN 0 and 23
0
 

Author Closing Comment

by:Yaniv Schiff
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

916 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