[AccessD] Would a crosstab Query work instead?

[Stuart McLachlan]

In a crosstab, I sometimes use  First  for the aggregate function if row/column values are 
unique.   That should work in this case   :)

On 25 Feb 2016 at 15:27, David McAfee wrote:

> Experiencing a bit of cerebal Falttulence...
> 
> I created the following query, which works, and gives the expected
> results, but I'm thinking to myself "there must be an easier way to do
> this..."
> 
> I was looking at cross tabs, but they all want to sum/count/average
> the data, I just want to display it.
> 
> 
> SELECT qrySchedules.FirstName,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'") AS SatStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'") AS SatEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'") AS SunStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'") AS SunEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'") AS MonStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'") AS MonEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'") AS TueStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'") AS TueEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'") AS WedStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'") AS WedEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'") AS ThuStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'") AS ThuEnd,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'") AS FriStart,
> 
> DLookUp("End","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'") AS FriEnd
> 
> FROM qrySchedules
> 
> GROUP BY
> 
> qrySchedules.FirstName,
> 
> DLookUp("Start","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("Start","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'"),
> 
> DLookUp("End","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'"); -- AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>