Form based on Crosstab Query

[xplo5iv]

Hi

I have a continuous form based on a crosstab query. The control source of each text box is a field from the query, however not all the fields are in the query each time.

Is it possible to make the control source the 3rd field, 4th Field etc?

 

[xplo5iv]

Help! Have I asked for something impossible to do?

 

[mik18]

I had to do something like this jsut recently.
You need to take a couple of steps back from the crosstab query to make this work.

I'm sure the column headings in the crosstab you want displayed even if there is null values. To do this you need a query that selects the column values you want and has default values in the rest of the columns.
Take your query that you use to create the crosstab and union that to the query mentioned above. Make sure you have the columns matched up correctly. Use this union query to create your crosstab. One line will have default values but you can filter those out later.
This is what I used for mine.

SELECT * FROM qry_Indication_Intervention

UNION ALL SELECT * FROM qry_Medication_Intervention

UNION ALL SELECT 0,"D",0, "D",Drug_Sub_Domain FROM L_tbl_Drug_Therapy_Sub_Domain;

This took a while to get just right so if you need more assistance just keep posting.

good luck

mike

 

[xplo5iv]

Thanks for this, Mike.
I can see where you're coming from, but it won't do what I'm looking for. I actually want to drop some of the columns - there are ~100 potential columns, but each user will only need a max of 20, and I can only get 20 on a form before it gets too cluttered.

Which leaves me still stuck...! I'm surprised Access doesn't seem to have a way of doing it.

 

[mik18]

In my exmple i only had 7 columns and I wanted to keep them all.

When do you filter your results based on user? Before or after you run the crosstab.

 

[xplo5iv]

Before. The main table is queried to provide a 1 user only temp table, which is then cross-tabbed.

 

[xplo5iv]

I mean't to post on here that I'd found a solution to this, and the cross-tab form now selects only fields with records. The work round isn't quite as elegant as it could be, but works perfectly and looks fine, by using ranking of entries. If anyone's interested I'll post the db

Jonathan

 

[mlesniak001]

hi xplo5iv / Jonathan,

i would be interested in seeing your work-around. I have a very simmilar design requirement.

cheers,
mike

 

[xplo5iv]

Hi

Sorry for delay, been on holiday

I'm not sure how/whether I can post a db on here - shall I email it to you?

Jonathan