1

Topic: Cross-tab query on hours and days

Kind time of the days, respected ! There was a task to make sampling of basis that it was visible, how much intensively, in what time of days during year go orders. I found an output such: to make cross-tab query, in which lines hours (from 0-23), and in columns day of year (from 1-365). I implemented it is such request:

TRANSFORM Count (public_TS_Orders. ID) AS [Count-ID]
SELECT DISTINCT DATEPART ("h", public_TS_Orders. DeliveryTime) as 123
FROM public_TS_Orders
GROUP BY public_TS_Orders. DeliveryTime
ORDER BY public_TS_Orders. DeliveryTime, public_TS_Orders. DeliveryTime
PIVOT DatePart ("y", public_TS_Orders. DeliveryTime);

As a result, on days a scale correct, and here hours, reaching to 23, are started over again from zero. In general, for visualization I put a picture.
Stick please with a nose where I was mistaken. Two days Google I torment... Thanks!

2

Re: Cross-tab query on hours and days

StasEnso wrote:

There was a task to make sampling of basis that it was visible, how much intensively, in what time of days during year go orders.

Well also what there it will be visible? Eyes run up every which way to search for maxima and minima...
There is order time, it is one request with grouping on hours, the total by an amount of orders and to sort the total of orders by decrease or increase, accordingly above there will be the productive hours below any, well or on the contrary...
At me many use this report, some shops change the operation schedule, in peak open additional cash register etc.

3

Re: Cross-tab query on hours and days

vmag wrote:

it is passed...
Well also what there it will be visible? Eyes run up every which way to search for maxima and minima...
There is order time, it is one request with grouping on hours, the total by an amount of orders and to sort the total of orders by decrease or increase, accordingly above there will be the productive hours below any, well or on the contrary...
At me many use this report, some shops change the operation schedule, in peak open additional cash register etc.

Thanks for the answer. Here the big role is played by the seasonal factor. Yes that there! Even the rain doubles orders. The analytics here is more thin, than is simple peak values, and that I do that now - only preparation for later processing. Your question I will answer - that to eyes not to run up, it is enough to make  illumination. For example from red to green, depending on loading. I hope that to the full answered your question. Answer also you mine. Thanks!

4

Re: Cross-tab query on hours and days

StasEnso;
Try so

TRANSFORM Count (t1.ID) AS [Count-ID]
SELECT t1.yday
FROM (SELECT public_TS_Orders. ID, DATEPART ("y", public_TS_Orders. DeliveryTime) AS yday, DATEPART ("h", public_TS_Orders. DeliveryTime) AS hday
FROM public_TS_Orders) AS t1
GROUP BY t1.yday
ORDER BY t1.yday
PIVOT t1.hday;

5

Re: Cross-tab query on hours and days

6

Re: Cross-tab query on hours and days

sdku, I am possible incorrectly expressed. Formats quite suit me. Does not arrange that the table is not restricted to 24 hours, and goes further downwards. The idea in building year across from 1 till 365 days, and on a vertical hours everyone days.

7

Re: Cross-tab query on hours and days

Try to group on DATEPART ("h", public_TS_Orders. DeliveryTime) and to remove DISTINCT (for the first time I see such in cross-tab query).
. It is more logical and more convenient to receive days on verticals and hours across.
. Value of hours to receive function HUUR ()  easier! [/quote]

8

Re: Cross-tab query on hours and days

It is necessary for you:
1. To solve 0:30 these are 0 hours or 1 hour
2. To create type expression:
IIF (format (; "hh:nn") <"00:31"; 0; format (; "hh:nn") <"01:31"; 1; format (; "hh:nn") <"02:31"; 2......))) (I do not remember how many nestings admits IIF if  concatenation - in VBA is easier)
3. This expression (field) to use as row headings (columns-as solve)
4. Usage of parameters for selection on years and-or months (to function Year, Month) is possible

9

Re: Cross-tab query on hours and days

As always, the failure from the designer of requests (which only with sense forces down) and creation of request by "pens" helped. Did without nested queries and other comparing, conditions and expressions. If it is required to whom - take, it is not a pity:) All thanks for the help!

TRANSFORM count (public_TS_Orders. ID)
SELECT DISTINCT DATEPART ("h", public_TS_Orders. DeliveryTime)
FROM public_TS_Orders
GROUP BY DATEPART ("h", public_TS_Orders. DeliveryTime)
PIVOT DATEPART ("Y", public_TS_Orders. DeliveryTime);

And result:

10

Re: Cross-tab query on hours and days

StasEnso wrote:

If it is required to whom - take, it is not a pity:)

