Report or Query with multiple lines per record 
Author Message
 Report or Query with multiple lines per record

I have created a database for managing patient visits.  One Table has a
record for each visit.  Each visit record has fields for up to six
scheduled follow up visit dates (i.e. DATE1, DATE2, DATE3, etc.) .  I would
like to produce a query that will produce a separate line for each date in
the record (without including the other dates), meaning six unique lines
for each record.  

Is there a way to do this with a query.  If not, any suggestions on how I
might accomplish this using code.  Any and all ideas appreciated.  Thanks

--
 W. David Urquhart
Assistant Director of Computer Services
Territorial Court of the {*filter*} Islands
St. Thomas, VI
voice:   (340) 693-6407
fax:       (340) 776-8690



Tue, 24 Apr 2001 03:00:00 GMT  
 Report or Query with multiple lines per record
The visit record table is poorly designed.
Any time you see fields like DATE1, DATE2, DATE3, etc. there is a structural
problem.
Access (and other databases) have wonderful tools for searching a single
column but they are clumsy when searching "horizontally" across many
columns.

The table needs to be split.
Link  a new table by PatientID and create a field called VisitDate.
Now you can enter an "infinite" number of visit dates and they are all in
one column so you can search it w/ >, or < or Between...


Quote:
>I have created a database for managing patient visits.  One Table has a
>record for each visit.  Each visit record has fields for up to six
>scheduled follow up visit dates (i.e. DATE1, DATE2, DATE3, etc.) .  I would
>like to produce a query that will produce a separate line for each date in
>the record (without including the other dates), meaning six unique lines
>for each record.

>Is there a way to do this with a query.  If not, any suggestions on how I
>might accomplish this using code.  Any and all ideas appreciated.  Thanks

>--
> W. David Urquhart
>Assistant Director of Computer Services
>Territorial Court of the {*filter*} Islands
>St. Thomas, VI
>voice:   (340) 693-6407
>fax:       (340) 776-8690




Tue, 24 Apr 2001 03:00:00 GMT  
 Report or Query with multiple lines per record
David,
You can use a union query, similar to this:

SELECT Table2.PatientID, Table2.DATE1 FROM Table2;
UNION ALL
SELECT Table2.PatientID, Table2.DATE2 FROM Table2;
UNION ALL
SELECT Table2.PatientID, Table2.DATE3 FROM Table2
...etc...
ORDER BY Table2.PatientID, Table2.DATE1;

(but it won't be updatable).  See also Joe Fallon's point about bad
structuring.

Simon Lewis


Quote:
>I have created a database for managing patient visits.  One Table has a
>record for each visit.  Each visit record has fields for up to six
>scheduled follow up visit dates (i.e. DATE1, DATE2, DATE3, etc.) .  I would
>like to produce a query that will produce a separate line for each date in
>the record (without including the other dates), meaning six unique lines
>for each record.

>Is there a way to do this with a query.  If not, any suggestions on how I
>might accomplish this using code.  Any and all ideas appreciated.  Thanks

>--
> W. David Urquhart
>Assistant Director of Computer Services
>Territorial Court of the {*filter*} Islands
>St. Thomas, VI
>voice:   (340) 693-6407
>fax:       (340) 776-8690




Tue, 24 Apr 2001 03:00:00 GMT  
 Report or Query with multiple lines per record
Changing the tables to have date1, etc in different tables as others have
mentioned is by far the best way to go.
It is also the safest, since as soon as some one starts using this program
chances are they are going to ask you to allow them to add comments to the
follow up dates.
It will also allow much easier reports on appointments.

If you put a list box or subform on the scheduler showing all previous
appointments and allowing movement to those appointments it might be
interesting.

I just did something similar for lab tests at the local university. Test
number 2 from the old app I built in 1991 had test numbers in 94 and 98.
This guy has been in school for at least 8 years.

At this point (you are getting sleepy) you will realize that the project is
to difficult for you and that I'm the only person that can help you. My
wanting to live where it is warm all the time has nothing to do with it.
(you are very sleepy.) send ticket.


Quote:
>I have created a database for managing patient visits.  One Table has a
>record for each visit.  Each visit record has fields for up to six
>scheduled follow up visit dates (i.e. DATE1, DATE2, DATE3, etc.) .  I would
>like to produce a query that will produce a separate line for each date in
>the record (without including the other dates), meaning six unique lines
>for each record.

>Is there a way to do this with a query.  If not, any suggestions on how I
>might accomplish this using code.  Any and all ideas appreciated.  Thanks

>--
> W. David Urquhart
>Assistant Director of Computer Services
>Territorial Court of the {*filter*} Islands
>St. Thomas, VI
>voice:   (340) 693-6407
>fax:       (340) 776-8690




Tue, 24 Apr 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Need to display multiple lines mini report within one record in a report

2. How to make (DB)ListBox show 2 fields per line (1 line = 1 record)

3. How to make (DB)ListBox show 2 fields per line (1 line = 1 record)

4. How to make (DB)ListBox show 2 fields per line (1 line = 1 record)

5. Data Report w/ multiple records/line

6. More than 1 record per line in the details section

7. Count of Records per Line

8. 2 records per line

9. Output query to text file with 3lines per record

10. Crystal Report: Line per Page

11. ? Text Box: multi line; fixed length per line

12. Delete multiple record (Query)

 

 
Powered by phpBB® Forum Software