We help IT Professionals succeed at work.

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

181 Views
Last Modified: 2019-01-19
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
Comment
Watch Question

John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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"
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
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.