# Any MS Access gurus out there?
**URL:** https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372
**Category:** Factual Questions
**Created:** [June 25, 2003, 9:31pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372 "2003-06-25T21:31:59Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Linty\_Fresh](https://boards.straightdope.com/u/Linty_Fresh)
#### Post date: [June 25, 2003, 9:31pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/1 "2003-06-25T21:31:59Z")
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.
---
### Author:  [@Suisaidh](https://boards.straightdope.com/u/Suisaidh)
#### Post date: [June 25, 2003, 10:13pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/2 "2003-06-25T22:13:43Z")
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.
---
### Author:  [@Mangetout](https://boards.straightdope.com/u/Mangetout)
#### Post date: [June 25, 2003, 10:18pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/3 "2003-06-25T22:18:39Z")
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;
?
---
### Author:  [@bryanmaguire](https://boards.straightdope.com/u/bryanmaguire)
#### Post date: [June 25, 2003, 10:33pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/4 "2003-06-25T22:33:02Z")
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.
---
### Author:  [@Mangetout](https://boards.straightdope.com/u/Mangetout)
#### Post date: [June 25, 2003, 10:42pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/5 "2003-06-25T22:42:57Z")
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?
---
### Author:  [@Johnny\_B.Goode](https://boards.straightdope.com/u/Johnny_B.Goode)
#### Post date: [June 25, 2003, 11:35pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/6 "2003-06-25T23:35:59Z")
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.
---
### Author:  [@bryanmaguire](https://boards.straightdope.com/u/bryanmaguire)
#### Post date: [June 25, 2003, 11:57pm UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/7 "2003-06-25T23:57:04Z")
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.
---
### Author:  [@Linty\_Fresh](https://boards.straightdope.com/u/Linty_Fresh)
#### Post date: [June 26, 2003, 1:49am UTC](https://boards.straightdope.com/t/any-ms-access-gurus-out-there/184372/8 "2003-06-26T01:49:31Z")
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.