I need that my query show me all values, 0 or null included

[scumposse]

good night to all,

I have a database with some tables. One of them is "Groups" and other "Types of failures"



After collecting a lot of data, I need to export to Excel a table to calculate the average number of faults per group. And this is where I have the problem.

In this query, I get all the failures and types of failures but I can not find the groups that have had 0 failures.




This is what I get




but this is what I want to get, with 0 values



Do you know how I can get it?

thank you very much everyone and greet regards

[Micron]

Try outer joins between your tables (double click on the join line and change to option 2 or 3, probably for both tables). I wish I could be more specific, but not being able to read the text in the images is a problem for me. I'll guess that the left most join should point to the middle table, meaning you want "all groups" and those records which match where the fields were joined. The text hints you'll see when looking at the join descriptions should help a bit, although not always are they crystal clear to some. I suspect that for table fields that have no counts, you have 0 as a default value to ensure you at least have a record that the count/value is reported as being 0.

[isladogs]

Alternatively create a second unmatched query listing all records from the first two tables where there is no matching data in the third and add a 'count' field with value =0
Now create a UNION query using the first query and the new one.
That's it

The reason the images are unclear is that each contains lots of unnecessary parts of the screen with the section needed being tiny.
In future please crop the images before uploading so the section we need is legible.

[scumposse]

Many thanks. I am going tu try it.
Sorry for the Photos
Many thanks. I'm going to try it.

[June7]

If you have records in Base Vorcheck with 0 value then they should be showing. How did you get that Total field to show with 0's for each Grupo/Tipo combination?

The query design does not show table Copia de Grupos but that table does seem to show the desired output. How is this table populated?

You may need a dataset of all possible combinations of Grupo and Tipo de falko values. This can be accomplished with Cartesian query - every record of each table is joined to every record of other table:

SELECT Grupos.*, Tipos_Falko.* FROM Grupos, Tipos_Falko;

Then join Base Vorcheck table to that dataset with compound join.

Cartesian query can perform very slowly with large dataset.

The other option is to create records with 0 value in Base Vorcheck.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[scumposse]

If you have records in Base Vorcheck with 0 value then they should be showing. How did you get that Total field to show with 0's for each Grupo/Tipo combination?

The query design does not show table Copia de Grupos but that table does seem to show the desired output. How is this table populated?

You may need a dataset of all possible combinations of Grupo and Tipo de falko values. This can be accomplished with Cartesian query - every record of each table is joined to every record of other table:

SELECT Grupos.*, Tipos_Falko.* FROM Grupos, Tipos_Falko;

Then join Base Vorcheck table to that dataset with compound join.

Cartesian query can perform very slowly with large dataset.

The other option is to create records with 0 value in Base Vorcheck.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Thank you very much for your answers.

Finally I have created a second table with values ​​at 0.




and I've made a union query with the first table








and it is the result. The union query creates new records but does not add the values ​​of the two tables.



Thank you so much for everything.


it is the result. The union query creates new records but does not add the values ​​of the two tables.

[isladogs]

Two errors
1. The query with zero values should only be for those records not in the main query. See my previous post.
2. Remove the blank lines before and after the word UNION in the union query

[scumposse]

Two errors
1. The query with zero values should only be for those records not in the main query. See my previous post.
2. Remove the blank lines before and after the word UNION in the union query

Thanks a lot Ridders.

The problem that when I try to list the null values, I do not find any.

[isladogs]

Your query uses INNER joins so it only lists records found in each table
So by definition searching for NULL will fail

Unmatched queries MUST be done using outer joins - so that records in one table but not the other can be found
Access has an unmatched query wizard - I suggest you use it for this purpose

[scumposse]

The SQL statement could not be executed because it contains ambiguous outer joins
Error Description:	The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement

when I do this




access generates this error

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement

Do you know how I could solve this error?

[isladogs]

Your outer join arrow can't work the way you have set this up
The complication is that you have 3 tables.

The problem I have is not understanding the language in your screenshots
Did you use the wizard?

You either need an outer join in the same direction for both links where the table with NULL values in the count field is at the end of the arrow 'flow'
OR possibly an inner join for the first two tables with an outer join to the table with NULL values in the count field so that is at the end of the arrow 'flow'.
Hard to put into words - hopefully you can grasp what I mean

If not post a stripped down version of your database explaining which tables & queries to look at

[scumposse]

Your outer join arrow can't work the way you have set this up
The complication is that you have 3 tables.

The problem I have is not understanding the language in your screenshots
Did you use the wizard?

You either need an outer join in the same direction for both links where the table with NULL values in the count field is at the end of the arrow 'flow'
OR possibly an inner join for the first two tables with an outer join to the table with NULL values in the count field so that is at the end of the arrow 'flow'.
Hard to put into words - hopefully you can grasp what I mean

If not post a stripped down version of your database explaining which tables & queries to look at

Thank you very much, I will try something of what you tell me. Great Regards.

[isladogs]

Thank you very much, I will try something of what you tell me. Great Regards.
Thank you very much, I will try something of what you tell me. a greeting

I'm seeing double!!!

Have a look at this post about ambiguous joins as it may help you understand what to do
https://www.accessforums.net/showthr...356#post390356

[scumposse]

I'm seeing double!!!

Have a look at this post about ambiguous joins as it may help you understand what to do
https://www.accessforums.net/showthr...356#post390356

hahahaha

Thank you so much for everything. You have a nice dog!

[isladogs]

You're welcome.
Isla has the dogged approach to problem solving....
Hopefully you'll be able to solve it now.
If not, post your database.