Access: Comparing Lists

[programmingmiss]

I have a list of numbers in a table w/ 10,000s of records and a want to mark a subset of those with a character designation or somehow query based on subset.

For example, field is social security No (type is number) and I have a subset of SS# I want to mark as R (retired) or D (deceased). Can reveal exact issue b/c classified.

New to using access beyond mailing list and all my work hinges on being able to first designate. I was thinking of a new field but to given size I couldn't manually enter a R or D.

Please help....one greatful and tired lady thanks you!

 

[plog]

I was thinking of a new field but to given size I couldn't manually enter a R or D.

So how are they going to be marked? A new field might be the proper way to go, unless you can determine the subset by the data itself.

Again, how are you going to identify those in the subset? Is there a rule? Like all odd numbers beginning with '27'? If not, its a manual job. If so, explain the rule.

 

[programmingmiss]

First, many thanks plog!

There is no pattern; I have an Excel file w/ a few thousand records and I want to run queries for that subset. They exactly match approximately 1/3 of records. I can go back and forth b/c ACCESS/EXCEL, IF it's is easy to sort/match that way, but I ultimately need to query in access.

 

[programmingmiss]

UR comment got me thinking, maybe I could export entire large table to excel and add two fields (column that match) data in field (think 3 rows of SS # w/ different labels) and run query where data element 1 = data element 2.

that would lead to a easier question; how to operationalize that concept in the query?

 

[plog]

I don't fully understand what you are trying to do, but this does sound like a job just for Access. If you can provide sample data--what you are starting with and then what you want to end up with, maybe I can help.

 

[programmingmiss]

Table has data element (ID number); type is number

egs of values 00002, 234432, 33222,4444, (for approximately 50,000 records)

of the 50,000 I need to flag approximately 10,000 (I have a list of numbers that match 00002, etc, but there is no particular order or pattern, just some meet the criteria and the only match is the ID number; those numbers are in Excel.

Does this clarify a bit...........hope so.....

 

[plog]

bring in your excel list into a table in access, bring both tables into a query, link on similar fields and the results will be the matching data.

From there you can add a field to your main table that will hold the type (R/D) and you can turn the above mentioned query into an UPDATE query to populate that new field in your main table.

 

[programmingmiss]

Plog: I understand where UR going. GREAT ADVICE!

I've imported the excel file into ACCESS (let's call it subset table), it has 1 data element, call it ID # that exactly matches the ID# (although slightly different name) in the Master Table and the Master Table has a new field (TYPE) to hold the R/D values. I made the ID# inthe subset the primary key.

Can U help w/ final part of query or suggest SQL statement to populate TYPE field.

Conceptually, logic is, where ID# from SubsetTable matches SS# from ID in MasterTable set Type = D (or R).

 

[plog]

First, with any action query (UPDATE, INSERT, DELETE) make a backup of the table being changed.

This is what I understand you want: If the SSN in Master matches ID # in subset the TYPE field in Master is D. If it's not in there, then it's R. If that's the case do this:

1. In design view of a query, bring in the Master table. Change it to an UPDATE query in the ribbon. Bring down the TYPE field and in the 'Update To:' field beneath it, type in: 'R'. Run that query (click the red exclamation point) and every TYPE should be R.

2. In that same query, bring in the subset table and link the two tables by the appropriate field. Beneath the TYPE field, change the 'R' to 'D' and run the query (click the red exclamation point) and it changes the matches to 'D'.

 

[Mihail]

Do you wish to switch from Excel to Access ?

I ask because seems to be a very simple task to accomplish this in Excel.
And I think that you do this once or very rarely.

 

[Mihail]

Remove the data from your Excel file(s) except the columns headers and 2 or three rows.
Replace sensitive data with false but realistic data if needed.

Maybe you can write some instructions in columns in order to explain what you wish to achieve.

Upload the Excel file (after you convert in 2007 or, better, 2003 version).
I'll return the file to you with the adequate macro.

 

[Mihail]

Ups. I've responded without to verify the OP identity. I'm almost sure that Vinc is a spam.
In order to ensure I wish to ask Vinc from where I am and at what date I am joined to AWF (see all this under my name).

 

[Pat Hartman]

field is social security No (type is number)

I hope not. SSN is a code and codes should be stored as text. SSN may have leading zeros and storing SSN as a number drops them. Same problem occurs with Zip which also may start with a zero.

 

[programmingmiss]

Thanks to all 3 of the responders; I just bought U a cup of virtual java ;-)

First, Pat, # is now type text, I learned that the hard way when I needed to sort :-(

Plog: I'm half way there. I ran and update query and was able to enter a N (for Null) in all TYPE fields in Master, but I can't seem to pull in the SUB-Table. I have created a separate query w/ links that can pull records from the MASTER TBL where #s b/t TBLs match, but it wants to replace ALL records. I can run that query the similar query that isn't an UPDATE query, and get the correct records from MASTER that match the SUB TBL.
THOUGHTS???

Thx! again to all.

 

[programmingmiss]

ACTUALLY, I GOT IT!!!!!! THX TO ALL AND Mihal, I didn't want to send files, b/c some the analyses are considered CBI; thx 4 offer.

NOW TO LEARN ABOUT APPEND QUERIES TO ADD VALUES IN THE SUB-TABLE.