Using "or" in my SQL query crashes MS Access 2019

Question

This is my first question on Stack. I am a student trying to develop my querying abilities through practise. Using Microsoft Access and some publicly available census data (5 tables of 57,000 rows) I came across the following issue.

When I try to use "or" instead of "and" in a "where" statement, it causes Access to stop responding. Why is this happening?

For example, when I try changing:

not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 and not EDUCATION.INFANTS_PRIMARY_TOT_P = 0

to this:

not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 or not EDUCATION.INFANTS_PRIMARY_TOT_P = 0

or even to this:

not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 or EDUCATION.INFANTS_PRIMARY_TOT_P = 0

It causes a crash.

Is this because my syntax is wrong? Or have I misunderstood the application of the "or" clause in Sequel?

My full code without the use of "or" is as follows.

(
  select distinct top 10 
      CHILDREN.PK as top10Last10, 
      CHILDREN.TOTAL_TOTAL as children, 
      HOUSEHOLD.TOTAL_FAMHHOLD as familys, 
      FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM as undivorced, 
      MARRIAGE.P_TOT_MARRD_REG_MARRGE as officiated, 
      EDUCATION.INFANTS_PRIMARY_TOT_P as primaryKto6
  from 
      CHILDREN, 
      HOUSEHOLD, 
      FAMILY, 
      MARRIAGE, 
      EDUCATION
  where 
      CHILDREN.PK = HOUSEHOLD.PK and 
      CHILDREN.PK = FAMILY.PK and 
      CHILDREN.PK = MARRIAGE.PK and 
      CHILDREN.PK = EDUCATION.PK and 
      not CHILDREN.TOTAL_TOTAL = 0 and 
      not HOUSEHOLD.TOTAL_FAMHHOLD = 0 and
      not FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM = 0 and 
      not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 and 
      not EDUCATION.INFANTS_PRIMARY_TOT_P = 0
  order by 
      CHILDREN.TOTAL_TOTAL asc, 
      HOUSEHOLD.TOTAL_FAMHHOLD asc, 
      FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM asc, 
      MARRIAGE.P_TOT_MARRD_REG_MARRGE asc, 
      EDUCATION.INFANTS_PRIMARY_TOT_P asc
) 
 union all
(
  select distinct top 10 
      CHILDREN.PK, 
      CHILDREN.TOTAL_TOTAL, 
      HOUSEHOLD.TOTAL_FAMHHOLD, 
      FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM, 
      MARRIAGE.P_TOT_MARRD_REG_MARRGE, 
      EDUCATION.INFANTS_PRIMARY_TOT_P
  from 
      CHILDREN, 
      HOUSEHOLD, 
      FAMILY, 
      MARRIAGE, 
      EDUCATION
  where 
      CHILDREN.PK = HOUSEHOLD.PK and 
      CHILDREN.PK = FAMILY.PK and 
      CHILDREN.PK = MARRIAGE.PK and 
      CHILDREN.PK = EDUCATION.PK and 
      not CHILDREN.TOTAL_TOTAL = 0 and
      not HOUSEHOLD.TOTAL_FAMHHOLD = 0 and
      not FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM = 0 and
      not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 and
      not EDUCATION.INFANTS_PRIMARY_TOT_P = 0
  order by 
      CHILDREN.TOTAL_TOTAL desc, 
      HOUSEHOLD.TOTAL_FAMHHOLD desc, 
      FAMILY.INTCT_FAM_NO_OTR_CHLD_PRE_FAM desc, 
      MARRIAGE.P_TOT_MARRD_REG_MARRGE desc, 
      EDUCATION.INFANTS_PRIMARY_TOT_P desc
);

This full code is functioning and returns the table I expected it to.

Thank you in advance for any responses.

score 0 · Accepted Answer · answered May 28 '22 at 07:05

0

First, not responding is not a crash.

Next, you probably need to be more specific:

(not MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0) or (not EDUCATION.INFANTS_PRIMARY_TOT_P = 0)

or:

not (MARRIAGE.P_TOT_MARRD_REG_MARRGE = 0 or EDUCATION.INFANTS_PRIMARY_TOT_P = 0)

Also, make sure you have indices on these fields.

answered May 28 '22 at 07:05

Gustav

53,498
7
29
55

1

Thank you for the response! The first solution you proposed did not work, and it ceased responding (is that the right phrasing?). However, the second solution did function as expected and I have implemented it into my query. What would "having indicies on these fields" look like for my solution? And what advantage does this have? Thanks again. – May 28 '22 at 07:48

Using "or" in my SQL query crashes MS Access 2019

1 Answers1