Two Different Date Query From Same Table Output In One Result (1 Viewer)

[Tamplier]

Dears, please, help to solve following problem:

In Access I have a table with some data like
DateTime__________| a | b | c | d | e |
10.03.2016 01:00:00 | 1 | 0 | 4 | 3 | 7 |
...
etc.

I need to get following results:
1. total result for dates 01.03.2016 till 10.03.2016 - Expr1: 100*sum(b)/sum(a),
total result for dates 01.03.2016 till 10.03.2016, counting only hours between 06:00-22:00 - Expr2: 100*(sum©+sum(e))/sum(d)
| Expr1 | Expr2 |
|__12__|__3__|
...
etc.

2. daily result for dates 01.03.2016 till 10.03.2016 - Expr1: 100*sum(b)/sum(a),
total result for dates 01.03.2016 till 10.03.2016, counting only hours between 06:00-22:00 - Expr2: 100*(sum©+sum(e))/sum(d)
_________| Expr1 | Expr2 |
01.03.2016 |__12__|__3__|
02.03.2016 |__10__|__8__|
...
etc.


Now I'm doing two separate queries for each Expr, for Expr2 just adding Format([CELL_CS_KPI]![Start Time];"hh\:nn\:ss"), Where Between "06:00:00" And "22:00:00"

So, my question is - How I can combine two different time queries from one source table in one query, in order to be able to prepare noremal report?

Thank you!

 

[Ranman256]

Union query.
Select * from Q1
Union
Select * from Q2

 

[plog]

Can you post some more sample data. Provide 2 sets:

A. Starting data from your table(s). Include field and table names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed it the data from A.

 

[Tamplier]

I'm new to Acces, dont know Union query.
I dont know how to put simple example. I've attached my current database.

Please, refer for tables "CELL_CS_KPI" and "CELL_PS_KPI" - as for input tables (importing through VBA).
Next I fill start and end dates, after I calculate KPI's by hour/day or total period aggregation.
Grouping based on Cluster and Operator.

Example of such query is "KPI_calc_daily".


The issue is that all that one KPI in this query "System operability" should be calculated only for hours 6-24 each day.
That's why I've qreated paralel same query with only "System operability" and put Time Where Between "06:00:00" And "23:00:00".

I dont know how to comine them in order to show real "System operability 6-24" in "KPI_calc_daily" together with other KPI's..


Do not be cruel to me, I'm only starting Access, not good in SQL. I'm more Excel/vba user)

 

[plog]

From what I see you have 2 options:

1. Start reading up on normalization (https://en.wikipedia.org/wiki/Database_normalization), do a few tutorials and then structure your tables properly. Then read up on how to write queries (http://www.w3schools.com/sql/). Then try and achieve what you want in Access.

2. Stick with Excel.

What you are doing now is using Access like its Excel Plus. It is not. Working with data in databases and spreadsheets are not similar. What you are attempting requires intermediate level database skills. If you want to stick with Access you need to build up your skillset with it.