Solved

Access 2013

Posted on 2014-04-11
33
328 Views
Last Modified: 2014-04-14
I have an access table with 3,752,801 rows. There are 5 columns and I have set the first 4 of them up as combined primary keys, had to do this because of the duplicates error. Example:

Nursing Home     Worksheet     Line Number     Column     Words
1004282               A00000           100                    0             0100CAP REL COSTS-BLDGS
1004282               A00000            200                   0             0200CAP REL COSTS-MOVABLE
1004282               B10000            100                   400         ADMINIS
1004282               S200001           200                  200           FL

If the bottom row, as I have showed in the example, does not say KY in the words column I want to delete all information for the 1004282 Nursing home. I have researched and played with the queries until I am blue in the face. Please tell me there is a very simple way to do this.
0
Comment
Question by:alesha711
  • 18
  • 13
  • +1
33 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39995092
"bottom"?  How would you determine "bottom"?  Tables/queries are unordered sets so unless you can specify a sort order, there is no way to determine the sequence in which rows will be returned to you and it doesn't look like you could use the first four columns to sort the data so "bottom" is not possible.  Now, if you had assigned an autonumber PK when the rows were inserted and defined a unique index to enforce your business rules, you could be relatively certain that the last record imported for a set would have the highest autonumber and you could use that to determine "bottom" assuming the data had been pre-sorted by whatever application you got it from.

Would "any" work?  So if no row contained "KY" in the Words column, then all rows for the Nursing Home would be deleted?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39995115
try this, create a backup table first


delete *
from tableName
where [Nursing Home]="1004282" and Instr([Words],"KY")=0

or just to find the  two letters "KY" as a word

delete *
from tableName
where [Nursing Home]="1004282" and Instr(" " & [Words] & " "," KY ")=0
0
 

Author Comment

by:alesha711
ID: 39995132
Sorry this is kind of really hard to explain over text. Some background:

Every Nursing Home in the US has to file a Cost Report, we have Nursing Home clients and we do these reports for them. Each report has a worksheet page, a line number, and a column number that we input the information onto. I have found all of the US Nursing Homes Cost Reports and it came in an excel file like ^^^^. I had to transfer it to access because it was so big Excel would not hold it.  

My company wants a summary report if all of the KY Nursing Homes. There is the one file like above^^^ and there is also another file that is the NUMERIC file. It holds the total amount for the W/S, Line, and Colums. Pretty much the same file except there are numbers instead of Words in the 5th column. To combine both of these files together I need to make them smaller. Since I am only pulling KY Cost Report information I would like to Delete all other states out of this.
So when I said bottom I ment the last row in my example. Lets use my example
If S200001 W/S, Line 200, Column 200 says FL then I want to delete that nursing home out of my table. Which would be all rows with 1004282 in the Nursing Home Column.
0
 

Author Comment

by:alesha711
ID: 39995143
I have already created a query where I picked out all of the KY but it just gave me that specific row that says KY. I have a list with all the Nursing Home #'s that have KY, there are  531 of them, but instead of scrolling through 3,752,801 rows and manually deleting over 1,000,000 rows I was just hoping there was a simple way to sort and delete the extras....
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39995167
revised the query I posted

delete *
from tableName
where Instr([Words],"KY")=0
0
 

Author Comment

by:alesha711
ID: 39995171
I am sorry I am fairly new with access could you explain just a few more steps on how to accomplish this? make query and imput this code where?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39995189
you said you already created a query, right?

open a new query, select the name of the table
then select Delete query

Field: *                                    Instr([Words],"KY")
Table: NameofyourTable  

Criteria                                     0
0
 

Author Comment

by:alesha711
ID: 39995206
select Delete query is not an option there is just
Create: Query Wizard or Query Design: I pick Query Design, Then I click my table and theres no options after that. All That comes up is to enter into:
Field:  
Table:  
Sort:
Show:  
Criteria:    
Or:


I am so sorry I am so complicated! I have just tried to do this on my own and play with this for about 4 months now on and off and my brain just wants simple and easy...
0
 

Author Comment

by:alesha711
ID: 39995212
NeverMind I found it... UGH! cannot believe I have never seen this.... THANK YOU SO MUCH!!!
0
 

Author Comment

by:alesha711
ID: 39995222
When I try to input into Delete: where Instr([Words],"KY") it pops up  The text you entered isn't an item in the list.
The only selections I can have from the list are From and Where
and it will not let me select "where" unless I type it, which it does not accept and automatically goes to From after. It will let me select where in the next column over where I have nothing in field:, Table:, etc.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39995253
what is the name of your table?
0
 

Author Comment

by:alesha711
ID: 39995257
This is a nightmare and if you all quit responding I don't blame you... I have figured out why that query will not work... If I put Field: * the only option in Delete I can put is from.... If I put Field: Nursing Home the only option in Delete I can put is Where...
0
 

Author Comment

by:alesha711
ID: 39995259
Copy of Snf10_2012_ALPHA
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 39995281
see this image

x
0
 
LVL 57
ID: 39995946
<<Please tell me there is a very simple way to do this. >>

When you get something like this, always try and break it down.  So without a lot of SQL, what you want to do is first create a list of the nursing homes you want to delete.

