#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Criteria to Modify Query

## alcharbonneau

Good Morning

I have been using Access 2010 to link to and retrieve data from government tables data for over 5,000 organizations.  I  then export to  Excel 2007 to analyze the data.  Several Access Tables and Databases contributors taught me how to use criteria in a query to eliminate some of the work in Excel.  For example, the tables that I am working with provides the opportunity for each organization to input 100 lines of numbers in two columns representing salary expenses and other expenses (line 95 is a subtotal) followed by line 101, a total.  As you will note from the SQL below, I used a Not 09500 criterion which allowed me to sum 1:100 checking to if it equals 101.  

SELECT Hosp_2009_NMRCa.RPT_REC_NUM, Hosp_2009_RPTa.PRVDR_NUM, Hosp_2009_NMRCa.WKSHT_CD, Hosp_2009_NMRCa.LINE_NUM, Hosp_2009_NMRCa.CLMN_NUM, Hosp_2009_NMRCa.ITM_VAL_NUM
FROM ([2009 rpt_num 4957] INNER JOIN Hosp_2009_RPTa ON [2009 rpt_num 4957].[2009 RPT_REC_NUM 4957] = Hosp_2009_RPTa.RPT_REC_NUM) INNER JOIN Hosp_2009_NMRCa ON Hosp_2009_RPTa.RPT_REC_NUM = Hosp_2009_NMRCa.RPT_REC_NUM
WHERE (((Hosp_2009_NMRCa.WKSHT_CD)="A000000") AND (Not (Hosp_2009_NMRCa.LINE_NUM)="09500") AND ((Hosp_2009_NMRCa.CLMN_NUM)="0100" Or (Hosp_2009_NMRCa.CLMN_NUM)="0200"));

Now I am wondering can the query design be modified to do the following: (1) eliminate line 95; (2) print out column 1 and 2 and a total (3) check total for each organization sum(1:100=101); and (4) return only those organizations that sum(1:100=101).

Thank you for reading and considering this request.

Al Charbonneau

----------


## alansidman

Al, this is difficult to visualize and determine without playing with some sample data.  Are you able to upload a sample so that we can attempt to make this happen.  It may take a couple of queries and I would prefer not to re-create and populate a new db when you have the data.

----------


## alcharbonneau

Great to hear from you.  I understand but my "newbieness" is getting in the way of responding to your request.  I have not been able to save an Access 2010 query so that it can be uploaded to the website.  Can you advise or will an Excel version serve the same purpose?

Thanks again for responding.

Al

----------


## alansidman

Run a compact and repair on your database.  Zip your database and then click on the Go Advanced button on the site.  Click on manage attachments.  Follow the wizard.

Alan

----------


## alcharbonneau

