Combine Table Fields by date

[sniperman]

I have started to run into some unusual problems with MS Access 2007 when I enter Query Design.

The objective is to create a list of items from 3 separate tables that can be pivoted.

Table: Expenses
Field 1: Amount
Field 2: Date

Table: Revenue
Field 1: Amount
Field 2: Date

Table: Time Spent
Field 1: Amount
Field 2: Date

I tried to create a query which listed the items but the query builder places each field beside each other. Bad data. I am looking to create a result as such in Datasheet view.

Amount || Date || Expenses Table Data
Amount || Date || Expenses Table Data
Amount || Date || Revenue Table Data
Amount || Date || Time Spent Table Data
Amount || Date || Expenses Table Data
Amount || Date || Revenue Table Data

I would also like to filter the query to be between a month. Since the dates on each table have no causal relationship between each other I find both steps impossible. I usually receive a "not responding" message as soon as I attempt to use a pivot table on the query.

Any ideas?

[sniperman]

no one has any pointers still on how to consolidate the data or it's not possible in MS Access?