string comparison in query

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