What's not clear is if your looking for "KY" in the words column only for specific rows or not, so I'm not going to give exact steps, but what you want to do is:

1. Create a query that selects the rows you need, and put in a check with Instr() as Ray indicated.  The only output field you should have is the nursing home.

2. Execute that and check it.  If you have more then one row for a nursing home, then make it a SELECT DISTINCT by setting the queries unique values property to yes in the property page.

3. Change the query type to a make table query.  Doesn't matter what you call the new table.   Something like _TempNursingHomeList  is fine (I always use a underscore as a prefix to indicate temp objects that are not part of my app, so I know I can delete them later without issue).

4. Now start a new query.  Include your table that you want to delete from and include the _TempNursingHomeList.  Create a join between the two on nursing home.  Now change it to a delete query and of course "Delete from" your original table.

 There are more elegant ways to do this, but for an ad-hoc, one time only processes (which I am assuming this is), it's often easier to do it in steps like this.  

 If this will be a recurring process, then we'll come up with something better.

As Ray said, make a backup of the DB in case anything goes wrong and you need to start over.

Once your set, just delete the temp table.

Jim.
0
 

Author Comment

by:alesha711
ID: 39996108
I did the query Rey said to do... it deleted every row...

Ky is in specific rows, KY is always in the  S200001 W/S, Line 200, Column 200.
There are 531 KY Nursing Homes, Each Nursing home has about 350 rows of data, but that varies because if there is no data in a line/column then they did not put a row.
So about 185,850 rows have KY data, all of this is ordered asending by the Nursing Home #, therefore KY is kind of anywhere within this data.

Also this will be a once a year project for me...

Also how do I create a query to select rows I need?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39996290
it will help, if we can see the content of the table.
so,

create a copy of the db.

delete all objects except the table "Snf10_2012_ALPHA"

zipped the db

upload the zipped db..
0
 

Author Comment

by:alesha711
ID: 39996298
Here is the file...
snf10-2012-ALPHA-copy.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39996313
@alesha711

we can't see the table if they are linked..

can you just upload the .CSV file
0
 

Author Comment

by:alesha711
ID: 39996340
Sorry! the CSV file was taking way too long because its so big so does this work?
snf10-2012-ALPHA-copy.accdb
0
 

Author Comment

by:alesha711
ID: 39996350
I still think I did that wrong... What about this? if not I will download the CSV file.
snf10-2012-ALPHA-copy.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39996382
still a linked table..
just upload the .csv file
0
 

Author Comment

by:alesha711
ID: 39996430
here is the CSV file.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39996435
where?
0
 

Author Comment

by:alesha711
ID: 39996439
It won't download the CSV it takes to long and then stops downloading... here is the website where I downloaded it.

http://www.cms.gov/Research-Statistics-Data-and-Systems/Files-for-Order/CostReports/Cost-Reports-by-Fiscal-Year-Items/SNF10-DL-2012.html?DLPage=1&DLSort=0&DLSortDir=ascending

 Just click on the SNF 10 FY 2012 DATA FILES load that and then it is snf10_2012_ALPHA.CSV file.

Sorry I could not upload!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39996471
see this sample db

there is a table tblWith_KY in this db, created by running a make table query "Query1"


copy the query to your db and run..
snf10-2012-ALPHA-copy.accdb
0
 

Author Comment

by:alesha711
ID: 39998991
I have done this.... Each of the KY has that first column with the Nursing Home Number, I need to now pick out all information that has those Nursing Home Numbers. There is 531 Nursing Homes that are in KY, I really need to find a simple way to do this.

That Nursing home Number is there ID number, I have a list of the ID's I need, there are just so many how can I get them in an easy way???
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39999037
<I need to now pick out all information that has those Nursing Home Numbers.>

where are all these information located / stored ?
0
 

Author Comment

by:alesha711
ID: 39999044
I did a query where I picked out KY in the word column and deleted the duplicates. So I have a list of the ones with KY and there Nursing Home Numbers.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39999064
<I did a query where I picked out KY in the word column and deleted the duplicates>

actually you could have done this with a Make table query, like what i did on the sample db.

<So I have a list of the ones with KY and there Nursing Home Numbers. >

so, now what is the problem?
0
 

Author Comment

by:alesha711
ID: 39999107
Each Nursing home has about 300 rows of data... the different worksheets, line numbers, ect.... I need to have a table with only the nursing homes that a from KY. there are 531 Nursing Homes that are in KY according to the query I made. I need to pick out those nursing homes from the big db that has all nursing homes in it. For ex:
the list I have with KY (from the query) is as shown
1012587
1017014
1024763
1027418
1027516
ect.....
How can I pull out all data with these Nursing Homes.
I know I can do a select query I have done it with a few but I really do not want to enter 531 numbers manually... I tried to copy and paste and I can't do that... is there a quicker easier way?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39999149
you need to create a query using the tblWith_KY and your big table with a join in field [Nursing Homes] from tblWith_KY and the corresponding field from the big table.
0
 

Author Comment

by:alesha711
ID: 39999313
IT WORKED!!!! Thank you so MUCH!!! I am so sorry this was so confusing... its been a complicated process and a long one! thank you so much!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Keep your audience engaged and get the most out of your next presentation with these quick Prezi tips.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now