can a table be opened in access by todays date

[Trev]

Hi
Can anyone point me in the right direction here, I would like to open
a table in access 2003 by date. I have an asp web page which needs to
read data from a table with each days today's date (which ever day
that is) then a new table is created with today's date.

Example:
I have a table called 17-may-2007 my ASP page reads this table for
24hours then tomorrow (12:00 midnight 18th) I will have a new table
called 18-may-2007 and the old table is left behind (As 17-may-2007)
so I need my sql statement to automatically open table by today's
date.


This is what I have so far but it does not work. Any ideas

'---------------Start of code---------------
Call BarGraphSQLData( _
"SELECT id, DataReading FROM DAY(CURRENT_TIMESTAMP) ORDER BY id" ,
_
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("db/data.mdb") & ";" , _

'---------------End of code---------------


Thanks


Trevor

 

[RBear3]

Naming a table a date violates relational database normalization rules.

The proper way to do this would be to have just one table that includes a
date. Then, filter the data so that only records with today's date appear.

 

[RBear3]

Please do not post separate copies of messages to different newsgroups. If
your topic could fit into more than one, include all the newsgroups in the
original message so that a linked copy will be placed in each group. Then,
when an answer is posted in any particular group, it will be visible in all
the groups so people will know the message has been answered.

..

 

[Trev]

Please do not post separate copies of messages to different newsgroups. If
your topic could fit into more than one, include all the newsgroups in the
original message so that a linked copy will be placed in each group. Then,
when an answer is posted in any particular group, it will be visible in all
the groups so people will know the message has been answered.

.










- Show quoted text -

Thanks RBear3

ye sure I can, if i knew how?

Anyway thanks for the feedback it does help, I'm not a programmer or a
db administrator by any means but i think i know what you mean......
have i got the right? todays ping stats which would be saved in the
table as 17-may-2007 then tomorrows ping stats would be saved in the
table as 18-may-2007 so on each asp page i would use a stament

Something like:

"SELECT today=now(), DataReading FROM tbldata ORDER BY id" , _

or

"SELECT DAY(CURRENT_TIMESTAMP), DataReading FROM tbldata ORDER BY
id" , _


Are any of these statments about right and which one would you
recomend?


Thanks

Trev

 

[John W. Vinson]

Anyway thanks for the feedback it does help, I'm not a programmer or a
db administrator by any means but i think i know what you mean......
have i got the right? todays ping stats which would be saved in the
table as 17-may-2007 then tomorrows ping stats would be saved in the
table as 18-may-2007 so on each asp page i would use a stament

Something like:

"SELECT today=now(), DataReading FROM tbldata ORDER BY id" , _

or

"SELECT DAY(CURRENT_TIMESTAMP), DataReading FROM tbldata ORDER BY
id" , _


Are any of these statments about right and which one would you
recomend?

Both are wrong, unfortunately. Now() does NOT return today's date; it returns
the current date and time accurate to a few microseconds. And Day() doesn't
return a date, it returns a Long Integer day number - 17 today, 18 tomorrow, 1
in a couple of weeks - so it would not distinguish May 1, June 1 and July 1.

If you will be (very unwisely, in my opinion!!) storing DATA - a date - in the
tablename, then you must construct the SQL string entirely in code, using
(e.g.)

Format(Date(), "dd-MMM-yyyy")

to construct the string used as the table name. Much better would be to have
one pings table with a Date/Time field and use a Query selecting today's date,
or any desired date, from the table:

SELECT DataReading FROM tblDate
WHERE [datefield] = Date()
ORDER BY ID;

If you object that the table is too large if you store all the records in one
table, bear in mind that in Access all the data is stored in one database
ANYWAY - whether that data is stored in one table or many tables does not
change that fact.

John W. Vinson [MVP]

 

[Trev]

Anyway thanks for the feedback it does help, I'm not a programmer or a
db administrator by any means but i think i know what you mean......
have i got the right? todays ping stats which would be saved in the
table as 17-may-2007 then tomorrows ping stats would be saved in the
table as 18-may-2007 so on each asp page i would use a stament

Something like:

"SELECT today=now(), DataReading FROM tbldata ORDER BY id" , _

"SELECT DAY(CURRENT_TIMESTAMP), DataReading FROM tbldata ORDER BY
id" , _

Are any of these statments about right and which one would you
recomend?

Both are wrong, unfortunately. Now() does NOT return today's date; it returns
the current date and time accurate to a few microseconds. And Day() doesn't
return a date, it returns a Long Integer day number - 17 today, 18 tomorrow, 1
in a couple of weeks - so it would not distinguish May 1, June 1 and July 1.

If you will be (very unwisely, in my opinion!!) storing DATA - a date - in the
tablename, then you must construct the SQL string entirely in code, using
(e.g.)

Format(Date(), "dd-MMM-yyyy")

to construct the string used as the table name. Much better would be to have
one pings table with a Date/Time field and use a Query selecting today's date,
or any desired date, from the table:

SELECT DataReading FROM tblDate
WHERE [datefield] = Date()
ORDER BY ID;

If you object that the table is too large if you store all the records in one
table, bear in mind that in Access all the data is stored in one database
ANYWAY - whether that data is stored in one table or many tables does not
change that fact.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Hi,
Just to let you know

This was solved by using
"SELECT DateTaken, DataReading FROM tblData WHERE DateTaken =
Date()" , _

Thanks, for everyone's help.

Trev