I am facing a problem joining two rows into one. Here is the structure of my table:
ID Code DATE TIME
01 ABC-1 01/01/2009 06:00 AM
02 ABC-1 01/01/2009 10:00 PM
Here is what I need the output for my query to be:
Code DATE MIN(TIME) MAX(TIME)
ABC-1 01/01/2009 06:00 AM 10:00 PM
There are hundreds of records like ID: 1 and 2. but what I need for now is MinTIme and MaxTime record to be linked to one seperate query or view for the records having SAME DATE. I don't want to do this using table dump method.
Sorry for that, but is there any way to get what I need? I need minimum value of time for that code on one day as "A" and maximum value for time for that code on same day as "B". in the end, i need two fields Min and Max in one row with date field
please note that these are actually logs generated by system and i want to merge the max and min of them to one row grouped by date and then code
Redo your first post but this time use some real data and show what you want the result to be. I have no idea if what you want is possible because you have not yet shown us what your starting point is and what your desired end is.
Ok let me start from the beginning. I have a table under ACCESS named as LOGS. Following is the structure of the table:
234______007_____________01/02/09 07:00 AM
345______007_____________01/02/09 17:30 PM
What i want is, i need a query to run which should appear to me as:
ID_____________EMP_CODE_______IN TIME_____________OUT TIME
234____________007_____________01/02/09 07:00 AM___01/02/09 17:30 PM
Currently logs are dumped into this table each log as a row. There are maximum two logs per date and I want to merge them into one.
Means the query should return me two records in one or in simple way: It should filter the emp code (007) on date (01/02/09) and return this emp minimum time (time in) and maximum time (time out) in two fields and this "view merge" should be in one row so I can use in reports.
One more thing is that what can be done if more than two logs exists for same emp on same day
make a query show only the start times group by emp [QUEMPSTART]
make and other query only showing the End times group by emp [QUEMPEND]
[QUEMPSTART] and [QUEMPEND] by the emp
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.