string comparison in query

[mpearce]

I have a diagnosis code field in a database with a datatype of text. some of the entries are just numbers and some start with a letter and all other characters are numbers.

I have categories that have ranges of codes that fit into them. for example one of my categories is all diagnosis codes that fall between 630.xx and 679.xx where xx could be any number. another would be 249.xx to 249.xx or 250.xx to 250.xx, or 295.xx to 300.xx, or E800.xx to E999.xx, there are a lot more than that but i wont burden you with all of them. I would like some help in wiriting queries using these ranges in a where clause.

Is it possible to use <, >, <=, >= with strings in a query? Or is there a better way to do this?

[OBP]

I think the best way would bt use the Like function or the Between function.
The like function
Like "249." & "*"
or
Like "*" & "249." & "*"
would provide all values starting with 249.
The Between function might work with Text so
Between "630" and "680"
would give any values between those 2.
If it doesn't work with text you might have to convert the text to a Value where possible.

Can you provide a database or excel sheet with some examples of those ranges to do some testing with?

[mpearce]

The between function does indeed work with text and that seems to have done the trick. however this leaves me with a very long query that i would ultimately like to store as the rowsource for a listbox. Here it is:

"SELECT WAH.* FROM WAH WHERE (((WAH.[Final DX1]) Between '42' And '42.99')) OR (((WAH.[Final DX1]) Between '140' And '239.99')) OR (((WAH.[Final DX1]) Between '249' And '249.99')) OR (((WAH.[Final DX1]) Between '250' And '250.99')) OR (((WAH.[Final DX1]) Between '277.30' And '277.39')) OR (((WAH.[Final DX1]) Between '278.01' And '278.01')) OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) OR (((WAH.[Final DX1]) Between '295' And '300.99')) OR (((WAH.[Final DX1]) Between '318' And '319')) OR (((WAH.[Final DX1]) Between '330' And '337.99')) OR (((WAH.[Final DX1]) Between '340' And '349.99')) OR (((WAH.[Final DX1]) Between '357.00' And '357.99')) OR (((WAH.[Final DX1]) Between '359.00' And '359.99')) OR (((WAH.[Final DX1]) Between '369.40' And '369.49')) OR (((WAH.[Final DX1]) Between '389.00' And '389.03')) OR (((WAH.[Final DX1]) Between '389.70' And '389.79')) OR (((WAH.[Final DX1]) Between '403' And '404.99')) OR (((WAH.[Final DX1]) Between '410' And '414.99')) OR (((WAH.[Final DX1]) Between '416.80' An
d '416.99')) OR (((WAH.[Final DX1]) Between '428' And '428.99')) OR (((WAH.[Final DX1]) Between '443' And '443.99')) OR (((WAH.[Final DX1]) Between '446' And '446.99')) OR (((WAH.[Final DX1]) Between '491.20' And '491.29')) OR (((WAH.[Final DX1]) Between '492' And '492.99')) OR (((WAH.[Final DX1]) Between '493.20' And '493.29')) OR (((WAH.[Final DX1]) Between '501' And '501.99')) OR (((WAH.[Final DX1]) Between '505' And '505.99')) OR (((WAH.[Final DX1]) Between '514' And '514.99')) OR (((WAH.[Final DX1]) Between '519' And '519.99')) OR (((WAH.[Final DX1]) Between '571' And '571.99')) OR (((WAH.[Final DX1]) Between '581' And '581.99')) OR (((WAH.[Final DX1]) Between '585' And '585.99')) OR (((WAH.[Final DX1]) Between '586' And '586.99')) OR (((WAH.[Final DX1]) Between '710' And '710.99')) OR (((WAH.[Final DX1]) Between '714' And '715.99')) OR (((WAH.[Final DX1]) Between '722' And '722.99')) OR (((WAH.[Final DX1]) Between '724' And '724.99')) OR (((WAH.[Final DX1]) Between '737' And '737.99')) OR (((WAH.[Final
DX1]) Between '741' And '742.99')) OR (((WAH.[Final DX1]) Between '784' And '784.99')) OR (((WAH.[Final DX1]) Between '997.60' And '997.69')) OR (((WAH.[Final DX1]) Between 'v21.30' And 'v21.39')) OR (((WAH.[Final DX1]) Between 'v42' And 'v42.99')) OR (((WAH.[Final DX1]) Between 'v45.10' And 'v45.19')) OR (((WAH.[Final DX1]) Between 'v53.80' And 'v53.89')) OR (((WAH.[Final DX1]) Between 'v62.84' And 'v62.84')) ORDER BY WAH.[Final DX1];"

Obviously that uses multiple lines in the VB editor but i cant figure out how to add separators so that it will treated as one line even though it is multiple lines. any ideas?

