Help with making a report sorted by week and then by day of the week (like a table)

[GeertG]

Hello everyone,

I have a query that shows all the orders from a specific product (in this case: 8 x 70g rood groen) for a client (Edeka) in a specific date range (from 1-1-2022 until 31-1-2022).

The query from I want to create the report is like this:

The fields Week (this is the week number of the field Date) and DayOfWeek (1: Monday ... 7: Sunday) is also from Date.

I would like to make a report from this query that shows something like a table where I have 8 columns and n rows (depending on the date range from the first picture). The first column will be the week number, the next 6 columns will be the days of the week (1: Monday until 6: Saturday) and the last one will be the total of boxes sold from this week).
Something like this:

The problem that I'm facing is that I cannot manage to get this "horizontal" view. I get something like this:

In a vertical way. Like you see I cannot manage to use the fields Week and DayOfWeek in my report. Is it because they are not "real"fields of the database?

I hope someone can help me! Thanks for reading my post!

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

There is such a thing as a columnar report, which you can search in this forum. I have never used columnar reports myself so can only tell you what to search.

As to your other question: If Week and Day of Week are in the query that feeds the report, you can use them. They are real as long as the query is open even if they only exist for that query. And if the report is driven from that query, it will stay open as long as the report is also open. So don't worry about using those fields.

By the way: Hello and welcome to the forum! For future reference, you should post questions in a "topic" forum. The introductory forum is only for welcoming new members. But not to worry... we see this so often as to be almost resigned to the idea that it will happen because we know that new members don't fully appreciate the way we work. Maybe in a few days I or someone else will move this to the Reports forum. Maybe not.

 

[GeertG]

Thank you so much for you answer!

 

[GeertG]

Hi Doc_Man, I'm still trying to find out why my crossTable is not working.
I will show you what I've done, maybe you find out what i'm doing wrong.

I have the query ready with all the information I need to make the cross table.

When I run this query, I get this:

Now I will show you the query that I used to "create" the cross table that I'm looking for:

Rijkop = row heading
Kolomkop = column heading
Waarde = value

When I execute this query I get the following error message:

The Microsoft Access database engine does not recognize [Formulieren]![FormOrders]![txtStartDate] as a valid field name or expression.​

I don't know how to fix this error. The value of txtStartDate it is already used in the qryReadyToCreateCrossTable and I worked and like i showed you above, the query get the right information that I need. So why is this second query, qryCrossTable_Kruistabel, pointing at that value?

I hope you understand what I mean, thanks for your help!
Greetings Geert.

 

[ebs17]

Formulieren => Forms
Look at the SQL view. SQL only understands English, it does not handle the confusion with the different language versions confidently.

For queries, instead of showing many images of the design, it is also better to show the SQL statement from the SQL view, as copyable code. In the end, the database engine executes the SQL statement. So you look at what you offer it.

 

[arnelgp]

you bring your report in design view and add Sorting to your report (not in the query).

 

[GeertG]

Formulieren => Forms
Look at the SQL view. SQL only understands English, it does not handle the confusion with the different language versions confidently.

For queries, instead of showing many images of the design, it is also better to show the SQL statement from the SQL view, as copyable code. In the end, the database engine executes the SQL statement. So you look at what you offer it.

Thanks your are right! I have Access in dutch, this means that he understands the word Formulieren, actually I don't even put this myself, when I choose for example the field cboCustomer, he changes it to [Formulieren]![FormOrders]![cboCustomer].

This is the sql of qryReadyToCreateCrossTab:

SELECT Orders.Id, Orders.Date, DatePart('ww',[Date]) AS Week, DatePart('w',[Date],2,1) AS DayOfWeek, Orders.Amount, Orders.Customer, Orders.Product, Orders.Code, Orders.Comment, Orders.maquina, Orders.Price, Orders.Linked
FROM Orders
WHERE (((Orders.Date) Between [Formulieren]![FormOrders]![txtStartDate] And [Formulieren]![FormOrders]![txtEndDate]) AND ((Orders.Customer)=[Formulieren]![FormOrders]![cboCustomer]) AND ((Orders.Product)=[Formulieren]![FormOrders]![cboProductsByCustomer]));

And when I run this query, it works. So I don't think that this is the problem, but maybe i'm wrong. I'm new with access.

 

[Minty]

In any crosstab query you have to define the parameters.
This includes any underlying queries.

So in your underlying query qryReadyToCreateCrossTab please use the parameters option to define your form parameters.

 

[ebs17]

when I run this query, it works

However, since this query is used as the data source for the crosstab query, it is effectively a subquery of the CT, and the form references are no longer evaluated and are therefore unknown.
Note: The Jet Engine/ACE, i.e. the database engine that executes the queries, knows VBA objects such as variables, forms, form text fields.
In simple designs, Access directly evaluates the contents of these objects and invisibly inserts these contents into the passed query in the correct way. In the described subquery this does not work anymore, and therefore the unknown expressions are queried.

There are several ways to pass the parameters:
- real parameter queries
- parameter tables
- Composing the query definition by VBA, where the contents of the VBA objects are used directly.
- Parameters via function. Functions can be evaluated via the so-called Expression Service of Jet/ACE.

 

[ebs17]

A proper parameter query directly on the table could look something like this:

PARAMETERS
   parDateFrom Date,
   parDateTo Date,
   parCustomer Text(255),
   parProduct Text(255)
;
TRANSFORM
   SUM(Amount) AS XY
SELECT
   DatePart('ww', [Date]) AS Week,
   SUM(Amount) AS ToTal
FROM
   Orders
WHERE
   [Date] BETWEEN parDateFrom AND parDateTo
      AND
   Customer = parCustomer
      AND
   Product = parProduct
GROUP
   DatePart('ww', [Date])
PIVOT
   DatePart('w', [Date], 2, 1)

Use of a parameter table (single-line table with the values to be used):

TRANSFORM
   SUM(O.Amount) AS XY
SELECT
   DatePart('ww', O.[Date]) AS Week,
   SUM(O.Amount) AS ToTal
FROM
   Orders AS O,
   tblParameters AS P
WHERE
   O.[Date] BETWEEN P.parDateFrom
      AND
   P.parDateTo
      AND
   O.Customer = P.parCustomer
      AND
   O.Product = P.parProduct
GROUP
   DatePart('ww', O.[Date])
PIVOT
   DatePart('w', O.[Date], 2, 1)

Use of functions:

' in a standard module
Public Function parDateFrom() AS Date
   parDateFrom = Forms.FormOrders.txtStartDate
End Function

' ... more

TRANSFORM
   SUM(Amount) AS XY
SELECT
   DatePart('ww', [Date]) AS Week,
   SUM(Amount) AS ToTal
FROM
   Orders
WHERE
   [Date] BETWEEN parDateFrom()
      AND
   parDateTo()
      AND
   Customer = parCustomer()
      AND
   Product = parProduct() 
GROUP 
   DatePart('ww', O.[Date])
PIVOT
   DatePart('w', [Date], 2, 1)