Crosstab functionality on a form 1

[Matf]

Hi I'm trying to use Crosstab functionality but I need to add data to the resulting query. Access will not let me add data to the resulting query from a Crosstab.
Has anyone found a way to display the data in a form the same way a crosstab does it but allowing the user to add or change the data?
Thank you

 

[MichaelRed]

You can join the XTab query with another recordset and display whatever resulting records are generated. While this sounds simplistic, there are a few wrinkles in the fabric, so -as is usual- the " ... devil is in the details ... "




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Matf]

Michael thanks for the idea. Now one question? How would you go around joining the recordset to the Xtab?
What should I make the base for my form the Xtab or a blank recordset?

 

[MichaelRed]

Ah, yes, those devilish details.



Again, it 'depends'.


If you want to include certain fields in the crosstab query, they need to be joined to some field in the (basic) source of the crosstab, and then just be included with the output.

On the otherhand, if you want the fields 'added' to the XTab resultsset, they process needs to become a layered query. The XTab itself, with at least one KNOWN column in the output -to which you join the additional fields from the 'other' recordset. This can be done in TWO ways. The simplistic and rigid approach is to use the "ColumnHeaders" property and simply type in the fields of interest. The XTab will then always have those (AND ONLY those) fields. The second approach is similar to the first, in that you make a join in the XTab query which is the list of desired / selected VALUES for the additional fields. Of course, this 'field' must appear int he XTAB query results, so it should be the outter of the left join. In the results set, simply join the 'forced' field to corresponding recordset.

In general, this is quite confusing for those who have not actually 'done it' -several times, so please think about the process. In the end, it is NOT so very different than any other query or set of layered queries, it just stretches the imagiination a bit, as many do not 'see' the XTab query fields as easily as the ones from the more common query types (e.g. select).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Matf]

Michael,
I think we may be talking about different things.
What I need to do is update the resulting data that the Crosstab query gives me. I don't need to add another field or another query.
I may not be understanding what you are explaining me or my initial question mey be confussing.
Please let me know if what you are recommending will allow me to display the data in Crosstab mode and still update it.
Thanks

 

[LachlanD]

A crosstab query is not updatable because it is a summary - it is not the raw data.
The only way to add / change the raw data is to add buttons to your crosstab form to open another form. The second form would show the raw data; which can then be added to or updated.
When you return to your cross tab then you will need to requery the form to show the updated data

 

[MichaelRed]

I mis-understood. Perhaps reading to much into the " ... add data ..." phrase.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places