By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,649 Members | 696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,649 IT Pros & Developers. It's quick & easy.

Query using multiple criteria on one field

P: 9
Hi everyone,

I am a beginning to intermediate user with Access 2000 on XP.

I am trying to create a query from a table of more than 1300 records. I need to look up individuals by their SSN only which is the primary key for the table) and I need to look up several at a time. Also, I will be looking up different individuals every time I run this query.

This is the SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Security Database].LastName, [Security Database].FirstName, [Security Database].SSN, [Security Database].DOB, [Security Database].POB, [Security Database].JobTitle, [Security Database].ClearanceLevel, [Security Database].DateGranted
  2. FROM [Security Database];
How I can I simply enter the required SSN's (which can be anywhere from 1 to 50 different ones at a time) under the criteria?

Please let me know in layman's terms since I am still learning.

I really, REALLY appreciate this and thank you in advance for ANY guidance....

Lisa
May 11 '07 #1
Share this Question
Share on Google+
16 Replies


puppydogbuddy
Expert 100+
P: 1,923
You need to create a search form that can handle multiple criteria. Fortunately, there is an example of one at the following link that you can download. The link contains detailed instructions, and hopefully be able to adapt to your application.

http://allenbrowne.com/ser-62.html
May 11 '07 #2

P: 9
I checked out your link and it's more complicated than i can understand yet. I'm not looking up a range of SSN's but specific SSN's. Isn't there a way to put a parameter in that field Criteria stating to [Enter SSN] and then enter "123456789 and 987654321 and 555121212..." etc... for as many people as I need to find?
May 11 '07 #3

JConsulting
Expert 100+
P: 603
I checked out your link and it's more complicated than i can understand yet. I'm not looking up a range of SSN's but specific SSN's. Isn't there a way to put a parameter in that field Criteria stating to [Enter SSN] and then enter "123456789 and 987654321 and 555121212..." etc... for as many people as I need to find?
a quick easy way would be to create a table to put your ssn's

then in your query, you can use it

select * from table1 where ssn IN(Select ssn from ssntable)
May 11 '07 #4

P: 9
Thank you! I didn't exactly do what you told me but I created a table for the SSN's and used that in a relationship with my full database for a query that I generate my report from.

By linking the SSN's for each table in that query I thought it would pull up only those SSN's that are listed SSN table. And it does but now there are blank places in my report; like the query isn't pulling over all of the information I requested from the database even though it's there and queried...

Just when I thought it was done... ARGH!
May 11 '07 #5

JConsulting
Expert 100+
P: 603
Thank you! I didn't exactly do what you told me but I created a table for the SSN's and used that in a relationship with my full database for a query that I generate my report from.

By linking the SSN's for each table in that query I thought it would pull up only those SSN's that are listed SSN table. And it does but now there are blank places in my report; like the query isn't pulling over all of the information I requested from the database even though it's there and queried...

Just when I thought it was done... ARGH!
can you post your SQL for your query? we'll get you sorted out.
J
May 11 '07 #6

P: 9
SELECT [Security Database].LastName, [Security Database].FirstName, SSN.SSN, [Security Database].DOB, [Security Database].POB, [Security Database].JobTitle, [Security Database].ClearanceLevel, [Security Database].DateGranted
FROM [Security Database] INNER JOIN SSN ON [Security Database].SSN = SSN.SSN
ORDER BY [Security Database].LastName;


Thank you. I am actually leaving for the day and won't be able to respond until Monday.

You can see that I am trying to use the numbers from the SSN to pull the rest of the needed information from the database. I know the fields are filled in the database, so I don't know why the query is not populating fully. Some records do and some don't...

Thank you again.

Lisa
May 11 '07 #7

JConsulting
Expert 100+
P: 603
SELECT [Security Database].LastName, [Security Database].FirstName, SSN.SSN, [Security Database].DOB, [Security Database].POB, [Security Database].JobTitle, [Security Database].ClearanceLevel, [Security Database].DateGranted
FROM [Security Database] INNER JOIN SSN ON [Security Database].SSN = SSN.SSN
ORDER BY [Security Database].LastName;


Thank you. I am actually leaving for the day and won't be able to respond until Monday.

You can see that I am trying to use the numbers from the SSN to pull the rest of the needed information from the database. I know the fields are filled in the database, so I don't know why the query is not populating fully. Some records do and some don't...

Thank you again.

Lisa
Give this a shot
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Security Database].LastName, 
  3. [Security Database].FirstName, 
  4. [Security Database].SSN, 
  5. [Security Database].DOB, 
  6. [Security Database].POB, 
  7. [Security Database].JobTitle, 
  8. [Security Database].ClearanceLevel, 
  9. [Security Database].DateGranted
  10. FROM [Security Database]
  11. WHERE [Security Database].ssn IN(select ssn from ssn)
  12. ORDER BY [Security Database].LastName;
  13.  
May 11 '07 #8

P: 9
Thank you. I did try it and it's still only pulling a few of the SSN's. I can't figure it out. The fields match, the SSN's are correct (I pulled them fro the regular DB). It's very random... I mean, it keeps showing the same 3 people but I can see no reason it would choose these 3 over the rest I had asked for... I double checked my input, making sure there were no typos, and everything meets up.

I just don't get it... COuld it be the primary key? If I delete some records and add new ones (SSN's in SSN, that is), would that cause a conflict?
May 14 '07 #9

