Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Help with a query

    Hi,

    I am hoping someone can help me from the lounge with my query. I can't seem to figure out how to change my query to get the results I am looking for. I am using grouped by all the fields except that I have a min on start date and max on end date. However, see my attached excel sheet for the table, results from the query and the results I am looking for. I am not sure how to change the query to meet my needs. Help...

    Thanks so much.
    Attached Files Attached Files

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I don't think you can get what you want with a single query, to be honest. I will try to do it for you, though, through a combination of queries.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, first a query to catch the state transitions:

    Code:
    SELECT  TOP 1 T1.[Start Date],T1.[End Date], T1.Job, T1.[JobTitle], T1.[OrgUnit], T1.[OrganizationalUnit] FROM Table1 T1,  Table1 T2
    WHERE (T1.[Start Date]=DateAdd("d",1,T2.[End Date]))  AND (   (T2.Job <> T1.Job) OR (T2.[OrgUnit]<>T1.[OrgUnit]) OR (T1.[OrganizationalUnit]  <>  T2.[OrganizationalUnit]  ) )
     ORDER BY T1.[Start Date] DESC )
    
    
    UNION
    
    
    SELECT  T1.[Start Date],T1.[End Date], T1.Job, T1.[JobTitle], T1.[OrgUnit], T1.[OrganizationalUnit] FROM Table1 T1,  Table1 T2
    WHERE (T1.[End Date]=DateAdd("d",-1,T2.[Start Date]))  AND (   (T2.Job <> T1.Job) OR (T2.[OrgUnit]<>T1.[OrgUnit]) OR (T1.[OrganizationalUnit]  <>  T2.[OrganizationalUnit]  ) )
    ORDER BY T1.[Start Date] DESC
    I noticed that you have different OrganizationalUnits with the same OrgUnit value. Is that correct or was it just a mistake in the sheet data?

    This one should be saved as a query (in Access) or View (in SQL Server). It gives you all the transitions and the final state. It can then be used with the original table to give the correct start and end dates for each of the periods with a common value of
    Job, OrgUnit and OrganizationalUnit.
    Last edited by ruirib; 2012-05-26 at 16:43.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Where are you developing this? Access, SQL Server?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I am working in Access.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    Here is the sql statement in Access I am trying to do based on your query. What you call T1 works fine but what is T2? Is that the same table added again? How do I do the UNION? Something is not working with my statement. Thanks so much for your help.


    T1 is represented as ([OrganizationalAssignment&orgs]
    ------
    SELECT TOP 1 [OrganizationalAssignment&orgs].[Start Date], [OrganizationalAssignment&orgs].[End date], [OrganizationalAssignment&orgs].Job, [OrganizationalAssignment&orgs].JobTitle, [OrganizationalAssignment&orgs].Orgunit, [OrganizationalAssignment&orgs].Organizationalunit FROM [OrganizationalAssignment&orgs]; Table 1 T2
    Where [OrganizationalAssignment&orgs].[Start Date]-DateAdd (d,1, [OrganizationalAssignment&orgs]. [OrganizationalAssignment&orgs_1].[End date])) AND (([OrganizationalAssignment&orgs_1]Job<>[OrganizationalAssignment&orgs].Job OR (T2.Orgunit<>[OrganizationalAssignment&orgs].Orgunit OR ([OrganizationalAssignment&orgs].Organizationalunit <>T2. Organizationalunit ))
    ORDER by [OrganizationalAssignment&orgs].[Start Date]DESC)
    UNION
    SELECT [OrganizationalAssignment&orgs].[Start Date], [OrganizationalAssignment&orgs].[End date], [OrganizationalAssignment&orgs].Job, [OrganizationalAssignment&orgs].JobTitle, [OrganizationalAssignment&orgs].Orgunit, [OrganizationalAssignment&orgs].Organizationalunit FROM [OrganizationalAssignment&orgs]; [OrganizationalAssignment&orgs_1]
    WHERE [OrganizationalAssignment&orgs].[End date]=DateAdd(d,-1, [OrganizationalAssignment&orgs_1].[Startdate])) AND (([OrganizationalAssignment&orgs_1].Job<>[OrganizationalAssignment&orgs].Job) OR
    ([OrganizationalAssignment&orgs].Orgunit<> [OrganizationalAssignment&orgs_1].Orgunit)OR
    ([OrganizationalAssignment&orgs].Organizationalunit <>[OrganizationalAssignment&orgs_1].Organizationalunit))
    ---------

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The same table needs to be added twice, as you need to compare two contiguous records. So you need to add two tables, first time I gave it an alias of T1, second alias is T2. In all sincerity, this is a complex thing to do and my view is that the cleanest way to do this is to write a VBA function that will use the records coming out of this query to calculate the dates for each of record. With the need to compare a record with its previous or following record and the fact that the first record has no previous and the last has no next, doing it with queries alone is complex.

    I will try to write the function, but can't promise a time frame. Playing with a chain of queries yesterday got me into 4 queries and then Access didn't budge.
    Last edited by ruirib; 2012-05-27 at 11:36.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, I hate the way Access handles dates, always hated it and I still do. I have wrote the function but I can't get it to return all the proper results for me, due to my european date formats.

    As such, I am leaving what I did to you, explaining each of the queries and the function I wrote.
    In the attached database, Table1 has your data. I think you need to recreate it, so that date formats are correct for your settings. Transitions is the query I posted earlier, that will give you transition records between different "states" for each user. I added EmpID to Transitions.

    I created a function name GetStartDate, now residing in Module1. The idea for this function is to use the data in Transitions and Table1 to get the correct start dates for the record in Transitions (which is really what you need). The query FinalValues does this. The start dates for two of the intermediate records in Transitions are not returned properly. It is my opinion that this is a result of conflicting interpretations of date values by Access.

    My suggestion would be for you to start a new database and create your own Table1. Then you can import my queries and my module. You may need to add a reference, in the module, to the proper ADO version. I can do that for you, if you tell me what Access version you are running.

    I am attaching the zipped version of the database.

    HTH

    P.S.: Attachment removed, will attach a new one in the next post
    Last edited by ruirib; 2012-05-27 at 10:40.

  9. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, I decided to start anew with the data, changed by regional settings to English US and pasted your data into the table after doing that. After a quick battle with Access, that decided DateAdd was being called wrongly, I stopped using DateAdd and now it all works.

    As I explained before, FinalValues gives you the values you need. It uses the function I wrote, and applies it to the TransitionsTablequery values. You can rename Table1 and even rename the column names in Transitions and the function and it all should work. It does now, for me:

    ACapture.JPG

    The database is attached here:
    Attached Files Attached Files
    Last edited by ruirib; 2012-05-27 at 11:36.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Oh wow, so sorry, i had not noticed you had provided a response. I need to turn on my notifications... I am going to give it a try and getBack to you. Thank you in advance!

  11. #11
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,
    I am getting a "overflow" message when I replace the table1 with a new table1. Do you know why I am getting that message? Is there something else I should be doing first?

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I have no idea. The final results are obtained through a succession of queries and the invocation of a function. I think the first query is Transitions, then TransitionsFinal, then FinalValues. Open each of these intermediate queries and check which one is erroring out. That can lead to some clues about the origin of the problem.
    Last edited by ruirib; 2012-07-04 at 11:05. Reason: spelling

  13. The Following User Says Thank You to ruirib For This Useful Post:

    Corden (2012-07-04)

  14. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Do let me know if you need help. It's been a while since I did it, but I can try. I suspect the issue may lie with the date operations.

Posting Permissions

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