MS Access 2002, creating a report from multiple tables using the wizard and errors

[WhatAboutBob?]

Hi there,

I have an issue with creating a report from 4 tables. The tables each
have showdates and info for school plays that are upcoming for the
06/07 school year.

I created a fifth table to include dates and have created relationships
using the date field in the DateTable.

Now two of the plays are during the 2006 year, and the other two are
duing the 2007 half of the school year.

Now, I can create a report using the 2006 plays with the fields that I
want, and the report works beautifully.

When I try to include the 2007 plays the report is blank and only shows
one page with the titles for each field but no data. When I create a
report only using the 2007 plays, it works beautifully and shows
everything I want.

Now, would someone be so kind as to help out with why the reports seems
to have nothing whe I try to include all four tables with the fields I
need from each?BTW MS Access freezes and crashes when I try to make the
report from all 4 tables. I have to 'Ctrl+Alt+Del' and restart Access.

My quick fix is to currently make a new table and append the records
and fields I need from each table, and then create a report on that...
the only thing is then I would have to do a querry to update the new
table...

It would be easier to just make the report work, and keep it rather
than turning one step into 4 or 5 steps!

I am not an MS Access programmer, just a marketing student working for
the Summer. So if there is a way to do this through the wizard that
would help. Oh, and this is MS Access 2002.

Thank you for any and all help

 

[Duane Hookom]

We don't know anything about your table structures and why you have 4
separate tables. I expect you need a union query but that is just a wag.

 

[WhatAboutBob?]

I just am wondering why the 2 tables seem to work, and the 4 tables
will not allow a report to be created.

The tables all have identical field structures, and I only need about 3
fields from each table for all the records.

example of a tables:

table 1: Field.Performance.Loc, Field.Performance.Day,
Field.Performance.Name, etc.

table 2: Field.Performance.Loc, Field.Performance.Day,
Field.Performance.Name, etc.

table 3: Field.Performance.Loc, Field.Performance.Day,
Field.Performance.Name, etc.

table 4: Field.Performance.Loc, Field.Performance.Day,
Field.Performance.Name, etc.


Now, all I want to do is create a report that takes all the reocrds
from each table, with just those 3 fields from each, and creates a
report.

This can not be this hard can it?

Thank you

 

[Duane Hookom]

Again, we don't know much about your tables or the sql you used that doesn't
work for the report.

It might not be that hard if you had used a single table rather than "tables
all have identical field structures".

Try create a single recordset of your tables with

SELECT Field1, Field2, Field3
FROM tblOne
UNION ALL
SELECT Field1, Field2, Field3
FROM tblTwo
UNION ALL
SELECT Field1, Field2, Field3
FROM tblThree;