Also i need to use the where clause as criteria for a dsum and dcount statement. Unless there is a better way to sum a charge field meeting the where clause and then count the number of results.

[CreganTur]

Obviously that uses multiple lines in the VB editor but i cant figure out how to add separators so that it will treated as one line even though it is multiple lines. any ideas?


You'll need to put ending quotation marks at the end of the line, then use the underscore character to tell VBA you're moving to a new line. You'll need to use an anpersand to concatenate the multiple strings... like this:

"SELECT WAH.* FROM WAH WHERE (((WAH.[Final DX1]) Between " _
& "'42' And '42.99')) OR (((WAH.[Final DX1]) Between '140' And '239.99')) " _
& "OR (((WAH.[Final DX1]) Between '249' And '249.99')) OR (((WAH.[Final " _
& "DX1]) Between '250' And '250.99')) OR (((WAH.[Final DX1]) Between " _
& "'277.30' And '277.39')) OR (((WAH.[Final DX1]) Between '278.01' And " _
& "'278.01')) OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) OR " _
& "(((WAH.[Final DX1]) Between '295' And '300.99')) OR (((WAH.[Final " _
& "DX1]) Between '318' And '319')) OR (((WAH.[Final DX1]) Between '330' " _
& "And '337.99')) OR (((WAH.[Final DX1]) Between '340' And '349.99')) OR " _
& "(((WAH.[Final DX1]) Between '357.00' And '357.99')) OR (((WAH.[Final " _
& " ,etc."

obviously, you'll probably want to make your splits in places that make sense, instead of randomly like I did above.

HTH:thumb

[mpearce]

You'll need to put ending quotation marks at the end of the line, then use the underscore character to tell VBA you're moving to a new line. You'll need to use an anpersand to concatenate the multiple strings

That works to a point, but i eventually get "too many line continuations"

[OBP]

Can I ask why you are doing it in VBA rather than having a standalone Query?

[mpearce]

Can I ask why you are doing it in VBA rather than having a standalone Query?

I need the results in a listbox and i thought VBA was the only way to set the rowsource of a listbox with a query. I mean i know you can set the row source in the properties pane of the listbox.

Basically i would like to have that query run and populate a listbox with the results.

If there is a way to do that with a standalone query, then i would love to know how that is done.

[CreganTur]

Just set the list box's row source to the name of the query you created. And make sure the row source type is table/query.

[OBP]

You might need to requery it if data is changed.

[mpearce]

I have it working now thanks a lot guys.

how can i have a query display only columns that contain data? I know that i can choose the columns in the query and accomplish it that way. basically i will be populating certain fields with data, the fields that i use may be different from time to time. This is all dependent on what the the client gives me to analyze.

Do this make sense?

[CreganTur]

how can i have a query display only columns that contain data? I know that i can choose the columns in the query and accomplish it that way. basically i will be populating certain fields with data, the fields that i use may be different from time to time. This is all dependent on what the the client gives me to analyze.


If I understand your question correctly, you can accomplsh this by putting a test to see if the field is null in the WHERE clause. Something like:
WHERE TableName.FieldName IS NOT NULL

HTH:thumb

[OBP]

I would leave all of the fields in the Query and display the results on a form, where you can make the Fields visible or not depending on whether or not there is data. (But not in Datasheet or Continuous Forms mode).
Randy's Criteria will filter out Any Records where that field has no data, not just the field.
You could use your original VBA SQL code to write a QueryDef and only change it when required, you can even use a Field selection process for including the Fields or not.
There has been a Thread on here previously about Interactive SQL.

[mpearce]

i think some of you have hit the nail on this.

but to further explain here is what i am thinking

something like:

if (dcount(field 1) > 0) or (dcount(field 2) > 0) ... (dcount(field n) > 0) then

'add to select query

else

'do not include in select query


I'd like to have this occur in a way where i can just write the code and not have to tweak it. Sort of a set it and forget it type thing. I could do this with a where clause but then i think i would have to tweak the query each time i load data into the table.

[OBP]

You would have to run the Query with all fields and then have the VBA modify it on the Fly and run it again in the modified version.
Is it worth it, for not seeing some empty Fields?

[mpearce]

It would mainly be for aesthetics i suppose, maybe for readability if i were to export the results to excel or some other form. I probably have about 60 fields in the table. I stretched the listbox across the entire width of the form even with it that wide only 13 of the fields are visible and there may be data in the fields that are not visible.

another thought:

how can i set the column width for all columns on a listbox to auto? I know i can adjust the width column by column and take a guess at a number, but i was hoping there was a better way to do that

[OBP]

Sorry, I can't help, I never use List boxes.

[Norie]

Why do you have 60 fields?

Having that no of fields, some of which may be empty, points towards the design of your tables needing re-structured.

As for constructing your SQL string try this.