JConsulting
Expert 100+
P: 603
Thank you. I did try it and it's still only pulling a few of the SSN's. I can't figure it out. The fields match, the SSN's are correct (I pulled them fro the regular DB). It's very random... I mean, it keeps showing the same 3 people but I can see no reason it would choose these 3 over the rest I had asked for... I double checked my input, making sure there were no typos, and everything meets up.

I just don't get it... COuld it be the primary key? If I delete some records and add new ones (SSN's in SSN, that is), would that cause a conflict?
ok, do it this way

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Security Database].LastName, 
  3. [Security Database].FirstName, 
  4. [Security Database].SSN, 
  5. [Security Database].DOB, 
  6. [Security Database].POB, 
  7. [Security Database].JobTitle, 
  8. [Security Database].ClearanceLevel, 
  9. [Security Database].DateGranted
  10. FROM [Security Database] Right Join SSN on [Security Database].SSN = SSN.SSN
  11. ORDER BY [Security Database].LastName;
  12.  
May 14 '07 #10

P: 9
Nope... no go. Same 3 personnel only now with 8 empty records above them. I checked to see if perhaps I entered the numbers wrong. I looked in design view to see if the field info matched. And it all does....

I'm really sorry. I VERY much appreciate your help. Not sure what to do at this point other than delete all the queries and recreate them. Maybe there is a conflict I can't see somewhere. I'd like to send the table to you but it contains sensitive information. So I am limited in my way of obtaining help.

Lisa
May 14 '07 #11

JConsulting
Expert 100+
P: 603
Nope... no go. Same 3 personnel only now with 8 empty records above them. I checked to see if perhaps I entered the numbers wrong. I looked in design view to see if the field info matched. And it all does....

I'm really sorry. I VERY much appreciate your help. Not sure what to do at this point other than delete all the queries and recreate them. Maybe there is a conflict I can't see somewhere. I'd like to send the table to you but it contains sensitive information. So I am limited in my way of obtaining help.

Lisa
Hi Lisa,
What that SQL did was pull everything from the new table you made...the one you put SSNs into that you want to pull from your larger table. I have to ask at this point, since we're linking via the SSN itself...is there some kind of formatting difference. Are you masking the field in some way in your large table? You say the field is your Primary Index...is the field text or numeric? are there dashes in these? 123-22-2134 like that?
May 14 '07 #12

P: 9
For tblSSN, the SSN is NOT the primary key - I am using autonumber for this table. The field is formatted to text, field size is 11 characters, the mask is 000\-00\-0000;0;_ and the caption is SSN.

for tblSecurity Database, the SSN IS the primary key and everything else formatted the same, text, 11 characters, caption, and input mask.

I double checked to make I entered the SSN's correctly on tblSSN and I did.

Also, if I haven't been clear. The only field in tbl SSN are the SSN's...

But since I now know what I need to do, I am am about to start over from scratch with fresh clean tables and queries and reports!

But I'll wait ot hear back from you! ;-p

Thank you again. You have been very helpful and wonderful in holding my hand through this!

Lisa
May 14 '07 #13

JConsulting
Expert 100+
P: 603
For tblSSN, the SSN is NOT the primary key - I am using autonumber for this table. The field is formatted to text, field size is 11 characters, the mask is 000\-00\-0000;0;_ and the caption is SSN.

for tblSecurity Database, the SSN IS the primary key and everything else formatted the same, text, 11 characters, caption, and input mask.

I double checked to make I entered the SSN's correctly on tblSSN and I did.

Also, if I haven't been clear. The only field in tbl SSN are the SSN's...

But since I now know what I need to do, I am am about to start over from scratch with fresh clean tables and queries and reports!

But I'll wait ot hear back from you! ;-p

Thank you again. You have been very helpful and wonderful in holding my hand through this!

Lisa
give this a try. It removes the - characters before it links. So we'll see

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Security Database].LastName, 
  3. [Security Database].FirstName, 
  4. [Security Database].SSN, 
  5. [Security Database].DOB, 
  6. [Security Database].POB, 
  7. [Security Database].JobTitle, 
  8. [Security Database].ClearanceLevel, 
  9. [Security Database].DateGranted
  10. FROM [Security Database] Right Join SSN on replace([Security Database].SSN,"-","") = replace(SSN.SSN,"-","")
  11. ORDER BY [Security Database].LastName;
  12.  
May 14 '07 #14

P: 9
Woo Hoo! Making progress! It pulled 9 out of 12 listed with 3 blank record at the top of the answer table.

I created that mask so I could just enter 123456789 and it would appear 123-45-6789. Is that the issue?
May 14 '07 #15

JConsulting
Expert 100+
P: 603
Woo Hoo! Making progress! It pulled 9 out of 12 listed with 3 blank record at the top of the answer table.

I created that mask so I could just enter 123456789 and it would appear 123-45-6789. Is that the issue?
Seems so. I am not sure why you have blank lines though, unless you have blanks in your SSN table. There may also be null SSNs in your big table too...so check for that. If there are, you probably want to ignore those?
May 14 '07 #16

P: 9
I'll have to check that out further and let you know.

But in the mean time... THANK YOU!!!!! You have been a tremendous help! I will let you know what happens!

You rock!

Lisa
May 14 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.