I am feeling as dumb as a fence post (I need to say I have a Master's Degree from an Ivy League School) but after compacting and repairing the file, I have spent the rest of the day googling how to zip an Access file.   I right click on the file in the navigation pane and there is no send to ZIP option.  

Obviously,  I am missing something or a lot of things.

Al

----------


## alansidman

Do you have a copy of WinZip or JZip on your computer?  If not, you may want to check with your technology department and see if they have it available for you.  There are some free Zip programs available on the internet.

Here is a link to one.  http://www.jzip.com/

----------


## alcharbonneau

Thanks for responding.

I downloaded a zip program this morning.  I also ran a query, which is visible in the navigation pane under queries,  to upload to the website so that you could swhat I am trying to accomplish.  I ran the compact and repair function, as you suggested, but have been stuck with getting the query into zip format.    I can right clickon it in access and it gives me numerous options (ex, export in various formats) but no send to zip.  

I am a 99% retired 70 year old-believe me, I wouldn't be bugging you if I had a technology dept.  

What am I missing with respect to the zip.

Thank you for your help.

Al

----------


## alansidman

ok Al;
No issues or worries.  You need to upload the whole db, not just the query.  I guess that was the misunderstanding.  So, with the db closed, open windows explorer (not internet explorer) and right click on the file name.  It should give you the option to zip the file.  Once that is done, click on the advance button on this website thread and follow that wizard.

BTW--I am 68 and not a techie.  It just comes in time.  Stick with it and you will be a guru in no time.  I didn't start working with Access until 5 years ago and now I am doing projects for money (also retired.)

We all start somewhere and it is tough being a beginner, but that is what this forum is here for.  We'll get you where you need to be and in the interim you will learn.

Alan

----------


## alcharbonneau

Good Evening

Thanks for the words of encouragement and guidance through the maze.  I uploaded the zip file, sure feels good when it works.  Here is what I would like to accomplish: (1) keep <> 09500 criteria (2) add a total column for columns 0100 & 0200 (3) check that ITM_VAL_NUM sum(1:100)=101 for each company (4) report only those companies where sum(1:100)=101.

Well thanks again for your encouragement.  Look forward to discussing the file.

Al

----------


## alansidman

Okay, I got the db, but I was unable to see your tables as they were linked and not imported to the db.  As such I am unable to assist on this as I cannot see any data to determine if what you want can be done.  The QBE is not visible because the tables cannot be accessed.  Perhaps you can make a copy of this data base with tables that have data within the database versus linked.  Just need a few records with sample data that reflects what is in the linked tables.  Alternatively, you could guide me to where the tables are located and I could link to them if that were possible.  Are they available to the public? on the internet? or are these proprietary?

----------


## alcharbonneau

Hi

I think I have been able to import and zip two files from the original data source.

When you have a chance, give it a look and let me know.

Thank you

Al

----------


## alansidman

Al
I am not sure exactly how you want this to happen.  I took a chance and ran an aggregate query.  Look at it and see if this is what you are looking for or if we need to tweak it.

Alan

----------


## alcharbonneau

Hi

Thanks for taking up the challenge.  It may be a good place to start since I haven't explained my goals very clearly.  So let me start from the beginning.  I have used Access to retrieve the data in the format used in the Excel Forum Example that I sent along yesterday: long column of line numbers running from 00100 to 01000 with a total on line 10100.  I have then exported the table to Excel where I have (1) summed the column 1 and column 2 data for each line and (2) ran a tests to see if sum(00100:01000)=101000.  I manually eliminated those organizations that failed the test.  The problem with this approach is that, in the case of large files with 350K+ lines, Excel really chugs so I thought I would try to use criteria in Access to return the data for each line submitted by each organization that passed the sum(00100:01000)=10100 test.  So it seems to me that the criteria would have to (1) eliminate 09500 because it is a subtotal (2) sum column 1 and column 2 for each line submitted by each organization (2) run the test, that is sum 00100 to 10000 to see if it equals the sum of column one and column 2 for each organization and (3) report out lines 00100-10100 for only those organizations that passed the test.

I hope this offers more detail on what I would like to accomplish.

Thanks again.

Al

----------


## alansidman

Al;
I'm not getting it.  I don't see any column 1 or column 2 in your db.  The only value that is a number is the field that can be summed is ITM_Val_Num.  Try to explain using the field names and what you are trying to do with each field.  Your description is very confusing.

----------


## alcharbonneau

Hi

Your confusion is  understandable - I was thinking in Excel.  Unbelievable!

Let me try to reorganize and send you another file with the detail that you requested.

Thanks for your patience.

Al

----------


## alcharbonneau

Hi

Spent the weekend on and off trying to clearly state what I am looking for.  This morning I decided to use the zip file, Example 2, that you sent to me.  Bingo, even though I don't understand your use of grouping, I was able to modified  Example 2 (now called Example 3) so that it, I hope, more clearly illustrates what I am trying to accomplish.  Please open Access Forum Example3, query example3.  As you will note from the design view, I expanded the range of lines retrieved for each provider by using "Between 00100 and 10100" coupled with "Not 09500" which is a subtotal line.  Now my last challenge is to see if Access will retrieve only those providers with  line numbers  00100 to 10099 Itm_Val_Num that when summed  = the Itm_Val_Num of 10100, column 1 + column2.  Please refer to the query:  when summed individually, the  column 1 & column 2 Itm_Val_Num for line totals (00300-09802) for provider number 010016 =31313137 which is = to the Itm_Val_Num of line 10100 column 1 + column 2.  Is it possible to add an Access criterion that will screen out (not retrieve) those providers with Itm_Val_Num for line numbers  00100 to 10099 that do not equal the Itm_Val_Num for the sum of line 10100 column 1+ line 10100 column 2?

I hope this helps.

I really appreciate your willingness to help.

Al

----------


## alcharbonneau

Hi

Just and additional question: is it possible to have Access calculate a total of the Itm_Val_Num for the column 1 and column 2 values for each line?

Thanks again for taking the time to listen and help.

Al

----------


## alansidman

Al--
tied up with a project so it will be a day or two until I have time to spend on this.  But rest assured I will look at it unless you or someone else solves the issue first.

----------


## alcharbonneau

Hi

Thank you for responding.

In the mean time, I will keep working on the query.

Look forward to your comments.

Al

----------


## alansidman

Al; I am really getting very confused and mixed up when I try to understand and recreate what you are looking for.  Please provide me with a smaller sample of data that you can provide the expected results for.  Perhaps only one or two providers with a limited number of records for each.  Perhaps four or five.  In that manner, I can re-engineer backwards.  Right now, there is to much data to sort through and it is not effective.  Simply make your example as clear as possible.  Also, please don't refer to column numbers as that makes no sense to me.  Use the field names.  I don't know what you are referring to when you say column1 or column2.

Alan

----------


## alcharbonneau

Hi

Just go in from a short trip up the East Coast - running from Hurricane Sandy.

Thanks for your continued interest.  Let me send you something tomorrow afternoon.

Thanks

Al

----------


## alansidman

Stay warm, high and dry.  Keeping a close watch on this storm as I have one daughter in Brooklyn and the other in Quincy, MA

Alan

----------


## alcharbonneau

Hi

I trust that your daughters have been minimally inconvenienced by Sandy.  Was this their "first" hurricane?  I also have a daughter living in the NY City area (Queens) and a son in White Plains.  In RI, we were very lucky - got our power back this afternoon and we did not have to evacuate throughout the storm although there is always a point during a hurricane where I think "maybe we should have..."  

I have reworked the example hopefully in the manner your prescribed.  The attached file represents the data that two providers submitted for Work Sheet A.  The problem is that Work Sheet A can have 80+ records per provider and you requested 4 or 5 records per provider.   At any rate, I would like to accomplish the following: (1) create a total field for ITM_VAL_NUM for each LINE_NUM.  As you will note, there are a maximum of two ITM_VAL_NUMs per LINE_NUM.  For example, PRVDR_NUM 250253 shows for LINE_NUM 0600, 2 ITM_VAL_NUMs 37468 & 14950.  I would like to see a total field for ITM_VAL_NUM per provider.  (2) I would like to sum the ITM_VAL_NUM for each provider to see if the sum of the ITM_VAL_NUM for LINE_NUMs 00300-09800=sum ITM_VAL_NUMs for LINE_NUM 10100.

I hope that I have presented this in a clear, understandable manner.

Thanks for your patience.

Al

----------


## alansidman

Al;
I followed your instructions and I hope I have provided you with what you are looking for.  If this is what you need, you should be able to copy the queries into your db and make them work with the larger universe.

Alan

----------


## alcharbonneau

Good Morning

Thank you for your solutions.  With any luck, I can take it from here.

Thanks again.

Al

----------

