Criteria to Modify Query

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

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

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

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

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

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

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

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

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

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

Hi

Thank you for responding.

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

Look forward to your comments.

Al

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

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

Good Morning

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

Thanks again.

Al