Results 1 to 3 of 3
  1. #1
    shadowsedge is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    19

    formatting a query?

    I want to make a query that shows information for 1 department.
    In the information, I want to to show an ID for each employee.
    For each employee, I would like it to show an infraction they committed and how many next to it.
    So I got the 3 fields but it shows every single one in a different format than what I have in mind.
    I know what I want but I don't know how to format it.

    Any halp?

    Basically, like this: (using imaginary examples)

    Electronics
    Employee #----- Infraction----Total

    0001 --------------Spitting--------3


    --------------------Swearing------6
    0002 --------------Spitting--------0
    --------------------Chewing-------3
    --------------------Swearing------0

    Toys
    0003---------------Slacking-------5
    Last edited by shadowsedge; 04-20-2016 at 10:54 AM. Reason: forums deleted my spacing so inserted dashes for spaces

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You want this for one table and you apply it to the others? Or are you going to join each table by EmpNo (using special characters = bad). Only exception is underscore.
    I bet you could make use of these pages sooner or later:
    http://allenbrowne.com/AppIssueBadWord.html
    http://access.mvps.org/access/general/gen0012.htm
    I taught myself to use some different versions (such as cmb for combo) rather than look these up every time in my early days. What really matters is that you adopt something that others should be able to follow and be somewhat consistent.

    AND normalization: the below sample should be derived from 2 tables, not 1. If this were a query result, OK. As a table, you should not be repeating the infraction itself, rather a Primary Key from tblInfractions. Infractions are one thing, Employees are another, departments another and so on. Keep them separate and bring them together only in forms or reports. If you have a spreadsheet mentality (stuff in columns) better learn the database (rows) mentality. Start here, but go beyond https://support.microsoft.com/en-ca/kb/283878

    After all that, your meaning of format might be different than my understanding. I don't get any clues as to what you desire in terms of formatting. If your example is too simplified and unrelated to what you really want, that approach often poses problems for us trying to help. One last thing re: removed spaces. Use code tags or copy/paste small sections of Excel spreadsheets like this
    tblElectronics
    EmplNo Infraction Total
    01 Spitting 3
    01 Swearing 6
    02 Spitting 1
    02 Tooting 3
    02 Having fun 5

    PS: I would get rid of employee 02. He looks like trouble.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you won't get a query to present per your requirement easily (i.e. employee number only appears on the first occurrence) but you can get a report to do this using the group properties.

    It is also technically possible to do so in a form using conditional formatting and a means of identifying the first occurrence - but it is quite complex and requires more information than you are displaying

    Bearing in mind queries are a means to an end - display in a form or report or export to another file - and not the end in its self, there is a way it can be done using union queries - but again requires more data than you are providing to facilitate this and you may not be able to use in a form or report

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

Similar Threads

  1. Formatting date in a query
    By accessmatt in forum Queries
    Replies: 1
    Last Post: 02-01-2016, 02:57 PM
  2. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  3. Better formatting for SQL Insert Into Query
    By Phred in forum Programming
    Replies: 7
    Last Post: 05-21-2015, 03:17 PM
  4. Formatting Query Results
    By tonygg in forum Access
    Replies: 6
    Last Post: 03-10-2015, 02:22 AM
  5. Query Field Formatting
    By Sparky in forum Queries
    Replies: 2
    Last Post: 11-13-2010, 04:03 PM

Tags for this Thread

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