Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

MS Access 2003 query to select a record on multiple conditions

I need  a query to select Field1, Field2, Field3, Field4 and Field5 from every record in Table1 *except* records that meet the following criteria:
- Records in table1 that have a "0" (zero) in Field3, Field4 and Field5.
AND
- also have the first 4 numbers of a 6 digit number in Table1.Field2 match the first 4 numbers of Table2.Field1

Both points (conditions) must be met for the record to be excluded from query results.
0
Muskie12
Asked:
Muskie12
  • 3
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

select *
from tablex
where (field3=0 or field4=0 or field5=0) and left(field2,4)=left(field1,4)
0
 
Rey Obrero (Capricorn1)Commented:
if you aare not getting the correct results, post sample data and the expected records you want to see
0
 
MNelson831Commented:
2 tables Cap.

select Field1, Field2, Field3, Field4 and Field5 from Table1
where Field3 <> 0 and Field4 <> 0 and Field5 <> 0 and left(Field2,4) not in (Select left(Field1,4) From Table2)

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Rey Obrero (Capricorn1)Commented:
and I didn't see  *except* <lol>  
0
 
Muskie12Author Commented:
capricorn1 - I *thought* your query looked funny but figured it was a case of it being brilliant in its simiplicity so I was trying to experiment with it anyway...

MNelson831 - what do you mean "2 tables Cap."?    Also, I think since your query is selecting the records I want but using the logic of the records I want excluded, I *think* the "and"s in the first part of the WHERE need to be "or"s. I'm just playing with it now.
0
 
Muskie12Author Commented:
Thanks MNelson831, a couple of slight mods (repl 'and's with 'or's and remove the 'not' gave me the records I was looking for.

SELECT Table1.Field1
FROM COA
WHERE (((COA.JAN)<>0)) OR (((COA.FEB)<>0)) OR (((COA.MAR)<>0)) OR (((Left([Account],4)) In (Select left(Field1,4) From Table2)));
0
 
MNelson831Commented:
My bad... I read the question as wanting to exclude records that met ALL conitions rather than records that met ANY condition
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now