query multiple tables using the current date

[DanDarwood2004]

I am trying to write a query that pulls information from a number of tables which are used as daily logs (with date as their primary key) for the current day. Not all of these tables are updated every day so I need a universal/system date to link them all together. I tired to use a query that returns the system date:

SELECT Now() AS SystemDate;

which works fine by itself but isn't allowed when used within another query as a linked table.

I can see having a date table within the database would solve this but I don't want to do this as it would require updating everyday and I'm sure there is a better and simpler solution.

 

[SkipVought]

Dan,

You you are selecting based on a date, you would want to do soemthing like

Select *
From MyTable
Where MyDateField = Date()

Now() includes TIME as well (Date Value like 38565.33225 which is 8/1/2005 7:58 AM here in Texas)



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[DanDarwood2004]

The thing is that I am pulling quite a few tables together in the one query so would I not need a situation like:

SELECT ...
FROM MyTable1, MyTable2, MyTable3, MyTable4 ...
WHERE MyTable1.DateField = Date()
AND MyTable2.DateField = Date()
AND MyTable3.DateField = Date()
AND MyTable4.DateField = Date()
...

But this would also mean that if one of these tables had no record the query would return nothing ... maybe I just need to have a break and some coffee

I was thinking that if I linked the tables to a common date I would get around this.

 

[PHV]

Have a look at outer join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Are these tables have an identical schema? If so do a series of UNIONS.

If not, how are they related?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[DanDarwood2004]

these tables aren't related to each other but as I they all have the date as a primary key I thought that I could link them in a query (if I had a global date table then they could all be linked to that I guess and all would be fine).

 

[DanDarwood2004]

UNIONS did the trick thanks.

Dan.