Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2019, 05:06 AM   #1
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 31
Thanks: 1
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Query records with same ID Number

Hello,

I have a table with a 9 digit SSN field. This number is followed by one or two additional characters (letters or numbers).

It may happen that same records have the same 9 digits and will need to create a query that returns all those records with just the same first 9 digits. Query source will be a textbox on a form.

Example:

Records:

000000000A
000000000B
000000000C
111111111A
123456789A

If in the textbox, the criteria is 000000000, then the query should return:

000000000A
000000000B
000000000C

Is there a way, I can achieve this?

Thank you for any help.

JPR is offline   Reply With Quote
Old 08-12-2019, 05:31 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,060
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Query records with same ID Number

Try a query
Code:
SELECT Table9SSN.MSSN
FROM Table9SSN
WHERE (((Left([MSSN],9))="000000000"))
GROUP BY Table9SSN.MSSN;
make sure this is the result you need,
then work with this syntax and your form's text box.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-12-2019, 05:40 AM   #3
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 31
Thanks: 1
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Re: Query records with same ID Number

Resolved. Just changed a few lines and got what I was looking for. Will share. Thank you


SELECT mytable.[ssn], First(Left([ssn],9)) AS Expr1
FROM mytable
GROUP BY mytable.[ssn]
HAVING (((First(Left([ssn],9))) Like [forms]![myform]![ssnsearch]));

JPR is offline   Reply With Quote
Old 08-12-2019, 08:35 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,509 Times in 2,409 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query records with same ID Number

you don't need the Left([ssn], 9) there, if you modify your criteria as:

Like [Forms]![myForm]![ssnsearch] & "*"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-12-2019, 12:01 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,930
Thanks: 13
Thanked 1,527 Times in 1,453 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query records with same ID Number

All in all, it is far better to never mush multiple pieces of data in a single field. You don't save anything. All you do is add complexity. Best solution is to separate SSN and the trailing character into two fields. You can use a compound unique index if you need to. Indexes can contain up to 10 fields. You have to build compound indexes using the indexes dialog. You cannot build them using design view on the table.

And finally, SSN should NEVER, EVER be used as a primary key. There are privacy lasw that prevent that. Hope you are not violating them.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-12-2019, 09:51 PM   #6
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 31
Thanks: 1
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Re: Query records with same ID Number

Thank you for your advice.
Don't worry. No violation.

JPR is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append query with x-y number of records Andrlimo Queries 7 01-09-2017 09:22 AM
[SOLVED] Query to count number of different records mafhobb Queries 7 05-28-2014 11:26 AM
Getting number of records returned by query oni29 Modules & VBA 1 04-22-2007 11:48 PM
Number of Records in a query frankbutcher Queries 2 12-20-2005 12:47 PM
Query - retrieve a set number of records j0se Queries 2 11-04-2005 03:20 AM




All times are GMT -8. The time now is 02:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World