How to show Query value in the Form?

[Unicon]

Dear All,

Please help me out re the attachment. I have a query with Name "PSBD Processor Query" and I want to show the value of Query in the main form.

Is there any way to make it possible??

Looking for your favorable response as always.

Thanks in advance.

 

[JamesMcS]

You've just crosstabbed the wrong way round is all. Change the query and create a subform in your main form with the query as its source object.

 

[DCrake]

It looks like you are using a cross tab query. So to get what you want simply switch round your column and row.

Use the date field as the column headings and the text field as the row heading.

 

[JamesMcS]

Beat ya

 

[DCrake]

Go to the top of the class.......

Come down you fool.

 

[JamesMcS]

[Revelling in glory]

 

[Unicon]

Thanks James & DCrake but I need to update values every week which I am updating in simple Query. It's not a crosstab query. Please see attached table structure.

Please advise.

Is there any way to count values directly from query to form??

 

[JamesMcS]

Ah - not sure how to pull back specific values from a query - DC, is a dlookup against a query possible? Or does it have to be a table? I can imagine a query-based dlookup is rather slower than the table option....

So, the table being the way it is, it'd be a nightmare to show as you can only have one column header per crosstab. Would it be possible to redesign the table? If it was me I'd have date, category (or however you'd describe PL etc. as a group), and value. That way you can crosstab it with category as a row header, date as a column header and the values as your value.

 

[Unicon]

Thanks James, I can redesign the table but how to design? as I have 6 values need to show on a different dates.

Please advise.

 

[JamesMcS]

Well, make a new table with the following field names:

Date
Category (i.e. NA, NT, NP etc. - however you'd describe these)
Value (Or whatever the numbers represent)

Then, when you enter data, you'll enter date, category and value, one per line. (You can get the data from your other table into this one no probs, just a bit time consuming - a series of update and append queries will do this).

When it comes to tabulating the data, create a crosstab query based on this table. Drag down Category as a row header, date as a column header and Value as value. Run it, it'll look like the table at the bottom of your original PDF.

Then, you can use the crosstab query as the source object of a subform in your main form, job done!

 

[DCrake]

You could actually create a union query to do it

Select NT As NameA, OtherField As NameB From Table
Union all Select AB As NameA, OtherField As NameB From Table
Union all Select CD As NameA, OtherField As NameB From Table
etc

This would get all fields into one field then use that.

Just a thought, not tried it.

 

[JamesMcS]

I've not tried unions at all, but I would imagine that's the more efficient route to go down (only because DC said it though )