ayha1999
asked on
QUERY
Hi,
I have attached an Access db with two tables.
Member:
UserID Name
00001000 Name1
00001001 Name3
00001002 Name4
00001004 Name6
00001005 Name7
History:
UserID EventDate EventTime
00001000 2013-03-13 08:49:34
00001000 2013-03-13 16:58:58
00001001 2013-03-13 10:59:04
00001001 2013-03-13 16:59:39
00001002 2013-03-13 08:54:07
00001000 2013-03-14 06:48:40
00001000 2013-03-14 07:02:08
00001002 2013-03-14 07:03:49
00001003 2013-03-14 07:04:44
00001004 2013-03-14 07:10:55
00001004 2013-03-13 16:44:22
How can return the following result if if I provide 2013-03-13?
All records from member table
two records (min time and max time for the given date) from history table.
Display Nil for no records in the history table for the given date.
The resut as follows:
UserID Name
00001000 Name1 2013-03-13 08:49:34 16:58:58
00001001 Name3 2013-03-13 10:59:04 16:59:39
00001002 Name4 2013-03-13 08:54:07 08:54:07
00001004 Name6 Nil
00001005 Name7 Nil
Thanks
ayha
db.mdb
I have attached an Access db with two tables.
Member:
UserID Name
00001000 Name1
00001001 Name3
00001002 Name4
00001004 Name6
00001005 Name7
History:
UserID EventDate EventTime
00001000 2013-03-13 08:49:34
00001000 2013-03-13 16:58:58
00001001 2013-03-13 10:59:04
00001001 2013-03-13 16:59:39
00001002 2013-03-13 08:54:07
00001000 2013-03-14 06:48:40
00001000 2013-03-14 07:02:08
00001002 2013-03-14 07:03:49
00001003 2013-03-14 07:04:44
00001004 2013-03-14 07:10:55
00001004 2013-03-13 16:44:22
How can return the following result if if I provide 2013-03-13?
All records from member table
two records (min time and max time for the given date) from history table.
Display Nil for no records in the history table for the given date.
The resut as follows:
UserID Name
00001000 Name1 2013-03-13 08:49:34 16:58:58
00001001 Name3 2013-03-13 10:59:04 16:59:39
00001002 Name4 2013-03-13 08:54:07 08:54:07
00001004 Name6 Nil
00001005 Name7 Nil
Thanks
ayha
db.mdb
First do a query with an inner join between your two tables, with a group by and min/max functions to get those records that have history. From there create a query that has an outer join and put is null as your criteria in the where clause for eventdate. You will then need to add a bogus date as well as time stamps to fill where the min/max would be. Finally union these two queries together. At that point you are done, but the structures of the queries must conform for the union. Hope that makes sense
When working with dates and times, you should define the data type in the table as a date/time to let Access ensure it is a date or time.
I made the change in the attached and created two queries. In one, the date is specified. In the other, a prompt for a date opens for you to enter any date.
Querydb.mdb
I made the change in the attached and created two queries. In one, the date is specified. In the other, a prompt for a date opens for you to enter any date.
Querydb.mdb
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
It worked.
Thanks
Thanks