Viewing data from a query

[Paranap]

Hello

I have a database with training records of staff of different bits of kit. I have created the user interface to input when they are doing training and that is all groovy. The trouble I'm having is viewing the data. I've got queries to interigate the data for instance I have a crosstab query with the training needs down the names of the staff across and the value showing the last time they did that training. How can I incorporate that into the user interface and what I'd really like to do is some conditional formatting to show if something is out of date.
I'm sorry if this is a simple question but still learning access and this sort of thing I've not done before.

Thank you

[June7]

It is not easy to build a stable report to run perpetually based on CROSSTAB because CROSSTAB fields can be so dynamic. Review http://allenbrowne.com/ser-67.html

[Paranap]

Is there a way of displaying the data easily??

[June7]

Certainly, just not with a CROSSTAB.

[Paranap]

Ok if I want to show a list of training needs with the last time people have done it for a number of people how would I do it??

[June7]

What dataset are you basing the CROSSTAB on? Don't make it a CROSSTAB. Data will be vertically oriented.

Since I don't know your data structure, hard to give specific guidance.

[Paranap]

Here is my relationships. (Hope its attached properly) I need to be able to show lots of different data in a simple way which can be accessed from the user interface. The one I'm trying to do is show a list of the latest training for each training subject. I have a query that does that but want to show it in a better way. I thought a report would do that but they seem quite inflexible.

Any help you can give is very much appreciated

[June7]

Still not sure what you want to show.

If you want all training to be shown for each individual even if they haven't had that training, this requires a dataset of all possible combinations of training and people. This dataset can possibly be created by a query that includes tblTraining and tblOperative without a JOIN clause of course since they don't have a direct relationship. This results in a Cartesian relation of records - every record in each table will associate with every record of other table to show all possible combinations of training and operative.

Then build another query joining that dataset to tblOperativeTraining by linking on both TrainingID and Payroll fields (not INNER JOIN, probably RIGHT JOIN).

[Paranap]

Hi This is kind of what I'm after. Some sort of visual display showing the users and what training they need to do and then the date they last did it and then the colouring system is to show if something is in date. I currently use a system like this in excel but needed to update it hence the access work. As I said I am fairly new to all this and learning as I go so I think the main issue I'm having is how to display my data from the user interface. I can create queries to show the data but its viewing it from the user interface.

Thanks for your help

[June7]

That representation of the data is a non-normalized structure - this manipulation can be done with a CROSSTAB query. However, as already stated, building a report so you can do the conditional color coding is often not easy with CROSSTAB. Did you review the link in post 2?

If the CROSSTAB is designed to always generate the same fields, then simply use the query as the RecordSource of a report, bind textboxes to the fields and set Conditional Formatting.

[Paranap]

Yeah I think this may be my problem. The whole reason for going away from excel is that it makes it hard to change things. The Training titles although won't change much may change and the data will need to change with them. I've not really used reports. Do they not bind to the results of a query so if a query result changes the report will change too??

[June7]

Reports are bound to table or query. Yes, if data changes the records displayed will change. However, textboxes must be bound to field names that are in the RecordSource. Since fields of a CROSSTAB can be dynamic, the report will error if a field is no longer generated by the CROSSTAB. Means redesigning the report. Unless CROSSTAB is designed to ALWAYS generate the same fields.