strSQL = "SELECT WAH.* FROM WAH "
strSQL = strSQL & "WHERE (((WAH.[Final DX1]) Between '42' And '42.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '140' And '239.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '249' And '249.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '250' And '250.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '277.30' And '277.39')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '278.01' And '278.01')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '295' And '300.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '318' And '319')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '330' And '337.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '340' And '349.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '357.00' And '357.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '359.00' And '359.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '369.40' And '369.49')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '389.00' And '389.03')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '389.70' And '389.79')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '403' And '404.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '410' And '414.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '416.80'And '416.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '428' And '428.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '443' And '443.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '446' And '446.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '491.20' And '491.29')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '492' And '492.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '493.20' And '493.29')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '501' And '501.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '505' And '505.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '514' And '514.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '519' And '519.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '571' And '571.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '581' And '581.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '585' And '585.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '586' And '586.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '710' And '710.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '714' And '715.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '722' And '722.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '724' And '724.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '737' And '737.99')) "
strSQL = strSQL & "OR (((WAH.[FinalDX1]) Between '741' And '742.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '784' And '784.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '997.60' And '997.69')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v21.30' And 'v21.39')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v42' And 'v42.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v45.10' And 'v45.19')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v53.80' And 'v53.89')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v62.84' And 'v62.84')) "
strSQL = strSQL & "ORDER BY WAH.[Final DX1];"

I would also be careful using Between with text values, it could cause problems similar to those when you try and sort data with text and numbers.

[mpearce]

Why do you have 60 fields?

Some of the files i have analyze contain that many fields

[mpearce]

I have an idea in my head I am just not sure how to apply it. So here goes:

I have a file that I am importing into access that contains medical data. It can have up to 6 diagnosis codes. I have 3 categories of codes and i need to search the table for those codes. A code can appear in any one of the 6 diagnosis code fields. I have priorities for the categories of diagnosis codes. Top priority is called OB, 2nd to that is Disability, 3rd to that is TPL.

I need a way to catch all OB codes from the 6 fields. What i was doing before was by hand i was sorting by diag code 1 and then doing a cut and paste on the data i need, and then deleting the records i just pasted from the original table. If the record contains a OB and a disability code this needs to be pulled as a part of the OB search because OB takes priority.

So i need to be removing records as a go along so that a record isnt placed somewhere twice.

So i was thinking of having a make table query handle OB codes appearing in the diag code 1 field, but then how would i delete those results from the original table? In addition to the make table i would then have append queries that would handle the rest of the diag codes. Again question being how can i remove the results of each query from the original table?

Does this make sense? Any help would be greatly appreciated.

[Norie]

I think you need to have a bit of a rethink on the whole thing.

For a start why not create a table with all the possible diagnosis codes and their categories?

Then you could use that in your query(s) when you wanted to extract data for a particular category.

The next thing you should look into is restructuring the data.

Having even 6 fields that are basically the same for one record isn't the best way to go about things.

I don't know what type of data you are dealing with but I think you probably need to separate the diagnosis codes out into another table.

You can link the original table and the new table by creating a primary key in the first and using it as a foreign key in the 2nd.

This probably all sounds like a lot of work, but believe me it would be worth it in the long run.

If you can post some sample data, then I could perhaps illustrate what I'm suggesting.

PS Obviously don't post 'real' data, just post something that is representative.

[OBP]

I agree with Norie, that rather than working with the data in the format that it comes in with I would prepare Tables to proper Relational database design.
Then use the imported data table as Temporary data, distribute it to the prepared tables and then delete the records.

[mpearce]

I sort of get what you guys are talking about. I have attached some fake data so that you all can see what i am working with.

Let me give some ranges too:

OB (Priority 1):

630.xx-679.xx and v20.xx-v29.xx
Disability (Priority 2):

42-42.99
140-239.99
249-249.99
250-250.99
277.30-277.39
278.01-278.01
282.62-282.69
295-300.99
318-319
330-337.99
340-349.99
357.00-357.99
359.00-359.99
369.40-369.49
389.00-389.03
389.70-389.79
403-404.99
410-414.99
416.80-416.99
428-428.99
443-443.99
446-446.99
491.20-491.29
492-492.99
493.20-493.29
501-501.99
505-505.99
514-514.99
519-519.99
571-571.99
581-581.99
585-585.99
586-586.99
710-710.99
714-715.99
722-722.99
724-724.99
737-737.99
741-742.99
784-784.99
997.60-997.69
v21.30-v21.39
v42-v42.99
v45.10-v45.19
v53.80-v53.89
v62.84-v62.84
TPL (Priority 3):

800.xx-999.xx
E800.xx-E999.xx
Again OB takes priority over anything, no matter what final dx field it appears in, then the same with Disability as well as TPL.

Your suggestions are appreciated.

[mpearce]

