Avatar of bfuchs
bfuchs
Flag for United States of America 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
Microsoft AccessVisual Basic ClassicVBASQL

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck