Query causes MSAccess to crash

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query causes MSAccess to crash

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2005-05-19 : 16:16:57

Sometimes when I run this query it causes Access to crash -- I get a message "MS Access has encountered a problem and must close. Sorry for the inconvenience" Anybody know what could be wrong with it ?? The tables and columns are all valid -- the query wont go into design mode, but is ok with SQL Mode. Sometimes the query runs fine -- other times it crashes

SELECT Sites.SiteNo, Monthly_MG.Month, Monthly_MG.MG AS MG, Sites.Type
FROM Sites LEFT JOIN Monthly_MG ON (Sites.SiteNo = Monthly_MG.SiteNo)
AND (((Monthly_MG.Month)=#1/1/2000#) AND ((Sites.SiteNo) Not In (13,201,202)))

Thanks

Pete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 16:36:22

You should be putting the 2nd and 3rd join conditions in the WHERE clause.

SELECT Sites.SiteNo, Monthly_MG.Month, Monthly_MG.MG AS MG, Sites.Type
FROM Sites
LEFT JOIN Monthly_MG ON Sites.SiteNo = Monthly_MG.SiteNo
WHERE Monthly_MG.Month)=#1/1/2000# AND Sites.SiteNo Not In (13,201,202)

Tara

kaus
Posting Yak Master

179 Posts

Posted - 2005-05-19 : 16:54:13

I've got more sites in the sites table than data in Monthly_MG. I'd like to pull all data from both tables -- when I use 'WHERE' instead of AND I get only matches -- inner join. Any way to work around this

Thanks

Pete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:03:11

I don't understand what you mean, but perhaps you need to use LEFT OUTER JOIN or FULL OUTER JOIN instead. Could you provide a data exmaple of what you want? The data example should illustrate exactly what your query is doing by showing us the data possibilities in the tables and the expected result set.

Tara

kaus
Posting Yak Master

179 Posts

Posted - 2005-05-19 : 17:19:21

My tables are:
Sites
SiteNo
SiteName
Type
etc..

Monthly_MG
SiteNo
MG
Month

Data
Sites
SiteNo
1
2
3
4
5

Data
Monthly_MG
SiteNo Month MG
1 1/1/2000 46
3 1/1/2000 52

I would like all the sites listed even if there is no data.

I've tried
SELECT Sites.SiteNo, Monthly_MG.Month, Monthly_MG.MG AS MG, Sites.Type
FROM Sites LEFT OUTER JOIN Monthly_MG ON Sites.SiteNo = Monthly_MG.SiteNo
WHERE (((Monthly_MG.Month)=#1/1/2000#) AND ((Sites.SiteNo) Not In (13,201,202)));

but I get only matches like an inner join if I try full outer join I get a syntax error

Thanks

Pete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:37:59

I'm not familiar with Access, but it seems to me your query is wrong here:

Monthly_MG.Month = #1/1/2000#

Shouldn't that be Monthly_MG.MG?

I converted this over to T-SQL and get the correct results. Have a look:



DECLARE @Site table (SiteNo int)
DECLARE @Monthly_MG table (SiteNo int, MG datetime, [Month] int)

INSERT INTO @Site VALUES(1)
INSERT INTO @Site VALUES(2)
INSERT INTO @Site VALUES(3)
INSERT INTO @Site VALUES(4)
INSERT INTO @Site VALUES(5)

INSERT INTO @Monthly_MG VALUES(1, '1/1/2000', 46)
INSERT INTO @Monthly_MG VALUES(3, '1/1/2000', 52)

SELECT s.SiteNo, m.MG, m.[Month]
FROM @Site s
LEFT OUTER JOIN @Monthly_MG m
ON s.SiteNo = m.SiteNo
WHERE s.SiteNo NOT IN (13,201,202) AND (m.MG = '1/1/2000' OR m.MG IS NULL)

Pretend that @Site and @Monthly_MG are your tables. Here is the result set I get:



SiteNo      MG                                                     Month       
----------- ------------------------------------------------------ ----------- 
1           2000-01-01 00:00:00.000                                46
2           NULL                                                   NULL
3           2000-01-01 00:00:00.000                                52
4           NULL                                                   NULL
5           NULL                                                   NULL

Tara

Tara

kaus
Posting Yak Master

179 Posts

Posted - 2005-05-19 : 17:50:00

Tara - the Monthly_MG.Month)=#1/1/2000# is correct. [Month] is a date. The MG is a number

I'd like to bring it into SQL Server -- Unfortunately the entire app is in Access -- I think I need the Full outer join but cant find the Syntax in Access -- Maybe I can make it work with UNION --

Thanks for your help

Pete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:52:58

quote:
Originally posted by kaus

Tara - the Monthly_MG.Month)=#1/1/2000# is correct. [Month] is a date. The MG is a number

That's not how you posted it:

Monthly_MG
SiteNo Month MG
1 1/1/2000 46
3 1/1/2000 52

So anyway, I think what you want is this:

SELECT Site.SiteNo, Monthly_MG.MG, Monthly_MG.[Month]
FROM Site
LEFT OUTER JOIN Monthly_MG
ON Site.SiteNo = Monthly_MG.SiteNo
WHERE Site.SiteNo NOT IN (13,201,202) AND (Monthly_MG.Month = #1/1/2000# OR Monthly_MG.Month IS NULL)

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 17:56:35

SELECT Site.SiteNo, Monthly_MG.MG, Monthly_MG.[Month]
FROM Site
LEFT OUTER JOIN Monthly_MG
ON Site.SiteNo = Monthly_MG.SiteNo AND Monthly_MG.Month = #1/1/2000#
WHERE Site.SiteNo NOT IN (13,201,202)

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 18:03:43

quote:
Originally posted by jsmith8858

SELECT Site.SiteNo, Monthly_MG.MG, Monthly_MG.[Month]
FROM Site
LEFT OUTER JOIN Monthly_MG
ON Site.SiteNo = Monthly_MG.SiteNo AND Monthly_MG.Month = #1/1/2000#
WHERE Site.SiteNo NOT IN (13,201,202)

Same query cost. Been doing too much fixing of orphaned data:

SELECT...
FROM Table1 t1
LEFT OUTER JOIN t2
ON ...
WHERE t2.Column1 IS NULL AND t2.Column2 IS NULL

Tara

kaus
Posting Yak Master

179 Posts

Posted - 2005-05-20 : 12:42:06

Thanks for your help Tara and Jeff -- I never could get the queries to work right in Access -- I imported the data into SQL Server and your queries work there -- It'll get what I need for now

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources