string comparison in query

[mpearce]

Yes, that is great thanks, I have no intention of "removing" the records, just grouping them in to a Report based on what you have just told me.
I will try using IIF() statements first, but if that proves too difficult due to the number of Disability groupings I will use a Query with a VBA Function to assign the code of OB, Disability or TPL to each record.
Then it will just be a case of Grouping them.

That is an interesting approach, i wasnt sure how to do it that way

What about the First rows of the Summary, the Hospital etc, where do they come from?

I havent really thought about a way to do this. I was manually typing it in when doing it in excel.

I fogot to ask, what format is the original data in when you want to Import it and what sort of naming convention do the files have.
The while thing might as well be automated if possible.

These files are coming from different hospitals which use different conventions. The files also have different column headings because they are from different facilities. I have done 3 of these so far and with each one the column headings and filenames have been different.

[OBP]

Mike, OK, I will need examples of those Headings with how you interpret them.
I did some work for Violette LePhan about 3 years ago where she could open the Excel File, get the headings and match them up to her table, that would be an Ideal solution to this, but I am not sure if I can remember how I did it. Unfortunatley I no longer have the original Database.
If you don't have too many variations I could just build a Query for each one.
I will make a start using your Excel Example, it might take me a little while as I am working on a few other Databases at the same time.

[mpearce]

what i can do is provide the column headings i have. This is a new service that my company will be providing so i assume this will become widespread and i dont know how much the column headings will differ from the ones i have.

I could take the headings that are in there now and just map the ones in the file i receive to match the ones in the table and then import the data after that

So far these files and been excel files but i am not sure what all hospitals will end up using for a file format


Here are the column headings so far:
1. Patient Name, Account No, Birth Date, SS #, Address1, Address 2, City, ST, Zip, Service, Diagnosis, Charges

2. patno, pt name, adm date, tot chg amt, fc, hosp svc, acct bal, clasf icda code, usr text, ins1 plan

3. (this is used in the sample database i attached earlier)

[OBP]

Mike, what are the names that you will want to use and most important of all which ones contain the DX data?
What about Admin DX ?

[mpearce]

Mike, what are the names that you will want to use and most important of all which ones contain the DX data?
What about Admin DX ?

Here are the column headings so far:
1. Patient Name, Account No, Birth Date, SS #, Address1, Address 2, City, ST, Zip, Service, Diagnosis (this would be the only DX field), Charges

2. patno, pt name, adm date, tot chg amt, fc, hosp svc, acct bal, clasf icda code (this would be the only dx field), usr text, ins1 plan

3. (this is used in the sample database i attached earlier)

it is hard to predict what other column headings will come. It will be more common that the files will contain more than 1 dx field. I was trying to write a standard that i could map fields to but i never really completed cause i wanted to first focus on logic and making sure the application would function properly, then i was going to focus on summarizing and exporting and finally making the import work in a universal so that it didnt matter what the original file contained in terms of column headings.

[OBP]

Mike, I have looked at your 3 queries and they are good, so I am going to use something very simliar to update a "PriorityCode" field which once set can then be used for the Grouping in your Summary.

What data do you need to store "Long Term", all the data Imported?
Just the data relevant to the Summary?
It may be too difficult to create a "Global" Import that will work with any File structure, unless I can remember how to recreate my old VBA code for Mixing and Matching Headings.
It might be better to have a routine "Setup" for each version of Import for now.

[mpearce]

Mike, I have looked at your 3 queries and they are good, so I am going to use something very simliar to update a "PriorityCode" field which once set can then be used for the Grouping in your Summary.

What data do you need to store "Long Term", all the data Imported?
Just the data relevant to the Summary?
It may be too difficult to create a "Global" Import that will work with any File structure, unless I can remember how to recreate my old VBA code for Mixing and Matching Headings.
It might be better to have a routine "Setup" for each version of Import for now.

Long term storage of data is something I havent really thought about. I dont know that it will be necessary. Up to this point the data has beem needed again one the analysis has been done.

I can probably just map the fields in the current file to those in the database. I cant really think of another way to do it.

[OBP]

Mike I will post soemthing tomorrow, I have my Son and Grandchildren coming in half an hour, so I can't get anything done.

[mpearce]

That is fine

[OBP]

Mike, I have imported your wah Excel data as Table Sheet1 and worked with that, with one field PriorityCode added to it for use with the summary.
I have also added a PriorityCodeSort table used to sort OB, Disabililty & TPL in the summary.
I have modified your Under19 Query and called it Under 19 1.
I have added a new Form called Summary that just has a Create & View Summary button on it.
When you click the button it runs the
OB2 Query
DX1 Disability Query
DX2 Disability Query
DX3 Disability Query
DX4 Disability Query
DX5 Disability Query
DX6 Disability Query
TPL1 Query
And then opens the Summary Report
I have kept it simple for now so that you have something to work with while I try and work out my old Import & match Headings VBA.
This technique can be used for the other 2 Formatted Files but would be much simpler as there would only be one DX field to worry about.
I haven't bothered with your Total Cost queries as they are OK anyway and can just be added to the Summary Report.

Let me know if you want to continue down this route or would liek to try something else.

[mpearce]

OBP,

This looks good so far. How tricky would it be, in the report, to show a list of the matching the queries and then on the next line after the query results have a count on the number of accounts and a sum on charges for those accounts? So the user can each account and not just a basic summary.

The way it is now the summary is available to the user as well as list(s) if accounts that make up that summary.

[OBP]

Mike, yes that should be OK, I will have a go when I get back to it, I am working on 2 other databases today.

[OBP]

Mike what fields from the Table do you want in the report, there are rather a lot of them, too many for a normal report?

[mpearce]

Would it be possible to only show the fields that contain data?

[OBP]

Mike, that requires some very intense VBA coding and hardly warrants the effort if you don't need to see the data.
Have a look at this version, I have just used a few fields, you could certainly add quite a few more, like the patient details etc. You can have a play around with it by adding some and changing fonts etc as I haven't bothered formatting it.
The new report is called Alternative Report.