any thoughts on this?

[OBP]

Mike, so you are talking about Columns AB to AH in your Example data, and does it matter where they Occur, i.e. if they appear in AB does that take preference to AH or the other way around?

[mpearce]

yes AB through AH would be correct. It doesnt matter where the code appears it could be any one of those six.

[OBP]

Have you created a table(s) for this data to go into yet?

[mpearce]

at this point i have a single table

[OBP]

So what goes where in your table, or does it all go in there and all you need to do is add your Code.

[mpearce]

right now its just one table with come code behind it. All the data is in that one table

[OBP]

So can you explain what you want to do with "Priority" that you showed in post #22?

[mpearce]

basically when i do this manually I sort the data by final dx1 and look for all OB codes. Then i sort by final dx2 and look for all OB codes, followed by the remaining final dx codes.

Then once i have all OB codes i move onto Disability. I sort by final dx1 again and then look for all disability codes. Then i sort by final dx2 and look for all disability codes. followed by all remaining final dx's.

the process is repeated for TPL as well.

With each pass through the data the matching records are removed and put into holding tables. After each pass the original data becomes smaller and smaller as records are removed.

Once i have passed over all final dx code columns the holding tables are then exported to excel and ditributed by email.

with 6 final dx fields priority needs to be established. there could be some overlap in the records. Maybe initially the person was diagnosed with something (disability code in final dx1, as an example) but later on it was found to be something else (OB code final dx3, as an example). Even though an OB code exists in final dx3 and would be found on the third manual pass it would be more important than the disability code in final dx1 and would therefore take "priority" over the disability code in final dx1.

Hopefully this logic makes sense.
Thanks

[OBP]

What do you do with the Access Tables once they have been distributed as Excel Sheets?
Why Excel sheets rather than have the users work in the Access Database?

[OBP]

Can you post a zipped Access 2003 database with an Empty copy of your table in it?

[mpearce]

excel sheets were used for ease of user interaction. I suppose I could have all this in access. I really just need to import the raw data then run the analysis and then provide the user with results which would include an over all summary of the analysis. This summary would include the number of accounts that falls under each category as well as the sum of charges of the accounts in each category.

Attached is the empty database, I havent set up the temp tables yet. Attached also is a template for the summary i have been using.

[OBP]

So if you could produce an Access Report that does exactly the same as your "Summary" Example would that be the ultimate goal?
In your Summary, where do the first rows Hospital Name etc come form?

Does any one DX take precedence over the others, i.e. If it is OB at DX1 does that beat something else at DX2,3,4,5, or 6.
From your description it sounds like DX1 beats Dx2 and DX2 beats DX3 etc.
Or is it the later numbers in the DX sequence that are more important, i.e. OB found at DX1 changes to disability at DX6 does the overall outcome become disability or OB?

[OBP]

With the disability, what about values between the ones you have quoted i.e.
42-42.99
140-239.99
What about 100 or 120, are they possible values?
If so what do you do with them?

[mpearce]

With the disability, what about values between the ones you have quoted i.e.
42-42.99
140-239.99
What about 100 or 120, are they possible values?
If so what do you do with them?


if a value occurs that is not in the list of codes, i ignore it. as for the intervals i.e. 42-42.99 or 140-239.99 i need it to grab things like 42.01 or 42.58 but not 43.02. same thing with 140-239.99 i would need 140 or 140.56 basically 140.xx where xx could be any number 01-99. so that could be 230.54 or 239.0 but not 240.02.

So again any value that doesnt fall under the list of values is ignored.

[OBP]

Can you answer the previous question as well please?

[mpearce]

So if you could produce an Access Report that does exactly the same as your "Summary" Example would that be the ultimate goal?
In your Summary, where do the first rows Hospital Name etc come form?

Does any one DX take precedence over the others, i.e. If it is OB at DX1 does that beat something else at DX2,3,4,5, or 6.
From your description it sounds like DX1 beats Dx2 and DX2 beats DX3 etc.
Or is it the later numbers in the DX sequence that are more important, i.e. OB found at DX1 changes to disability at DX6 does the overall outcome become disability or OB?

An access report would be great to show the summary

The dx fields themselves do not take preference it is the code that occurs in them that does. In order for a record to be OB it would have to contain an OB code in any one of the dx fields. There could be a disability code in one of those fields as well. But it would still count as OB because OB takes precedence over all. With that said if OB and Disability occur in the same record it counts as OB so it would have to be removed from the original table because once i count it as OB i dont want to count it as Disability. Same thing between disability and TPL. If it contains both Disability and TPL in any dx field Disability would take precedence and even though TPL is there it would still be Disability. Those records would need to be removed as once there count as Disability they should not be counted as anything else.

Hopefully this clears things up

[OBP]

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.

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

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.

[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.