Problems with a report based on a query

[Linty Fresh]

I have two tables for a test department/activity database (Which department hosts which activity). They are:

DPT
DPT_ID (PK)
DPT_NAME

ACT
ACT_ID(PK)
ACT_NAME
DPT_ID(FK, referencing DPT.DPT_ID)

This is simple, but it works fine for now (I'll be spicing it up later.).

I created a form, did some data entry, and everything worked fine.

I then created a query to retrieve the departments and their activities:

select DPT.DPT_NAME, ACT.ACT_NAME
from DPT, ACT
where DPT.DPT_ID=ACT.DPT_ID

I ran this query, and it worked fine. I called it qxSelAll.

Here's the problem. So far, everything is working well. I go to make a report and select the wizard. The wizard asks me what I want to base the report on, and I choose qxSelAll from the dropdown menu.

At this point, Access freezes. Every time. I have to bring the task manager up and get out of Access. Did I do something wrong with the query?

I'm running Access 2000 on Windows XP. The report wizard works fine on anything else. Once again, the query itself runs without a hitch, and I don't have any problem entering data. Any thoughts?

 

[Fornatian]

You don't need a where clause if the relationship is perpetual you need to join the tables together as in:

select DPT.DPT_NAME, ACT.ACT_NAME
from DPT INNER JOIN ACT ON DPT.DPT_ID=ACT.DPT_ID

...or thereabouts

 

[Linty Fresh]

That did it!!

Wow, I knew about inner joins, but just use the x.id=y.id out of old habit. This is the first time creating a report based on that didn't work for me.

Fornation, you're awesome, thanks!

 

[Pat Hartman]

The syntax of your original query is pre 1992 syntax and creates a cartesian product (which is by the way not updateable). Access uses the newer syntax of the specific join type. This is much more efficient especially with large joins.

 

[Linty Fresh]

You guys are gonna hate me . . .

. . . but I was tinkering with this thing, and now it won't work again.

DPT
DepartmentID(PK)
DepartmentName

ACT
ActivityID(PK)
ActivityName
ActivityDate
AdditionalComments
DepartmentID (FK to DPT.DepartmentID)

I created the query like this:
SELECT dpt.DepartmentName, act.ActivityName, act.ActivityDate, act.AdditionalComments
FROM dpt INNER JOIN act ON dpt.DepartmentID=act.DepartmentID;

And once again, this works fine when I run it from the query window.

Sigh . . . and once again, when I try to create a report based on this query, it freezes. What am I doing wrong this time?

 

[Fornatian]

Sounds like you db is corrupted. Try importing the entire contents into a new database and try again.

Does it do this on every report or just this one? Does it only do it under certain circumstances (i.e.if you build it from a query, or query with more than one table etc..)

Also try it on a different computer if you can - you never know - it might be the Access installation that's not worked!

Definitely doesn't sound like anything you've done wrong - keep the faith!

 

[Linty Fresh]

Thanks, fornation . . .

To answer your question, it only comes up when I try to do a report that incorporates both tables. It will do fine if I try to do an all-ACT report based on the query or an all-DPT report based on the query. I only get problems if I try to do a report with values from both tables.

I'll try your suggestions. Thanks again!

 

[Fornatian]

If you have a small dataset in A97 I can look at I can look at it for you and try and resolve your issue.

formatian@aol.com

put "Access World Forums" in the header because I delete anything and I mean ANYTHING I can't recognise - my fiancee has given me numerous b*ll*ckings for deleting her mail.

 

[Linty Fresh]

Thanks very much for the offer, fornation. Actually, I figured it out. It was XP. I reprogrammed the database on W98, and the report wizard worked fine. Then, just to make sure, I programmed it on W2K, and once again, no problems. I have to make this database on another machine, and then, I think, it'll run fine on XP. It's just the programming on XP that's a hassle.

Once again, thanks for your help, and I'm sure I'll be back with new concerns.