Solved Transform query

[Cris VS]

Hello everyone,

I have created a query in the sample database that I would like to look like the table in the picture. Taking index 135 in Event A as an example, in the query there are two lines for this combination, one with a cross in type "Welcome" and one in type "Wedding". Is there any way to show just one line for each combination of event and index, with all the corresponding types crossed in the same line?

Thank you for any help

 

[arnelgp]

TRANSFORM First("X") AS Expr1
SELECT Table1.Index, Table1.Event
FROM Type INNER JOIN Table1 ON Type.ID = Table1.Type
GROUP BY Table1.Index, Table1.Event
PIVOT Type.Type;

 

[Cris VS]

Thank you!

 

[Cris VS]

Hi again, I have a little issue with this code: when it creates the query, it creates an extra column for the indexes where no type has been registered. How can I delete or hide this column? I do not want it to appear in the crosstab query. Thanks

 

[theDBguy]

Hi. Try adding a WHERE clause to eliminate the missing index.

 

[Pat Hartman]

Add criteria to the query to not select Null or ZLS records.

Where Indexes & "" <> ""

 

[Cris VS]

But I want to display the blank indexes (row with no type marked) in the query, it's just that I don't want the column "<>" to show

 

[theDBguy]

But I want to display the blank indexes (row with no type marked) in the query, it's just that I don't want the column "<>" to show

Hmm, you may have to use the In() clause for the Column Headings.

 

[Pat Hartman]

You're going to have to give us a sample of what you are talking about.

Removing a value from the Column Headings acts as a where clause and removes the data from the query.

 

[Cris VS]

An example would be, taking the picture I posted on the first post, that there was a pair Index-Event with no type specified. In my process, first I define which indexes go with which events and in a second step, decide whether to assign a type and which ones. When building the query, it will create one column for each type and include a "<>" column to mark that "blank" pair. I want this column to disappear, so that if no type is specified that row appears blank, just as it looks in the picture. I tried this but it only allows me to filter the blank rows, not to show them blank

 

[Pat Hartman]

Is there any way to show just one line for each combination of event and index, with all the corresponding types crossed in the same line?

What you have presented in the picture looks like the results of a cross tab query. I'm beginning to think that is not the case because the pivot would have already summarized the rows. SO - the problem is that the data is not normalized. You have a repeating group and you are using data (Welcome, Wedding, Graduation, ...) as column headers. Instead of many columns, you need many rows. One for each type. Then, once the data is properly normalized, a simple cross tab query will give you the presentation results you want.

 

[Cris VS]

What you have presented in the picture looks like the results of a cross tab query. I'm beginning to think that is not the case because the pivot would have already summarized the rows. SO - the problem is that the data is not normalized. You have a repeating group and you are using data (Welcome, Wedding, Graduation, ...) as column headers. Instead of many columns, you need many rows. One for each type. Then, once the data is properly normalized, a simple cross tab query will give you the presentation results you want.

From what I have read I strongly believe it is normalized: there are independent tables for each group of repetition, foreign keys to my main table and all the attributes of each table depend on their primary key. The group type is an independent table which gets "called" through a foreign key.

I am not using the field "Type" as headers, in fact that is my question, if there is a way to plot a query where the headers are the types - only those and always those, in the sense of no "<>" field if one index is blank and no not-showing one of them if there are no indexes, just like it looks here:

(This is just a made up table I am using to exemplify what I want to get as output)
I am using the result of the query for export and that is why it is so important for me to get this appearance. Thanks a lot

 

[Pat Hartman]

If your table looks like that picture, it is NOT normalized. If you want help normalizing it we can do that. A normalized table would be:
Index
EventName
EventType

To answer your original question, you need a totals query that uses Min() as the function for the x columns. But you would loose the Event A, B, C stuff to get one row for the index with x's in the various boxes.

Index	Event	Event Type
34​	Event D	Dance
79​	Event B	Wedding
114​	Event B	Wedding
135​	Event A	Welcome
135​	Event A	Wedding
135​	Event B	Welcome
135​	Event C	Graduation
183​	Event A	Welcome
1598​	Event B	Wedding
1598​	Event C	Graduation