Soem kind of cross tab query needed ?

[FL0XN0X]

Dear,

I have 3 tables:

Calendar Customer Type of reservation Id Date Id Name Id Reservation Type 1 1/01/2018 1 Matthew 1 AM 2 2/01/2018 2 Jake 2 PM 3 3/01/2018 3 Els 3 D … … … … …
Table 1: Table holding a calendar Table 2: Table holding Customers info Table 3 : Table holding information of the type of reservation Now, I got a query that populates a table that show per date which customer has which type of reservation, resulting in table: Reservation Id CalendarId CustomerId Type of Reservation Id 1 1 1 1 2 1 2 1 3 1 3 3 4 2 1 1 5 2 2 2 6 2 3 1 7 3 1 3 8 3 2 2 10 3 3 1 11 … … … Question How to make the query that lists the dates (group by) and then shows a column per type of reservation and count the number of times that type of reservation occurred for that date. For the example above, result should be: Query Id Date # AM # PM # D 1 1/01/2018 2 0 1 2 2/01/2018 2 1 0 3 3/01/2018 1 1 1 Thank you in advance. Kind regards, Bart

[isladogs]

It would be easier to answer if you can post the relevant tables from your database

[FL0XN0X]

The tables I have contains exact the same type of info, only more records. In the example above , I have provided only 3 dates, 3 customers, and 3 types of reservations. So I'm looking for the solution for the above tables. I can extrapolate/derive the correct solution then myself on the 'live' data.
Kind regards,
Bart

[isladogs]

I appreciate that you have provided the exact table structure

I'm happy to help but I'm going out in about an hour
Creating crosstab queries as 'air code' isn't easy and liable to errors.
For that reason, it is much easier to work with the actual tables.

I could create a copy of your db & paste in your sample data.
However, you already have the tables & could do that very easily.
Create a new db, copy the 3 tables, empty them & then paste in your sample data

If you do that in the next 30 mins or so, I can probably provide a solution before I go out.
If not, perhaps someone else will do it as air code or manually create the db themselves
Up to you ....

P.S. Posting a second near identical thread won't get you a quicker answer

[FL0XN0X]

it was solved in the Query section of the forum