Group By formatting

[westcoaststyle]

Good morning everyone! I have a question about the Group By function in my SQL statment:

Say I'm querying on apples, grapes, pears, oranges, and melons. The problem that I'm facing is that my report is showing (for example):
apples - 10
pears - 8
oranges -6


I want to see this:
apples - 10
grapes - 0
pears - 8
oranges - 6
melons - 0

I want to display those zero values, but I don't know how.

Any ideas? I tried the Nz( ) function, but I think that's for VBA and I'm not returning a null.

Thanks!

 

[randysmid]

Hi,
Yes, it all depends on your join properties. Presuming that you have two tables, one called tblFruits, and the other called tblFruitsSold, and these are connected in your query, you will want to right click on the line connecting them, and select Join Properties. You will see 3 options, with the first being to select only those records where there is corresponding records in both tables. Obviously, you want to display all fruits regardless of whether there are any sold or not.
HTH,
Randy Smith
California Teachers Association

 

[westcoaststyle]

SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM Bases INNER JOIN Equipment ON Bases.Base = Equipment.BASE
WHERE (((Bases.Majcom)="something"))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE;

This is the query. I am linking by the base name in one table to the base name in another.

 

[swampBoogie]

SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM Bases left outer JOIN Equipment ON Bases.Base = Equipment.BASE
WHERE (((Bases.Majcom)='something'))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE

This will retrieve all records from the left table (bases) regardless of if there is any matching records in equipment.

 

[westcoaststyle]

Nope, neither LEFT OUTER or RIGHT OUTER work... they return exactly the same things - no zero values.

 

[randysmid]

Hi,
Can you switch from SQL view to Design View, then check the join properties as I indicated?
HTH,
Randy Smith
California Teachers Association

 

[westcoaststyle]

randysmid: yes, I know. I did that too. Thanks though.

 

[randysmid]

Ok,
You checked the join properties and corrected it to include all Fruits? and it still isn't working??? HTH,
Randy Smith
California Teachers Association

 

[westcoaststyle]

Yes, when I double click on the relationship line it shows me the "Join Properties". I select option '2: Include ALL records from 'Bases' and only those records from 'Equipment' where the joined fields are equal.'

I tried option 3 as well, but no luck...

 

[randysmid]

HI,
Can you tell us more about the query? For instance, is this a regular select query, update, make-table, or something else? And, is there more than two tables involved, where the join properties there must also be checked? HTH,
Randy Smith
California Teachers Association

 

[swampBoogie]

Why are you grouping on the equipment table? As all non-matching rows will have null in the equipment columns you will only get one group for these values.

 

[jebry]

Hi!

What you need is a table which list all of the NOMENCLATUREs. If you add this table to the query and join it on NOMENCLATURE making sure to use the left join or right join (whichever includes all of them) then you will get the results you are looking for. If you don't have a table like that then you need to create one because, if Access doesn't know that there can be grapes, it won't list them out on the fquery no matter what you do. From the test I just ran, you could do one other thing. Add a record for each NOMENCLATURE that is missing without an Equipment ID and Access will count that as zero.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[westcoaststyle]

SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM Bases INNER JOIN Equipment ON Bases.Base = Equipment.BASE
WHERE (((Bases.Majcom)="something"))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE;

This is the select statement. It's straight forward except for the join.

Bases Table:
this table includes ID, Base and Majcom

Equipment Table:
This table includes ID, Base and Nomenclature (among other things)

I want to know how many of each type of Nomenclature there are according to Majcoms - Majcoms are like corporations and bases are like branches of that corporation, so you will have many bases per single Majcom. In order to know what bases are part of which Majcom, I like the Bases table to the Equipment table by Base name....

Make anymore sense, or did that just make it worse??

thanks again!

 

[jebry]

Hi again!

From your last post the Equipment ID is the PK so my second suggestion will not work. So you will need a table of NOMENCLATUREs that you can include in the query with an outer join. Then you will get the zeros you want.

P.S. If the NOMENCLATUREs appear in another table you can do a Select Distinct query to use in place of the table I mentioned above.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[westcoaststyle]

Ok, I did have a NOMENCLATUREqry that was a select distinct of the nomenclature. I added that query and changed the join type to include all of the data from that query.

Here's the new SQL statement:

SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM NOMENCLATUREqry LEFT JOIN (Bases INNER JOIN Equipment ON Bases.Base = Equipment.BASE) ON NOMENCLATUREqry.NOMENCLATURE = Equipment.NOMENCLATURE
WHERE (((Bases.Majcom)='something'))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE;

The problem now is, when I try to run the query, I get 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.

???

 

[jebry]

Hi!

Evidently it wants you to do it like this:

Query1
SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM Bases INNER JOIN Equipment ON Bases.Base = Equipment.BASE
WHERE (((Bases.Majcom)='something'))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE

Query2
Select NOMENCLATUREqry.NOMENCLATURE, EquipTotal From NOMENCLATUREqry Left Join Query1 on NOMENCLATUREqry.NOMENCLATURE = Query1.NOMENCLATURE

You may need to set up an IIf statement for the EquipTotal like IIf(IsNull([Query1]![EquipTotal] = True, 0, [Query1]![EquipTotal]).

Before trying this I would change the original query to a Right Join to see if Access can resolve that.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[westcoaststyle]

Let me try this new query, but I am not displaying any of this information in Access, it is all an ASP application so that if statement may be a little different (if I have to use it).

I'll let you know about those queries.

 

[westcoaststyle]

Ok, I did the 2 queries and it's returning all of the Nomenclatures, and the EquipTotal column without any values at all...

 

[westcoaststyle]

Scratch that, I got it to work except that the 0 values are null... I'll try to format it correctly.

 

[westcoaststyle]

GOT IT!

Here's the final:
Query 1

SELECT Equipment.NOMENCLATURE, Count(Equipment.ID) AS EquipTotal
FROM Bases INNER JOIN Equipment ON Bases.Base = Equipment.BASE
WHERE (((Bases.Majcom)='something'))
GROUP BY Equipment.NOMENCLATURE
ORDER BY Equipment.NOMENCLATURE;

Query 2

SELECT NOMENCLATUREqry.NOMENCLATURE, Nz([Query1].[EquipTotal],0) AS EquipTotal
FROM NOMENCLATUREqry LEFT JOIN Query1 ON NOMENCLATUREqry.NOMENCLATURE = Query1.NOMENCLATURE;

I had to use the Nz function after all.

Thanks everyone for the help!!!!!!!!!!!!!!