Any MS Access gurus out there?

Factual Questions
1

Help me out, pleeeeeze!! I’m having problems creating a report base on a query.

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?

Free cheesecake to the person who gets me through this, the next time you come to Boston.

2

It’s possible the name of your query is causing the problem. I would recreate your query, using the wizard, and give it a different name.

I must confess, I have Windows 2000, so am not up on XP. Sometimes, though, there’s a glitch in a file, with no logical reason, and when I recreate it, it works fine.

3

Why would you not use a query like this:

SELECT DPT.DPT_NAME, ACT.ACT_NAME
FROM DPT INNER JOIN ACT ON DPT.DPT_ID = ACT.DPT_ID;

?

4

I dont see how its the sql in the query thats causing the problem. The sql in the OP and the inner join posted by Mangetout are the same.

5

Are they the same?
They probably return the same record set.

But I’m not too sure how the database engine works - without the join, is it possible that the query might be retrieving the cartesian result, then(as a result of the WHERE clause) throwing away all the records where DPT_ID doesn’t match?

6

Your SQL is fine. If the results show up ok when you double click the query qxSelAll, then all is well with that.

All I can suggest are the following things:

  1. Try creating the report in design view and select qxSelAll as the Record Source.

  2. If that doesn’t work (ie. it still freezes), try pasting your SQL as the recordsource.

  3. If that still doesn’t work, check your data carefully to make sure that there are no funny entries like spaces instead of nulls. Eliminate nulls if possible. I can’t really see how this would make a difference though.

  4. Try the whole thing with only a few records in the tables but the same structures. Then add your data in a few records at a time, trying the report out after each add.

  5. Try the SQL mangetout has used or build the query using the builder (same thing). Again, I can’t see this would make a diff.

  6. If all those don’t work, try reinstalling access.

7

All depends how Access does its optimisations mangetout. It’s quite possible that Access recognises that it is a join and behaves accordingly. It’s also quite possible that I’m talking out of my arse.
Either way Minty Fresh should change his sql to a join. Wont help him with his problem but at least we’ll both sleep easier tonight.

8

A-a-a-a-and, mangetout wins the cheesecake.

Replacing the existing syntax with inner join worked. I don’t know why. Every time I’ve used the “where x.id=y.id” previously, it’s worked. Then again, this is the first DB I’ve programmed in Access 2000 (I’ve been out of the loop for awhile), and the first time I’ve programmed it on XP (The worst OS I’ve ever used, btw. I’d rather work on W95!!).

Still, if it works, it works. Thanks, mangetout, and thanks to everyone else who wrote in.