Query using multiple criteria on one field

[tiarynn]

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:

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
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

[puppydogbuddy]

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

[tiarynn]

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?

[JConsulting]

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)

[tiarynn]

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!

[JConsulting]

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

[tiarynn]

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

[JConsulting]

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

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
FROM [Security Database]
WHERE [Security Database].ssn IN(select ssn from ssn)
ORDER BY [Security Database].LastName;
 

[tiarynn]

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?

[JConsulting]

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

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
FROM [Security Database] Right Join SSN on [Security Database].SSN = SSN.SSN
ORDER BY [Security Database].LastName;
 

[tiarynn]

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

[JConsulting]

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?

[tiarynn]

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

[JConsulting]

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

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
FROM [Security Database] Right Join SSN on replace([Security Database].SSN,"-","") = replace(SSN.SSN,"-","")
ORDER BY [Security Database].LastName;
 

[tiarynn]

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?

[JConsulting]

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?

[tiarynn]

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