How do i fashion a report on this crosstab query ? (1 Viewer)

[LOUISBUHAGIAR54]

I am trying to make a report on this crosstab query.

PARAMETERS [Forms]![Dispensing History]![Combo5] Text ( 255 ), [Forms]![Dispensing History]![Text2] DateTime;
TRANSFORM First(Query2.NurseID) AS FirstOfNurseID
SELECT Query2.Description, Query2.Dose, Query2.Frequency
FROM Query2
GROUP BY Query2.Description, Query2.Dose, Query2.Frequency
ORDER BY Query2.HIN
PIVOT Query2.HIN;

Basically Combo5 gives the idcard text of a patient from a combobox. Text2 gives the date when a drug is prescribed from a textbox on a form. NurseID is a unique ID 'text' to identify a particular nurse that dispenses a a particular group of medicines. The medicines are displayed as description,dose and frequency in the rows.

HIN is the time fraction of the Now() which is derived from a date/time field on a table.

The column headings themselves are the particular instances 'HIN' when medicines are given. There are several, ( around five) instances during the day when medicines are given. These column headings are dynamic as they change without any prediction according to the time of day when the drugs are dispensed. The crosstab query works as planned.

The problem is that when I try to make a report using the report wizard on this crosstab query, no fields appear.

There are two parameters that are declared.

I hope I have explained this problem clearly.

Can someone please give me some clues as to how I can solve this problem ?

 

[arnelgp]

your parameter is based on a form, so to be able to do what you want (i hope), you must open the form first before opening/editing your report.

 

[LOUISBUHAGIAR54]

Many thanks for the reply. Unfortunately that did not work out. When I try to do the report using the report wizard no fields appear in the dialogue box of the wizard.

I think that the problem lies with the columns. Since they are dynamic and the time (column ) headings (HIN) change with each report I think something needs to be done with the columns. I do not know how or whether it can be done. The following is how the crosstab query appears. The column headings derive from when the medicines are given. 430554M is the id number of the nurse dispensing the medicines (in this case the same one). There are three row headings of the medicine, dose, and frequency the dose is given.

Many thanks again for any further help.

Query2_Crosstab1 Description Dose Frequency 07:43:01 07:46:59 07:50:22 08:20:30 13:55:59 14:13:50 14:47:32 21:10:03 Bumetanide 1mg tab 0.5 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Insultard 100u/ml inj 50 BD 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Paracetamol 500mg tab 2 prn 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Slow K 600mg tab 1 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Omeprazole 20mg tab 1 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Folic Acid 5mg tab 1 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Salbutamol inhaler 2 8hrly 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Hypomellose eye drops 1 8hrly 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Paroxetin 20mgs tab 0.5 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m Amitriptyline 25mg tab 2 daily 430554m 430554m 430554m 430554m 430554m 430554m 430554m 430554m

 

[LOUISBUHAGIAR54]

I am sorry that representing the results of the query as shown above has probably put off more attempts at solving this problem. I admit that the results of the query appear confused on this post.

The problem with being able to put this query on a report is that the headings are dyanamic and keep changing. The headings are a time value, and they change.

Are there any new clues please ?

 

[JHB]

I am sorry that representing the results of the query as shown above has probably put off more attempts at solving this problem. I admit that the results of the query appear confused on this post.

Then show a printscreen of the query result instead.
You've parameters but I can't see you're using them in your query so why do you have them.

PARAMETERS [Forms]![Dispensing History]![Combo5] Text ( 255 ), [Forms]![Dispensing History]![Text2] DateTime;

Is the the number of columns also variable or always the same?

 

[HiTechCoach]

Here is a working example that may help: Dynamic Report based on a Crosstab query

 

[LOUISBUHAGIAR54]

Hello again.

Sorry I did not keep up. The number of columns may change as well as it depends on the number of times medicines are given to the patient. As medicines to an individual patient are given in a single batch at any one particular time the column heading will be at that particular time which is dynamic. Many thanks for any guidance.

 

[LOUISBUHAGIAR54]

I now presume that a solution may need to have code to stratify the time portion and to have fixed column headings. I have found this article interesting but I do not know how I can apply it to my problem.

http:/support.microsoft.com/en-us/kb/155489

Many thanks.