Can this be done in a Report?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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;
 
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;


Duane Hookom said:
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


Sean said:
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
Click to expand...
Click to expand...
 
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


Sean said:
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;


Duane Hookom said:
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


Sean said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thank you

Duane Hookom said:
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


Sean said:
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;


Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top