Report expression errors

[WoG18]

I am trying to build a report from 4 different tables. They all have the same fields, but they are 4 different "years". (yeah- I know- I didnt design it) There are ID's associated with different dollar amounts.

What I need is a report that shows the ID's with each "years" data per line. (They want to check out trends with the dollar amounts) Some of the IDs have records for all 4 years, some dont.

How would I display this information on each line, adding the 2nd, 3rd, and 4th years data to the line if and only if they have that data.

ALSO it needs to obviously display the IDs if they dont have that 2nd, 3rd, 4th, etc.

I am having trouble with the data source- I keep getting the cursed #name where my data should be.

Here is how I am trying this (in the report):
ID: table.ID dollar: table.ID ID2nd year: table.ID=table.ID2 dollar2: table.ID2 and so on...

Anyone have a similar experience?

Thanks
Jesse

 

[cityboy3]

I would start by turning your 4 tables into one big data set. After that, the report design should be trivial.

Consolidating your data set requires 2 things:
1) Merging all 4 tables into a single recordset
2) Creating a Year field to hold the data for each

In the Recordsource property of your report create a UNION query. (If you're not sure of the syntax, check out the Help under UNION queries) The query will have all the fields from the individual tables. In addition, you'll have to create a new, calculated field for each table with the year value.

Example
SELECT id, dollars, "2000" AS Year From Year2000table UNION SELECT id, dollars, "2001" as Year From Year2001table

etc...

should give you 1 big table with everything you need to set up the report