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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.