CrossTab/Source Relation

  • Thread starter Thread starter GRayL
  • Start date Start date
G

GRayL

How do you find which record in a source table is the
row/column intersection of that table's crosstab,
programatically or otherwise?

Source CrossTab
DateTime:var -> Date1,Date2,Date3...
Time1: var, var, var
Time2: var, var, var
 
I am having trouble understanding your question. The values in a crosstab
may represent a sum or average or other. Maybe you need to type in some
actual records in the "source table" as well as the resulting crosstab
display and what exactly you want.
 
Duane: One table of DrID, datetimes, and PatientID for
every 15 min on specific working days for each doctor.
I use the CrossTab to generate the schedule for a
specific doctor for the next 35 days using the first
function as follows:

TRANSFORM First(nz([nam],0)) AS Patient
SELECT TimeValue([DateTime]) AS [Time]
FROM DrsAppts
WHERE (((DateValue([Datetime])) Between Date() And Date()
+35) AND ((DrsAppts.DrID)="nnnnn"))
GROUP BY TimeValue([DateTime])
PIVOT DateValue([Datetime]);

This crosstab shows a "0" for a date and time slot open
for the specific doctor. When I find a slot suitable for
a patient, I would like to translate the cursor position
on the crosstab to the specific record in the Schedule
table and fill in the patient id. There is only one
datetime for each doctor so the first aggregate function
works just fine. If a patient has already been assigned a
datetime, his/her patientid or NAM shows up in stead of
the "0". In the example below, the table had been
filtered for a specific doctor and var would be NAM above.

Ray
 
Back
Top