Results 1 to 12 of 12

Thread: Crosstab (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab (A2k)

    <!t>[pre] tags removed by HansV - they caused horrible horizontal scrolling

    I want to create a crosstab query from the following select query:

    SELECT tblEMP.NME, tblEMP.INI, tblLABTXN.EMP_CDE, tblLABTXN.JOB_NO, tblLABTXN.SUP_NO, tblEST.F_DTE, tblLABTXN.WRK_GRP, tblLABTXN.ALL_HRS, tblLABTXN.TAK_HRS, tblEST.STA, [INI] & " " & [NME] AS Name, [ALL_HRS]/[TAK_HRS]*100 AS [Eff%]
    FROM tblEMP INNER JOIN (tblLABTXN INNER JOIN tblEST ON (tblLABTXN.SUP_NO = tblEST.SUP_NO) AND (tblLABTXN.JOB_NO = tblEST.EST_NO)) ON tblEMP.EMP_CDE = tblLABTXN.EMP_CDE
    WHERE (((tblEST.F_DTE)>=[forms]![frmCalendar]![txtDateFrom] And (tblEST.F_DTE)<=[forms]![frmCalendar]![txtDateTo]) AND ((tblLABTXN.ALL_HRS)>0) AND ((tblLABTXN.TAK_HRS)>0) AND ((tblEST.STA)="F"));

    Row header = Name
    Column Header = F_DTE (formatted to month)
    Calculation = ALL_HRS / TAK_HRS * 100

    The calculation is in the query and thought I could use that field.
    Somehow I'm getting the error "Scaling of decimal value resulted in data truncation"

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

    Re: Crosstab (A2k)

    Are all tables located in an Access database, or are they linked from another database system?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (A2k)

    The Query "qryLabourTransaction" originates from three underlying tables which are populated prior to to running the crosstab:

    SELECT tblEMP.NME, tblEMP.INI, tblLABTXN.EMP_CDE, tblLABTXN.JOB_NO, tblLABTXN.SUP_NO, tblEST.F_DTE, tblLABTXN.WRK_GRP, tblLABTXN.ALL_HRS, tblLABTXN.TAK_HRS, tblEST.STA, [INI] & " " & [NME] AS Name, [ALL_HRS]/[TAK_HRS]*100 AS [Eff%]
    FROM tblEMP INNER JOIN (tblLABTXN INNER JOIN tblEST ON (tblLABTXN.SUP_NO = tblEST.SUP_NO) AND (tblLABTXN.JOB_NO = tblEST.EST_NO)) ON tblEMP.EMP_CDE = tblLABTXN.EMP_CDE
    WHERE (((tblEST.F_DTE)>=[forms]![frmCalendar]![txtDateFrom] And (tblEST.F_DTE)<=[forms]![frmCalendar]![txtDateTo]) AND ((tblLABTXN.ALL_HRS)>0) AND ((tblLABTXN.TAK_HRS)>0) AND ((tblEST.STA)="F"));

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

    Re: Crosstab (A2k)

    Do you have any fields of type Number, Decimal?

    It might help if you could post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (A2k)

    Hans

    Heres a very, very cut down version.
    I've edited the data so its not sensitive.
    Thanks

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

    Re: Crosstab (A2k)

    I assume that the tables have been imported from another databas system. The Number fields in the tables have field size 'Decimal'. This is not a native Access data type; it is not fully supported. (Why is it available then, you ask? For compatibility with other database systems). Try changing the field size for the TAK_HRS and ALL_HRS fields to Double (you can do the other number fields to, if you like).

    Something else: open qryLabourTransaction in design view.
    Select Query | Parameters.
    Enter [forms]![frmCalendar]![txtDateFrom] in the Parameter column and set the Data Type to Date/time.
    Enter [forms]![frmCalendar]![txtDateTo] in the next row and set the Date Type to Date/Time.
    Click OK and save the query.

    Crosstab queries require that parameters in the underlying query are declared explicitly.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (A2k)

    Hans

    Yes I see now.
    A question, if I change the field from Decimal to Double, what happens when import more data from the external database.
    Will they keep the format I give them ?
    tblEst etc are create via make table queries so assume they won't because the query deletes the table first ?

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

    Re: Crosstab (A2k)

    If you use a make-table query, the changes will be undone again and overwritten by the design of the imported table(s).
    It would be better to use a delete query to delete all existing records, then an append query to add the new records. That way, you'll preserve the design.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (A2k)

    Thanks Hans

    It took a while to change the queries but it's running fine now.
    The only thing is, the calculations are incorrect.

    TRANSFORM Avg(qryLabourTransaction.[Eff%]) AS [AvgOfEff%]
    SELECT qryLabourTransaction.Name, Avg(qryLabourTransaction.[Eff%]) AS [Total Of Eff%]
    FROM qryLabourTransaction
    GROUP BY qryLabourTransaction.Name
    PIVOT Format([F_DTE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

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

    Re: Crosstab (A2k)

    What exactly is incorrect? The calculation of Eff% or the calculation of the average?

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (A2k)

    As the attached PNG:

    The first calculation at the top is for September , this calculation is correct.
    The calculation underneath is from the crosstab and the same date range, showing a higher efficiency.

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

    Re: Crosstab (A2k)

    I think you'll have to calculate the averages differently. Look at the following table:

    <table border=1><tr><td align=right>

Posting Permissions

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