Results 1 to 6 of 6
  1. #1
    chr1stoper1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    55

    Grouping Data by dates


    Hi Guys

    I have a database which updates on a daily basis the production figures and scrap figures on several machines running lots of different tooling . The machines are constantly changing tooling .Currently I have lots of queries which can analyse the figures and look at the production figures and scrap rates on tools and machines etc.. But I have a problem which I'm not sure how to over come. I would like to make a report that can show me the how each piece of tooling ran on each production run by working on the figures between the dates it ran. So it can show me Tool "A" ran in machine "1" between date "A" and date "B" and the scrap rate was "X"
    I cant identify when the production run finishes. The longest a tool would not run would be two days(weekend) Any ideas would be much appreciated

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Query 1 to show every date so that you could say which days production was entered for the tool. Then a function to read that query and each time a tool changes have "start" and "stop" dates.

  3. #3
    chr1stoper1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    55
    Thanks for the reply .Its the working out the "start" and "end" dates with the function I cant fathom

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you post your database, or the part that counts, with some sample data? Then I can work it out and provide you with the exact instructions.

  5. #5
    chr1stoper1 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    55
    Hi
    thanks for the reply the database is huge so I have tried to show you what I mean by creating a simple database. If you open the bottom query and add a product number (try 5864435-01} this show s the result as a list of dates it ran. I want to be able to group the dates into runs.
    Attached Files Attached Files

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Here is a function which will give you start and end dates. It does not include the made and scrap, you will need to get those values by joining a separate query. Before you open your report, do a "Call GetDates()" which will then update the data.

    The names you have used have broken every rule in the book! It is very important to have the right names of the fields on the tables - you can change the names in queries when you show it to a user. Remove all special characters (/,(,%,etc). Change all reserved words - Access uses certain words for itself, these are called "reserved", words such as Date, Name, etc.
    Attached Files Attached Files

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

Similar Threads

  1. Grouping and dates
    By loonytoons in forum Queries
    Replies: 4
    Last Post: 02-24-2015, 10:28 AM
  2. Replies: 3
    Last Post: 06-27-2013, 10:18 AM
  3. Grouping Columns with dates
    By gauravnagpal in forum Access
    Replies: 6
    Last Post: 04-10-2012, 06:03 AM
  4. Replies: 10
    Last Post: 11-16-2011, 10:58 AM
  5. Replies: 1
    Last Post: 06-09-2011, 09:15 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