Combining dates from two tables in one column

[pandaw1 via AccessMonster.com]

Hi,

I want to (eventually) run a report that has the first column as a date
column, in ascending order. But I want to get the dates from different tables.


So I'm starting with the query - how do I get these two different dates into
the same column?

Many Thanks.

 

[Marshall Barton]

I want to (eventually) run a report that has the first column as a date
column, in ascending order. But I want to get the dates from different tables.


So I'm starting with the query - how do I get these two different dates into
the same column?

It think you will need to use a UNION query. This can not
be done using the query designer, you have to use SQL view
to create the query.

I suggest that you use the query designer to create a simple
select query for one of the tables, then switch to SQL view
and copy/paste and edit until you get it to look something
like:

SELECT datefield, thisfield,thatfield FROM table1
UNION ALL
SELECT datefield, thisfield,thatfield FROM table2
UNION ALL
SELECT datefield, thisfield,thatfield FROM table3

Note that sorting the query is a waste of resources because
report sorting should/must be specified in the report's
Sorting and Grouping window (View menu).

 

[pandaw1 via AccessMonster.com]

Hi Marshall,

Thanks for your reply. I tried the UNION query - but I don't think it will
work. I should have provided more information in my initial question I think.

The report I want to end up with will include the following columns:
Staff Activity Details (multiple columns) - activity carried out on the
project
Invoice Amount - invoice sent to the customer
Expense Details - expenses incurred on the project

Each of these areas has a date attached to it, i.e. when the different
activity was carried out by the employee on the project, when each invoice
was produced, when the expenses were incurred.

The link for all of these is project number (the report will be run for a
specified project number)

So if I do a UNION query, it's looking for the same column info to link
together, which I don't have.

I'm a bit stuck!

Many Thanks,
Adele.

 

[Marshall Barton]

Thanks for your reply. I tried the UNION query - but I don't think it will
work. I should have provided more information in my initial question I think.

The report I want to end up with will include the following columns:
Staff Activity Details (multiple columns) - activity carried out on the
project
Invoice Amount - invoice sent to the customer
Expense Details - expenses incurred on the project

Each of these areas has a date attached to it, i.e. when the different
activity was carried out by the employee on the project, when each invoice
was produced, when the expenses were incurred.

The link for all of these is project number (the report will be run for a
specified project number)

So if I do a UNION query, it's looking for the same column info to link
together, which I don't have.

I still don't follow all that, but it seems like you may
have trouble doing all that with a single query/report. I
suggest that you try using subreports for for the invoices
and expenses.