Report with Columns

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I want a report with 7 columns, one for each day of the week. Under
each day I want just the records for that day. I tried a listbox as well
as undound fields any help is appreciated.
Thanks
DS


SUN MON TUE
Lunch Lunch Dinner
Diner Late Night

I have a DayID field on each record, I thought that under Sunday the
field with DayID 1 would only show Sundays records but it's not that simple.
 
Sounds like you need a crosstab query.

Post the SQL for your query so someone can take a look.
 
Post a select query SQL that pulls the data. Omit the Days table as it is
not needed.
--
KARL DEWEY
Build a little - Test a little


DS said:
KARL said:
Sounds like you need a crosstab query.

Post the SQL for your query so someone can take a look.
Click to expand...
Heres the code, I tried a crosstab but its kind of, well it is new to me.

TRANSFORM Count(tblMenuSchedule.SchedMenuID) AS CountOfSchedMenuID
SELECT tblMenus.MenuName
FROM (tblMenuSchedule INNER JOIN tblMenus ON tblMenuSchedule.SchedMenuID
= tblMenus.MenuID) INNER JOIN Days ON tblMenuSchedule.SchedStartDay =
Days.DayID
GROUP BY tblMenuSchedule.SchedTerminalID, tblMenus.MenuName,
Format([SchedStartTime],"h:nn AM/PM") & " / " &
Format([SchedEndTime],"h:nn AM/PM")
PIVOT Days.DayName;

Thanks
DS
Click to expand...
 
KARL said:
Sounds like you need a crosstab query.

Post the SQL for your query so someone can take a look.
Click to expand...
Heres the code, I tried a crosstab but its kind of, well it is new to me.

TRANSFORM Count(tblMenuSchedule.SchedMenuID) AS CountOfSchedMenuID
SELECT tblMenus.MenuName
FROM (tblMenuSchedule INNER JOIN tblMenus ON tblMenuSchedule.SchedMenuID
= tblMenus.MenuID) INNER JOIN Days ON tblMenuSchedule.SchedStartDay =
Days.DayID
GROUP BY tblMenuSchedule.SchedTerminalID, tblMenus.MenuName,
Format([SchedStartTime],"h:nn AM/PM") & " / " &
Format([SchedEndTime],"h:nn AM/PM")
PIVOT Days.DayName;

Thanks
DS
 
This is what you need to get the day of the week --
PIVOT Format([SchedStartDay],"ddd");
--
KARL DEWEY
Build a little - Test a little


DS said:
KARL said:
Sounds like you need a crosstab query.

Post the SQL for your query so someone can take a look.
Click to expand...
Heres the code, I tried a crosstab but its kind of, well it is new to me.

TRANSFORM Count(tblMenuSchedule.SchedMenuID) AS CountOfSchedMenuID
SELECT tblMenus.MenuName
FROM (tblMenuSchedule INNER JOIN tblMenus ON tblMenuSchedule.SchedMenuID
= tblMenus.MenuID) INNER JOIN Days ON tblMenuSchedule.SchedStartDay =
Days.DayID
GROUP BY tblMenuSchedule.SchedTerminalID, tblMenus.MenuName,
Format([SchedStartTime],"h:nn AM/PM") & " / " &
Format([SchedEndTime],"h:nn AM/PM")
PIVOT Days.DayName;

Thanks
DS
Click to expand...
 
Back
Top