asked on

Help needed in creating either a crosstab query or an equivalent VBA function.

Hi Experts,

I need to have the following in a sub form within a main form.

From the table Patients_Daily_Medications

with the fields below.
PatientID
Medication
Day
TimeGiven
Sequence

I need a crosstab query that would show me Medications as column heading and sequence as row heading and TimeGiven as the values.

See attached what the data looks like, and what I am trying to accomplish (not completed as you can see...)

At the end I would need this query as the record source of a form that has a patientID and a date as fields, so this should only return values of a sibgle patient at a particular day.

PS- Note TimeGiven has a date+time, we need to extract the Time only.

PS -, a VBA function that will open a recordset of that table, Accept two params (PatientID,Day) and return the following would also serve the purpose.
ALBUTEROL: 9:00 AM, 11:00 AM BUDESONIDE: 11:AM, 1:00PM, 5:00 PM MIRALAX: 3:PM

Thanks in advance.
Untitled.png
Untitled1.png

John Tsioumpris

I think that a form based on a crosstab query is not a very good idea...since crosstab is readonly...unless you use it as an optical aid..so that you put it as a "subform' along with a helping form to input the "data"

bfuchs

ASKER

so that you put it as a "subform' along with a helping form to input the "data"

Correct, this is what I'm trying to do.
However I am open to a vba solution as well.
Thanks,
Ben

ASKER CERTIFIED SOLUTION

bfuchs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Edwards

So you answered your own question......

I don't think you get any points for answering your own question, but I'll let the moderator be the judge of that.