Can this be done in a Report?

[Sean]

I have a table of data called table1 and it has 2 fields, OrderNum and Time.
I want to have a report that will count the number of orders between
certian time frames and I want it to appear in one result set. So if I
wanted to see the number of orders between 0:00 and 4:00, 4:01 and 12:00,
12:01 and 16:00 I would get 3 different results all in one report. If
someone could provide me an example that would be great.

Thanks

 

[Duane Hookom]

I would create a table of time frames:
tblTimeFrames
===================
StartTime Date/Time
EndTime Date/Time
TFTitle Text

Add your records.

You could then create a query with Table1 and tblTimeFrames. The SQL view
would be:

SELECT StartTime, EndTime, TFTitle, Count(OrderNum) as NumOrders
FROM table1, tblTimeFrames
WHERE [Time] Between StartTime And EndTime
GROUP BY StartTime, EndTIme, TFTitle;

 

[Sean]

I am having trouble getting it to run, I get an error with the following code:

SELECT TimeFrame.TFTitle, Count(OrderLineDetails.[Order Number]) AS
[CountOfOrder Number]
FROM OrderLineDetails, TimeFrame
WHERE (((OrderLineDetails.Time) Between "Starttime" And "EndTime"))
GROUP BY TimeFrame.StartTime, TimeFrame.EndTime, TimeFrame.TFTitle;

I would create a table of time frames:
tblTimeFrames
===================
StartTime Date/Time
EndTime Date/Time
TFTitle Text

Add your records.

You could then create a query with Table1 and tblTimeFrames. The SQL view
would be:

SELECT StartTime, EndTime, TFTitle, Count(OrderNum) as NumOrders
FROM table1, tblTimeFrames
WHERE [Time] Between StartTime And EndTime
GROUP BY StartTime, EndTIme, TFTitle;

--
Duane Hookom
Microsoft Access MVP

I have a table of data called table1 and it has 2 fields, OrderNum and Time.
I want to have a report that will count the number of orders between
certian time frames and I want it to appear in one result set. So if I
wanted to see the number of orders between 0:00 and 4:00, 4:01 and 12:00,
12:01 and 16:00 I would get 3 different results all in one report. If
someone could provide me an example that would be great.

Thanks

 

[Duane Hookom]

Remove the quotes from around your field names:
SELECT TFTitle, Count([Order Number]) AS [CountOfOrder Number]
FROM OrderLineDetails, TimeFrame
WHERE [Time] Between [Starttime] And [EndTime]
GROUP BY TFTitle;

--
Duane Hookom
Microsoft Access MVP

I am having trouble getting it to run, I get an error with the following code:

SELECT TimeFrame.TFTitle, Count(OrderLineDetails.[Order Number]) AS
[CountOfOrder Number]
FROM OrderLineDetails, TimeFrame
WHERE (((OrderLineDetails.Time) Between "Starttime" And "EndTime"))
GROUP BY TimeFrame.StartTime, TimeFrame.EndTime, TimeFrame.TFTitle;

I would create a table of time frames:
tblTimeFrames
===================
StartTime Date/Time
EndTime Date/Time
TFTitle Text

Add your records.

You could then create a query with Table1 and tblTimeFrames. The SQL view
would be:

SELECT StartTime, EndTime, TFTitle, Count(OrderNum) as NumOrders
FROM table1, tblTimeFrames
WHERE [Time] Between StartTime And EndTime
GROUP BY StartTime, EndTIme, TFTitle;

--
Duane Hookom
Microsoft Access MVP

I have a table of data called table1 and it has 2 fields, OrderNum and Time.
I want to have a report that will count the number of orders between
certian time frames and I want it to appear in one result set. So if I
wanted to see the number of orders between 0:00 and 4:00, 4:01 and 12:00,
12:01 and 16:00 I would get 3 different results all in one report. If
someone could provide me an example that would be great.

Thanks

 

[Sean]

Thank you

Remove the quotes from around your field names:
SELECT TFTitle, Count([Order Number]) AS [CountOfOrder Number]
FROM OrderLineDetails, TimeFrame
WHERE [Time] Between [Starttime] And [EndTime]
GROUP BY TFTitle;

--
Duane Hookom
Microsoft Access MVP

I am having trouble getting it to run, I get an error with the following code:

SELECT TimeFrame.TFTitle, Count(OrderLineDetails.[Order Number]) AS
[CountOfOrder Number]
FROM OrderLineDetails, TimeFrame
WHERE (((OrderLineDetails.Time) Between "Starttime" And "EndTime"))
GROUP BY TimeFrame.StartTime, TimeFrame.EndTime, TimeFrame.TFTitle;

I would create a table of time frames:
tblTimeFrames
===================
StartTime Date/Time
EndTime Date/Time
TFTitle Text

Add your records.

You could then create a query with Table1 and tblTimeFrames. The SQL view
would be:

SELECT StartTime, EndTime, TFTitle, Count(OrderNum) as NumOrders
FROM table1, tblTimeFrames
WHERE [Time] Between StartTime And EndTime
GROUP BY StartTime, EndTIme, TFTitle;

--
Duane Hookom
Microsoft Access MVP


:

I have a table of data called table1 and it has 2 fields, OrderNum and Time.
I want to have a report that will count the number of orders between
certian time frames and I want it to appear in one result set. So if I
wanted to see the number of orders between 0:00 and 4:00, 4:01 and 12:00,
12:01 and 16:00 I would get 3 different results all in one report. If
someone could provide me an example that would be great.

Thanks