Displaying Oldest and Newest Date in Query

[spyldbrat]

I don't know much about Access, but I have Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?



Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.

I have included an example of my current output and what I need the output to read.

Current Output

Sunday	In	Out	Monday	In	Out	Tuesday	In	Out
			Monday, June 22, 2015	8:00:00 AM	2:30:00 PM	Tuesday, June 23, 2015	8:00:00 AM	2:30:00 PM
Sunday, June 21, 2015	4:00:00 PM	11:59:00 PM	Monday, June 22, 2015	4:00:00 PM	11:59:00 PM
Sunday, June 21, 2015	12:00:00 AM	6:00:00 AM	Monday, June 22, 2015	12:00:00 AM	6:00:00 AM	Tuesday, June 23, 2015	12:00:00 AM	6:00:00 AM
Sunday, June 21, 2015	12:00:00 AM	8:00:00 AM

What I actually need to see:

Beg Date	End Date	Directions	Sunday	In	Out	Monday	In	Out	Tuesday	In	Out
06/21/15	06/23/15	Directions				Monday, June 22, 2015	8:00:00 AM	2:30:00 PM	Tuesday, June 23, 2015	8:00:00 AM	2:30:00 PM
06/21/15	06/23/15	Directions	Sunday, June 21, 2015	4:00:00 PM	11:59:00 PM	Monday, June 22, 2015	4:00:00 PM	11:59:00 PM
06/21/15	06/23/15	Directions	Sunday, June 21, 2015	12:00:00 AM	6:00:00 AM	Monday, June 22, 2015	12:00:00 AM	6:00:00 AM	Tuesday, June 23, 2015	12:00:00 AM	6:00:00 AM
06/21/15	06/23/15	Directions	Sunday, June 21, 2015	12:00:00 AM	8:00:00 AM

[ranman256]

If you have dates in multiple columns then your db layout is wrong.
it should be

event	Date	direction
event	5/1/15 8:00 AM	IN
event	5/1/15 5:00 PM	out

or even:

event	in	out
event	5/1/15 8:00 AM	5/1/15 5:00 PM
event	5/2/15 8:00 AM	5/2/15 5:00 PM

as you have it, you cant utilize access MIN/MAX functions correctly.

[June7]

Finding the 'oldest' or 'newest' of several fields in same record requires testing each field against the other fields. Usually too complex to build an expression in query. Alternative is a custom VBA function.

As ranman suggests, normalized data structure is real solution.