Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab All records problem (2003 SP1)

    I want to create a crosstab report, derived from 3 tables.

    The picture below shows the rough idea. The problems i have at the moment, are that i can
    create a crosstab, that almost does what i want, but i then cannot turn it into a report.

    From the People table, i need to be able to include all people, say on YELLOW shift, and they need to appear in the final report, even if they have no training records from the training table. (eg: there are 15 people on Yellow shift, they all need to be shown, even if they have no training in anything)

    From the specific Docs to check table, the header of the report needs to only be Docs 1 to 4 (but docs 1 to 4 Always needs to be shown on the header of the report)

    The 'Value' of whether they are trained in a specific document, is gleaned from the Training table, which has the doc number, and whether they are trained or not (Trained Y/N type of data) in that specific document.

    The crosstab i have created at the moment, shows all docs 1-4. but only shows 10 names on the left, because 5 people have no training but i need to show this as blanks in the table.

    I can't seem to get this into a report, as i get an error message of too many fields.

    Also, ideally on the report, i don't want to show the 'Y' but a coloured square. (no idea if thats possible)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab All records problem (2003 SP1)

    I'd start with a crosstab query based on the Training table, with Name as row header (Group By), Doc as Column header (Group By) and Trained as Value field (set the Total option to First).
    Set the Column Headings property of the query to "Doc 1";"Doc 2";"Doc 3";"Doc 4"

    Next, create a query based on the People table and on the crosstab query. Join them on the Name field, double click the join line and select the option to return all records from the People table.
    Add the name field from the People table and the Doc fields from the crosstab query.

    In the report, play with Conditional Formatting to display the Doc fields with colours.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab All records problem (2003 SP1)

    Thanks Hans... i've managed to get a crosstab working with the required number of people.. and the required documents...

    however.. i now have two problems

    The first column header in the report is this symbol '<>' which is weird...

    Secondly, the autoreport wizard just crashes every time, even though the query shows everything fine.

    Trying to do the report manually, and select the crosstab as the source, i don't get the 'value' data, as a required input into the
    report.. ie: im only offered documents running across the top, or names down the left.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab All records problem (2003 SP1)

    The column header <> represents a blank (empty) value. If you set the Column Headings property of the crosstab query, you shouldn't get this.

    If you put text boxes bound to Doc 1etc. in the detail section of the report, they will display the values of the field you specified as Value field in the crosstab query.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab All records problem (2003 SP1)

    Thanks Hans.. that fixes both of those problems... so i now have

    Name <trained condition format box> [doc2] [Doc3] etc

    But.. because i am forced to create the report manually... how do i get all the headings into the report. the headings are
    fixed.. i guess i could just make a bunch of labels and place those into the header. ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab All records problem (2003 SP1)

    If the wizard doesn't work, you'll have to create the labels manually. Create a few, then copy/paste to make life easier.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab All records problem (2003 SP1)

    Gahhh im being silly.. labels are no good, as the documents come from a table, and the document numbers may change !!!

    Is there any way to place a single item in there, that will update ?

    Edit: I actually have 64 documents total, to go across the top...
    Gahh why do i think this is gonna be hard.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab All records problem (2003 SP1)

    64 - isn't that a bit much? How are you going to fit them all across the top?

    You can use code to set the headings - see for example <post:=134,439>post 134,439</post:>. The database attached to <post:=35,485>post 35,485</post:> demonstrates another approach, allowing for an unlimited number of columns.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab All records problem (2003 SP1)

    Have looked at your suggestions Hans, thanks.. but i can't find a way to fit them to what i need. (prolly my own uselessness!)

    Regarding the 64 headers Hans...

    Do you know if its possible to use the Column Headings property in another way to restrict it to just the items in my table.

    Ie: there are 2000 documents in the documents table, but in my 'needs' table, i have 64 documents only, that i want across the top of the crosstab, and i also want them in the same order as the table..

    Is it possible to use a Dlookup on the table in the Column Headings property?

    At the moment, i can get the right amount of people on the left, but i cannot seem to get all 64 of the document names that i need across the top correctly, i can get about 30 (which is only those where there is a record of training).

    I could put all 64 in the Columm Headings property.. but if the document changed, either its title, or more likely its issue number, the query would become useless. [img]/forums/images/smilies/sad.gif[/img]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab All records problem (2003 SP1)

    The Column Headings property is a string, you can't refer to other tables in it. But you can use VBA code to generate the SQL string of the query. The SQL of your crosstab query will look like this:

    TRANSFORM First(<training>.[Doc]) As V
    SELECT <training>.[Name]
    FROM <training>
    GROUP BY <training>.[Name]
    PIVOT <training>.[Doc] In ("Doc1","Doc2");

    The part In ("Doc1","Doc2") defines the column headings. You can update it using the following code, where you must substitute the appropriate names, of course:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strHeadings As String
    Dim strSQL As String
    Dim intPos As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT [Doc] FROM [Need] ORDER BY [DocID]", dbOpenDynaset)

    Do While Not rst.EOF
    strHeadings = strHeadings & "," & Chr(34) & rst![Doc] & Chr(34)
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing

    strHeadings = " In (" & Mid(strHeadings, 2) & ");"

    Set qdf = dbs.QueryDefs("qryCrosstab")
    strSQL = qdf.SQL
    intPos = InStr(strSQL, " In (")
    strSQL = Left(strSQL, intPos - 1) & strHeadings
    qdf.SQL = strSQL

    Set qdf = Nothing
    Set dbs = Nothing

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab All records problem (2003 SP1)

    Will have a look at that code on Monday Hans,, many thanks...

    I tried to place all 64 documents into the Column Header, but it won't allow duplicate document numbers... (which is yet another problem for me)

    gahhh!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •