Results 1 to 9 of 9
  1. #1
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Cross Tab Query

    Through the help of Micron I created a cross tab query of our issued and expended labor hours. We have numerous department supervisors that overcharge job orders and prior to this cross tab query it was difficult to track the Issued and Expended hours. We might issue 280 hours to a specific job order and the super may charge 320 hours. I set up an alias column in this cross tab query that shows the percentage of expended to issued and want to create an automated report based on criteria from this alias column such as the job order has reached 90%, 338 hours expended divided by 375 hours issued. The report would pull all records from the cross tab on any Job Order that is say greater than 90% and send this report via email within Access to the applicable supervisors. It would be sort of a warning not to overcharge any Job Order unless authorized.

    Any help is appreciated and I am not that versed in VBA.



    Regards

    Gene

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you most likely do not need vba.
    using queries, a table would have the limits, and another with hours.
    1 query would show the difference as it gets close to the limit.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The report would pull all records from the cross tab on any Job Order that is say greater than 90%
    You should research basing reports on cross tab queries because if there's a chance that the number of columns returned by this query could change, simply creating a report from a crosstab can cause that report opening to fail (when there's more report controls than there are fields returned by the query).

    As for not needing any vba, not sure how you'd send email without it - unless maybe you use macros (which I would not). For this you would search "send email from Access" or something similar. What you want is doable, but it involves a bit of work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Thank you Micron. Will do that.

    Regards

    Gene

  5. #5
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    ranman, Thanks for you response

    Let me throwback to what I think you mean. We are currently tracking 787 individual job orders in this cross tab query. Are you saying to create a table with the limits ie: 90%, 91%, 92%, etc and then another table showing all of the individual job orders along with issued and expended hours and then tie these two together in a query?

    Regards

    Gene

  6. #6
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Micron,

    Did some research on Cross Tab queries with reports and found no issues. I created one from the cross tab and only pulled 6 of the 15 columns that were necessary and all of the numbers were correct compared to the cross tab numbers. The labor table(s) include all Job Orders from10 departments and I was thinking of creating an individual report for each department. I looked at a Master Report option with underlying sub reports but that would not seem to work. I would need to create filters for each of the 10 reports/departments. That seems to be the best option. Would you agree.

    Regards

    Gene

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is the department field part of the report? If not, it probably should be. If it is, then do something to allow it to be part of the report criteria. Usually that's handled by your query. Right now I don't know what else you might be filtering on - maybe job number - but make sure department is part of that. You could use a form to provide the job number/department/dates/etc. A report per department where the reports all look the same is a bad idea
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Department Code is now part of the cross tab and will grouped as part of the report. Will create a form and pull in necessary columns, create a List Box along with command button to open report and also add a Tempvar on Code to pull all records within that code. All department reports will have the same columns however be differentiated by the code. The label header will have to be generic as I don't know of a way to write code to change the reports label header each time to say, Engineering Dept,Welding Dept, ect etc. Thanks again for your help and comments.

    Regards

    Hikerdood

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can make the department code part of the report, do you not have a corresponding description field in the dept table? If not and you cannot add "Welding" to a field that you might have to add to the table, there are other options (untested):

    hide the dept code field and in a calculated control for the report title (using the source property)
    - use the switch function, or
    - set that control to the value of a function that returns the dept text based on the code, or
    - set that control value using a Select Case block in the load event
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  2. Cross Tab Query
    By balajigade in forum Access
    Replies: 1
    Last Post: 09-09-2015, 01:12 PM
  3. Cross Tab Query
    By vishal09 in forum Access
    Replies: 3
    Last Post: 04-04-2015, 10:52 AM
  4. Cross Tab Query.
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 01-04-2012, 01:29 PM
  5. Cross Tab Query
    By RycherX in forum Queries
    Replies: 1
    Last Post: 04-26-2010, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums