reporting with expressions question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
ID: table.ID dollar: table.ID ID2nd year: table.ID=table.ID2 dollar2: table.ID2 and so on..

Anyone have a similar experience

Thank
Jesse
 
jrk said:
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:
ID: table.ID dollar: table.ID ID2nd year: table.ID=table.ID2 dollar2: table.ID2 and so on...
Click to expand...


If you want the data from each table in the same detail
record, then you add all four table to the report record
source query and make sure the connecting line from table1's
ID field to the other table's ID fields. Then right click
on the line and select the Join type that has all records
frome table1 and any matching records from the other table.

Now you can drag the field from each table down to the
query's field list. Only add the ID field from table1 and
provide an alias name for all the other fields, probably
something like:

table1.ID
Dollar1: Table1.Dollar
Dollar2: Table2.Dollar
. . .

That should get you fairly close to what you need for the
report with Null values for the fields that don't exist in
one of the other tables.

Most likely, you'll have a couple of follow up questions, so
post back if you need more assistance.
 
Excellent, that definately gets me on the right track. Thanks

The only problem, is it is showing more records than it should be. For example, the maximum dollar amounts an ID can have is 2, and some ID's show 4 (identical). I even tried changing the properties - Unique values to try and fix it

Any ideas?
 
jrk said:
Excellent, that definately gets me on the right track. Thanks.

The only problem, is it is showing more records than it should be. For example, the maximum dollar amounts an ID can have is 2, and some ID's show 4 (identical). I even tried changing the properties - Unique values to try and fix it.
Click to expand...


I would have to see the query to be sure, but it sounds like
you may have some duplicate entries in one or more of the
tables. Try running the query all by itself (from the query
design window). If the query result really has some records
with a duplicate ID, it shouldn't be too hard to find the
table and the rows given that you know the offending ID.

If that isn't the issue, post back with a copy/paste of the
query's SQL statement.
 
Back
Top