I do not want you to afflict, but  it is required...
1. Distinct, in your case, here it is absolutely not necessary, as to you Anatoly (at Group By is as "oil oil") correctly noted
2. See a picture

11

Re: Cross-tab query on hours and days

StasEnso;
Well, and so, for interest - as IT at you could earn in this connection?
Access specifications

12

Re: Cross-tab query on hours and days

zimkon, at you in a cap format Access 2010, at me - 2007-2016. Probably in it a difference. About DISTINCT - thanks, removed.

13

Re: Cross-tab query on hours and days

zimkon, it is guilty, 2000, instead of 2010.

14

Re: Cross-tab query on hours and days

StasEnso wrote:

zimkon, at you in a cap format Access 2010, at me - 2007-2016. Probably in it a difference. About DISTINCT - thanks, removed.

Here I resulted restrictions and for your format.
In particular, as IT can work for you, if
"Applies To: Access for Office 365, Access 2016, Access 2013, Access 2010, Access 2007
...
Query
...
Number of fields in a recordset
255
..."???
Very much and very much to me it is doubtful... Or you do not finish speaking something (In particular, you write - "at me - 2007-2016". Such, I think, basically be specified cannot - too different formats in this range, something one, either 2007, or 2016)

15

Re: Cross-tab query on hours and days

zimkon, one question to you. Only one. What your way can be my motive to draw the kilometer table and all to deceive? Here what for to me it? Do not ignore this question please, answer me it. And yours I will answer with a picture:

16

Re: Cross-tab query on hours and days

zimkon, as you can see, a format or generation of a format 2000 to which you use, is even not previous...

17

Re: Cross-tab query on hours and days

StasEnso;
I did not state, I expressed only doubts since I know that the DB 2007 formats, for example, can essentially differ from 2016, I do not have possibility to check up everything about what I expressed doubts (since is not present  2016). But I think, as the Microsoft is not present need to mislead users that they wrote in Access specifications for these versions. If I all am mistaken, let I will be corrected by those who has possibility to check up your request in 2016 . (And in case of success (columns can be more than 255 in request) we will know that ...  Microsoft in the article (that would be a nonsense).

18

Re: Cross-tab query on hours and days

StasEnso;
And then, in my opinion, it is not necessary to confuse a file format (mdb, accdb) and a database format. Followed make a screen of the main window of a DB (as it has been made on a screen at me, I used for the example  2003, and at it the DB format on-default costs 2000) that however does not belittle those doubts which have been stated by me earlier.

19

Re: Cross-tab query on hours and days

zimkon, I also am the one who launched on Access 2016, than my check is not pleasant to you? Concerning the basis, it on PostgreSQL is constructed, Access I through ODBC connected to it.

20

Re: Cross-tab query on hours and days

StasEnso wrote:

zimkon, I also am the one who launched on Access 2016, than my check is not pleasant to you? Concerning the basis, it on PostgreSQL is constructed, Access I through ODBC connected to it.

So that you to people fool brains - "basis Access, basis Access..." There is at you no basis Access, and there is a jacket for operation from an indirect DBMS. . Would know, even to answer did not begin (that, probably, and I will fulfill - I "will hammer" henceforth on excessive responsiveness, and that as in that history - itself it is still guilty it will appear).

21

Re: Cross-tab query on hours and days

zimkon wrote:

it is passed...
So that you to people fool brains - "basis Access, basis Access..." There is at you no basis Access, and there is a jacket for operation from an indirect DBMS. . Would know, even to answer did not begin (that, probably, and I will fulfill - I "will hammer" henceforth on excessive responsiveness, and that as in that history - itself it is still guilty it will appear).

In my opinion, you to yourselves fool a head since do not want to check up the basis on access 2016, and confuse also yourself and people. Today already time is not present, and tomorrow  similar basis in most Access and I will make request. Not for you, and for people whom you confuse.

22

Re: Cross-tab query on hours and days

StasEnso;
Drag , show to everything as "eggs learn a hen". An example to a forum do not forget to put (for people) smile))

23

Re: Cross-tab query on hours and days

zimkon, please, observe culture of dispute. It is impolite itself you conduct, and the shade falls on all forum. And under the specification that you gave, too not all so is unambiguous:

24

Re: Cross-tab query on hours and days

StasEnso;
To argue with you - it is a lot of honor for you. Hold an example, I hope you will understand, as to 2016 format to transform.

25

Re: Cross-tab query on hours and days

zimkon wrote:

StasEnso;
To argue with you - it is a lot of honor for you. Hold an example, I hope you will understand, as to 2016 format to transform.

Yes I in general also was not imposed, told all thanks and left. In other, me I became already curious also here I will a little be delayed;)
By the way, what for to me these conversions? I unless about something asked?