Should I use an array for this data?

[baquist]

I have a table with several hundred records. Up to 10 of these records belong to single individuals; with the only difference in the records being in the date_seen field and diagnosis. I would like to make this more efficient and store the data with one record containing several dates and the diagnosis of that date. To change the following:

id name Date Diagnosis
1 Jon Doe 1/1/00 Warts
2. Jon Doe 1/1/00 Headache
3. Jon Doe 1/2/00 Influenza
4. Jon Doe 1/2/00 Hangnail
5 Jon Doe 1/2/00 Toe fungus


To this:

id name Diagnosis
Date

1. Jon Doe 1/1/00 Warts, Headace
1/2/00 Influenza, Hangnail
Toefungus


Any suggestions, please? I am under the impression that i need to put this in an array, but don't know how to do that.

Thank you.

 

[mission2java_78]

Actually the current method you use I personnaly think is much better. Parsing the data and extracting reports is much tougher with the method you are thinking. It is always ok to have a one to many relationship..in which you have.

Jon

 

[WayneRyan]

baquist,

Have to agree with Jon.

Try experimenting with queries with different sorts on people,
illness etc. Then experiment with grouping. I think you'll
find that it is very flexible.

Wayne

 

[baquist]

Thanks for your comments.

To further explain the issue, we have to eventually print out the table or a report showing all the diagnoses per date per person. So whereas I understand your well taken point about normalization, I would like a way to be able to show this data without printing out dozens of unnecessary rows. The original table will be kept for the purposes of data management.

Baquist

 

[mission2java_78]

So create queries and reports that combine this data....don't ruin your current setup...

Its kinda like eating at taco bell...if you dont hold your nacho supreme in a flat straight matter ... it wont be so supreme.

Same thing with your tables...you break the normalization and they wont be normalized .

Jon

 

[FoFa]

I understand your problem, I think. We did this by writing a function that took the person, returned all the rows of date/diag. and concantonate them together. we usually call this in a query that returns the other data and it is easy to use that way. Downside, large reports can take awhile as it is doing a lot of I/O.

 

[mission2java_78]

Fofa's method is what most people would use should there be a need to concatenate data...warning though as he said this could take time to run queries like so, keep in mind these queries may be calling functions in modules for each record.

Imagine 1k's of records each making a function call or several function calls.

Life isn't perfect now is it ?

 

[baquist]

In response to:

I understand your problem, I think. We did this by writing a function that took the person, returned all the rows of date/diag. and concantonate them together. we usually call this in a query that returns the other data and it is easy to use that way. Downside, large reports can take awhile as it is doing a lot of I/O.

Could you show me a sample of how to write that function?

Thanks

 

[The_Doc_Man]

The other guys are being nice. I'll tell you straight out, you have a FORMATTING problem, not a DATA problem. Do not REPEAT *DO NOT* break normalization to get this job done. As you gain more experience with Access databases, you will learn to really hate yourself for having done so.

If I understand your complaint, you are saying that you want to see

Patient John Q Public   1/3/03         Broken Toe, Broken Ankle
                        1/5/03         Broken Tibia, Broken Fibula

instead of

Patient John Q Public
   1/3/03
         Broken Toe
         Broken Ankle
   1/5/03
         Broken Tibia
         Broken Fibula

A good VBA programmer could make this happen for you but you are playing with fire. If you don't understand enough about VBA to write this for yourself, you will not be able to maintain it when you need it to be updated to add one more feature - and none of us will be able to advise you because the code won't be trivial at all.

Please don't misunderstand my intent here. I know that the aesthetics of Access can be disheartening at times. But from the nature of your question and subsequent comments, you are asking for an advanced function to do a problem that could be handled simply by accepting a few extra lines of output.

Stated another way: Using someone else's function if you are NOT comfortable with VBA is equivalent to buying a black box and hoping that it never breaks or needs upgrading.

Let me ask this question: Are you looking at putting page breaks between patients anyway? If so, why do you care whether you have compressed lines or more traditional line presentation? You will probably have lots of wasted space anyway.

 

[mission2java_78]

To add to Doc's comments:

Use Arial narrow on reports

Helps on the amount of paper wasted...its a standard here .

Jon

 

[Pat Hartman]

This article from the knowledge base explains the process nicely.

ACC: How to Concatenate a List of Items from a Many-Side Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

 

[baquist]

Thanks everyone for your comments and suggestions.

The_Doc_Man, we are not looking to have page breaks between each patient because the diagnoses are not the primary sense of what the report is for, just incidental data. Hence the desire to compress for space. More a matter of 'while we are at it, lets show this too............'

Pat, I looked at that article and I'm going to adapt from that.

I understand enough about access databases to realize that it is a matter of presenting the data, (or 'a formatting problem'), but since we tend not to use Access to report our data I'm not very familiar with its capabilities.

From our current setup we will only need to use this once or twice, and I really needed a sense of which direction to go in.

Cheers,

Baquist