Results 1 to 9 of 9
  1. #1
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13

    Custom Grouping & Sorting (pics attached)

    Hi! I would really appreciate your help in this:



    I am trying to create a report which will be grouping the projects according to the Stage. However, in some cases there are projects with the same exact name, apart form an extra CC1 added. In this case the CC1 shows an extra addition to the project but the users just create another project. Those two projects most of the times are in a different Stage. However, I was asked to create report that even though will group the projects by stage, it will keep those changed projects in the same stage group ignoring its actual stage.(Please see picture attached). Then in order for them to be able to track the actual stage of the project I have to add the stage field again. I hope this makes sense.. I would rather do that with a query but haven't figured out a way..


    Click image for larger version. 

Name:	Capture_Project Table.PNG 
Views:	11 
Size:	19.3 KB 
ID:	23557Click image for larger version. 

Name:	Capture_Report format.PNG 
Views:	11 
Size:	23.5 KB 
ID:	23558

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you will have to create a report control that contains enough of the project name to be able to group on that (say BKM001_11111).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Thanks Micron! I am sorry but I am relatively new to Access and I am not sure I understand what you mean by report control..

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us more about your database? What is it about? Can you post a jpg of your relationships window?

  5. #5
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Sure!So right now they are using a CRM to monitor the projects. Meaning the different stages and the budget.As of now they consider this to be an on-off project. Meaning I was just asked to import an excel file generated by our CRM which I used to create the table(an example of this is shown in the attached picture). So right now my DB has only one table. I know that in the future they will want to create a consistent database with data from various platforms. But right now the data is not consistent across the different platforms meaning for example that Project names do not much. In some cases there are project codes in others there are not.. . So I was trying to find a temporary solution in order to create that report only for the data generated from the CRM. I will have to extract the same excel file every week from the CRM and update the table in my DB. In that case,do you think I should split my table in to different tables anyway?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    From your post I gleaned this information concerning what you are doing. But I'm not sure what your ultimate goal is --keep or change CRM, replace CRM with Access based application???

    We monitor projects. A Project has different stages and budget. We currently have issues with Project names that do not match. We use a CRM product, Excel and MsAccess (for reporting). In some cases there are project codes in others there are not.
    This is a classic example of a lack of data management discipline --no standards, multiple software products, no consistent big picture, and probably no documented business processes.

    My suggestion would be to get a solid description of what the business and rules are, and document same. That doesn't help with the current situation, but it does show you what you are aiming at or a goal that can be defined and tested.

    Once you get a clear picture of the business, you can start to design a database to support that business. Building a data model and testing it with sample data will give you a blueprint for your "logical business".

    You can build interim databases and changes to meet your current/evolving needs, and keep working within the blueprint design.

    Does the CRM not provide an adequate reporting function?

    Good luck.

  7. #7
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    You are so right and thank you for your answer!That is exactly the case! Of course the CRM provides an excellent reporting functionality!That was my first reaction as well.From what I understand, the goal is to keep using all these various platforms but use

    Access for joining all this data and only for reporting purposes. The users will keep entering data in all the different software (different software for time-sheets,different software for projects etc) and then I will have to update my DB weekly in order to

    create joint reports. My understanding is that they want to monitor the projects along with the employees' billable hours and project budget. I haven't given a specific direction yet.

    Unfortunately this is a report that I was asked to deliver ASAP. Any idea how I could approach this?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    even though will group the projects by stage, it will keep those changed projects in the same stage group ignoring its actual stage.
    I think what you're asking is impossible - it's just mutually exclusive. What I was referring to was a field/control on the report that contained enough of the project name to group similar id's together, but it is impossible to group by a status (or anything else) while ingoring that value at the same time. So my suggestion was aimed at providing a means to group by project id's that are similar, thus I take it, related, regardless of any other attribute they might have. I also agreee that your task is monumental. It is hard enough for novices to get the tables related and normalized without having to deal with varying formats that need to be modified to fit the db model. It's like someone has handed you a book on rocket engines and asked you to put one in orbit. You can do either if you have time.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Thank you!I will try to explain this and hopefully they will understand the need for putting together a better process.

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

Similar Threads

  1. sorting and then attached photos gets lost
    By newguy28 in forum Forms
    Replies: 1
    Last Post: 11-04-2014, 04:49 PM
  2. Sorting & Grouping
    By tanyapeila in forum Reports
    Replies: 6
    Last Post: 03-27-2014, 03:49 PM
  3. Replies: 11
    Last Post: 01-12-2012, 07:55 PM
  4. Date not Sorting Correctly (screenshot attached)
    By KrenzyRyan in forum Queries
    Replies: 5
    Last Post: 06-27-2011, 01:44 PM
  5. Replies: 1
    Last Post: 07-09-2006, 09:23 AM

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