Changing Format of Retreived Data

**alcharbonneau** · 10-13-2012, 07:07 AM

Good Morning

I have managed, by trial and error, to create a query (SQL below) that retrieves the information needed. However, I have been unable to output the data in 1 record per PROVIDER NUMBER (PRVDR_NUM) table with line 01800 and 01900 displayed in separate fields on the same record.

I would appreciate your suggestions for (1) keeping all individual provider information on one record and (2) other suggestions you might have on the overall query.

SELECT Hosp_1996_ALPHAa.RPT_REC_NUM, Hosp_1996_RPTa.PRVDR_NUM, Hosp_1996_NMRCa.WKSHT_CD, Hosp_1996_NMRCa.LINE_NUM, Hosp_1996_NMRCa.CLMN_NUM, Hosp_1996_NMRCa.ITM_VAL_NUM, Hosp_1996_ALPHAa.WKSHT_CD, Hosp_1996_ALPHAa.LINE_NUM, Hosp_1996_ALPHAa.CLMN_NUM, Hosp_1996_ALPHAa.ALPHNMRC_ITM_TXT
FROM (Hosp_1996_RPTa INNER JOIN Hosp_1996_NMRCa ON Hosp_1996_RPTa.RPT_REC_NUM = Hosp_1996_NMRCa.RPT_REC_NUM) INNER JOIN Hosp_1996_ALPHAa ON Hosp_1996_NMRCa.RPT_REC_NUM = Hosp_1996_ALPHAa.RPT_REC_NUM
WHERE (((Hosp_1996_NMRCa.WKSHT_CD)="S200000") AND ((Hosp_1996_NMRCa.LINE_NUM)="01800" Or (Hosp_1996_NMRCa.LINE_NUM)="01900") AND ((Hosp_1996_NMRCa.CLMN_NUM)="0100") AND ((Hosp_1996_ALPHAa.WKSHT_CD)="S200000") AND ((Hosp_1996_ALPHAa.LINE_NUM)="00200") AND ((Hosp_1996_ALPHAa.CLMN_NUM)="0100"));

Thank you for taking the time to read and consider this request.

Al Charbonneau

**JBeaucaire** · 10-13-2012, 01:46 PM

You provide examples of the output you get (a few rows is fine) and the output you would prefer (the same few rows)?

**alcharbonneau** · 10-13-2012, 08:00 PM

Hi

Thanks for responding. I have copied and pasted the table that I got from the statements above:

RPT_REC_NUM PRVDR_NUM Hosp_1996_NMRCa.WKSHT_CD Hosp_1996_NMRCa.LINE_NUM Hosp_1996_NMRCa.CLMN_NUM ITM_VAL_NUM Hosp_1996_ALPHAa.WKSHT_CD Hosp_1996_ALPHAa.LINE_NUM Hosp_1996_ALPHAa.CLMN_NUM ALPHNMRC_ITM_TXT

7 010004 S200000 01900 0100 1 S200000 00200 0100 NORTH JACKSON HOSPITAL
7 010004 S200000 01800 0100 9 S200000 00200 0100 NORTH JACKSON HOSPITAL
13 010005 S200000 01800 0100 9 S200000 00200 0100 BOAZ-ALBERTVILLE MEDICAL CENTER
13 010005 S200000 01900 0100 1 S200000 00200 0100 BOAZ-ALBERTVILLE MEDICAL CENTER

I would like all the information for the hospitals in the table to be contained in one record, one line for each hospital. As you will note, the two hospitals in the excerpt use two records, lines.

I hope that the above example is what you requested. The original table has 5000+ lines. As an Access newbie, I was unable to reduce it to several lines as requested. I could, however, forward the table in Excel.

Thanks again for responding.

Al Charbonneau

**JBeaucaire** · 10-13-2012, 11:05 PM

I thought I was going to be able to suggest some amendment, but I don't really know how to get all the information retained for a single hospital in a single row.

I can do it in Excel, but not Access.

If you want an Excel "after-the-fact" solution, then post a workbook showing a BEFORE sheet (the query result you currently get) and an AFTER sheet (how you want the final output to look, exactly).

**alansidman** · 10-14-2012, 12:54 AM

Al
I'm having a hard time distinguishing the data and the appropriate field names. Can you post a table with the data (only a few records) so that I can attempt to solve your issue without recreating a new db. It is easier to work with your data than to build a new db. I am thinking you may need to have a self join on the table, but cannot be sure until I test it.

Forums traditionally do not display data efficiently hence the issues.

**alcharbonneau** · 10-14-2012, 01:03 PM

Hi

Here is the Excel version.

Thanks for taking the time to help.

Al

**alcharbonneau** · 10-14-2012, 01:06 PM

Hi

I would love to take advantage of your offer to look at a shorter version of my original Access file. I had some trouble this morning generating a shorter version but I have now succeeded. However, my newbieness kicked in and I have been unable to save the Access example in a format that I can attach to a message. I have to leave to drive my daughter to New Haven CT so I will play with it when I get back later on this evening.

Is there a simple way to save this output to the desktop or somewhere else?

Thanks again.

Al

**swhitesell** · 11-02-2012, 06:12 PM

If you only have the two criteria 01800 and 01900, here may be a solution for you.

SELECT Hosp_1996_ALPHAa.RPT_REC_NUM, Hosp_1996_ALPHAa.PRVDR_NUM, Hosp_1996_ALPHAa.WKSHT_CD, Sum(IIf([LINE_NUM]="01800",[ITM_VAL_NUM],0)) AS LINE_NUM_01800, Sum(IIf([LINE_NUM]="01900",[ITM_VAL_NUM],0)) AS LINE_NUM_01900, Hosp_1996_ALPHAa.APLHNMRC_ITM_TXT
FROM Hosp_1996_ALPHAa
GROUP BY Hosp_1996_ALPHAa.RPT_REC_NUM, Hosp_1996_ALPHAa.PRVDR_NUM, Hosp_1996_ALPHAa.WKSHT_CD, Hosp_1996_ALPHAa.APLHNMRC_ITM_TXT;

This SQL doesn't include the total criteria you selected, nor your limitations. However, you can use this as a stepping point to get to what you wanted. What I did was create two columns named LINE_NUM_01800 and LINE_NUM_01900 which uses IIF statement to pull the information and sums it to return the data you want.