Home > other >  Using "or" in my Sequel query crashes MS Access 2019
Using "or" in my Sequel query crashes MS Access 2019

Time:05-28

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.

CodePudding user response:

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.

  • Related