Choosing which of three tables to query?

[chuckles1066]

Hi all,



First post so be gentle

I have put together a database. I have three tables, only one of which will contain a full year's worth of data at any given time (it's date driven). The other two will contain part-year data.

I have a fourth table which I want to hold the full year's worth of data (it's being referenced elsewhere).

How can I put together a query that "knows" which table to use? Is it possible to do it by a count of records? (The full year's worth of data will obviously contain more records).

Thanks in advance.

[RayMilhon]

Question are the data elements the same? Is the data in the partial year data the same as in the full year table just a different date set? If so, you need to rethink that quickly use 1 table for all of your data and use queries to get the data you need with date parameters for forms/reports when that specific date range is needed.

[chuckles1066]

Question are the data elements the same? Is the data in the partial year data the same as in the full year table just a different date set? If so, you need to rethink that quickly use 1 table for all of your data and use queries to get the data you need with date parameters for forms/reports when that specific date range is needed.

Yes, all four tables contain the same fields.

But where we are in the year drives the population. So between January to March, table1 is "active", between April to June, table2 is "active" etc.

[RayMilhon]

Ok, I would still only use 1 table. Read up on Data Normalization. Everything else can be defined in queries. For Example:

Select * from table1 Where datefield between [startdate] and [enddate]

Everytime you run that query you will be prompted for a start date and end date.

You could include 2 functions in VBA

Public function Startdate() As Date

Dim intmonth as integer
intmonth = Month(now())
Select case intmonth
Case is 1 to 3
startdate = CDate("10/01" & Year(Now())-1) 'Start date = Oct 1 of last year when current month = January, February or March
Case 4 to 6
startdate = CDate("01/01" & Year(Now())) 'Start date = Jan 1 of this year when current month = April, May or June

....
do the same for end date

Then in your queries where clause for the date field You can say between startdate() and enddate()

there are a number of ways to define it