Combine the results of multiple queries into one table

I have a table that gets its entries populated through a form in MS Access 2013. I have attached an image of part of the table below. Using the StartTime entry, I created a query that displays all the transactions that occur between 8AM – 9AM, and another query to display all the transactions that occur between 9AM – 10AM, and so on. The problem is that I have so many queries. Is there a way to combine the statements in all the queries into one sql statement and ouput the result in one datasheet with column headings ‘8AM-9AM’, ‘9AM-10AM’, ’10AM-11AM’ and so on. Thank You.

Query example:

SELECT TimeValue([StartTime]) 
FROM Table1 
WHERE (TimeValue([StartTime]) Between '8:00:00 AM' And '8:59:59 AM')

Main Table

Query Result 8 AM to 9 AM

Answer

Here’s what I did that may help you or at least get you started. I created queries for each hour that you want output for (8-9, 9-10, 10-11, etc.). Then I created a final query which uses UNION for all of the queries.

Example of your queries that you probably already have:

SELECT TIMEVALUE(T1.StartTime)
FROM Table1 AS T1
WHERE (TIMEVALUE(T1.StartTime) Between '8:00:00 AM' And '8:59:59 AM');

Second:

SELECT TIMEVALUE(T1.StartTime)
FROM Table1 AS T1
WHERE (TIMEVALUE(T1.StartTime) Between '9:00:00 AM' And '9:59:59 AM');

And so on…

Exapmle of final query:

SELECT '' AS [8-9],'' AS [9-10],'' AS [10-11]
FROM [8-9AM]
UNION
SELECT T1.*,'',''
FROM [8-9AM] AS T1
UNION
SELECT '',T2.*,''
FROM [9-10AM] AS T2
UNION ALL SELECT '','',T3.*
FROM [10-11AM] AS T3
(and so on...);

Note: I had to create a blank 1st row in order for it to properly name the column on my machine. Not sure if you would have the same problem.

EDIT: Possibly a simpler way only using one table:

SELECT ID,
 SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '8:00:00 AM' And '8:59:59 AM', 
TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '8:00:00 AM' And '8:59:59 AM', ''
) AS [8-9],
 SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '9:00:00 AM' And '9:59:59 AM', TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '9:00:00 AM' And '9:59:59 AM', ''
) AS [9-10],
 SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '10:00:00 AM' And '10:59:59 AM', 
TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '10:00:00 AM' And '10:59:59 AM', ''
) AS [10-11],
SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '11:00:00 AM' And '11:59:59 AM', TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '11:00:00 AM' And '11:59:59 AM', ''
) AS [11-12],
SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '12:00:00 AM' And '12:59:59 AM', 
TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '12:00:00 AM' And '12:59:59 AM', ''
) AS [12-1],
SWITCH(
     TIMEVALUE(T1.StartTime) BETWEEN '1:00:00 AM' And '1:59:59 AM', TIMEVALUE(T1.StartTime)
    ,TIMEVALUE(T1.StartTime) NOT BETWEEN '1:00:00 AM' And '1:59:59 AM', ''
) AS [1-2]
FROM Table1 AS T1