Repeat columns in a query?

[DaverD]

Hello.

I am trying to graph seven series of data associated with blood pressure. They are date/time, systolic at home, diastolic at home, systolic at work, diastolic at work.

The table structure has date/time, systolic, diastolic, work/home. Records populate all fields with the work/home field identifying where the blood pressure was taken.

I need a query for the graph that has columns of date/time, systolic at home, diastolic at home, systolic at work, diastolic at work.

Is it possible to create a query that repeats the systolic and diastolic using the work/home field to designate where the blood pressure was taken?

Thanks in advance.

DaverD

 

[WildHare]

There's probably a slicker way of doing this, but I've had to do this sort of thing a lot in the past, and I've found that this technique seems to work...

1) Use a Make-Table query to create a table with the patient HOME readings - fields would be PatientID, Date/Time, HomeSys, HomeDias
2) In your query, add two NULL fields for WorkSys, WorkDia.
3) Run the query selecting HOME checked observations from the main table.
4) Use an APPEND query, matching your new table PatientID with the existing table PatientID, to update your previously-NULL fields of WorkSys and WorkDia, to the existing table readings that are checked WORK.

This should give you a dataset with the right structure for your reporting needs.



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[randy700]

How about a Crosstab query?


Randy

 

[DaverD]

Thanks for the ideas...

WildHare, I got your technique to work for me also. In fact, I employed a macro to generate the graph on demand right from the data entry screen. Thanks.

 

[Golom]

Possibly joining the table to itself would also work.
[tt]tblPressures
PatientID DateTime WorkHome Systolic Diastolic
01 02/05/2006 Work 120 80
01 02/05/2006 Home 118 78
etc.
[/tt]

Select W.PatientID, W.DateTime, 
       W.WorkHome, W.Systolic, W.Diastolic,
       H.WorkHome, H.Systolic, H.Diastolic

From tblPressures W INNER JOIN tblPressures H
     ON  W.PatientID = H.PatientID
     AND W.DateTime = H.DateTime

Where W.WorkHome = 'Work'
  AND H.WorkHome = 'Home'