Count with if

[GetRektByNoob]

ok thanks for helping!

[GetRektByNoob]

it worked but i have one little problem, if the phone doesnt have any sales it just dont show up instead of typing 0 (i changed NULL to 0) how do i fix this? if there is a way

[June7]

What you want would be fairly simple if PhoneTypes was one table instead of 2 as was suggested in your other thread. As is, gets complicated.

Query1: AllPhones
SELECT [Phone ID 32GB] AS PhoneID, "32GB" AS Type, [Inventory 32 GB] AS Inventory FROM [Phone Types - 32GB]
UNION SELECT [Phone ID 64GB], "64GB", [Inventory 64 GB] FROM [Phone Types - 64GB];

Query2
SELECT AllPhones.PhoneID, AllPhones.Type, Nz(Sum(IIf([Phone Type]="32GB",1,Null)),0) AS Count32, Nz(Sum(IIf([Phone Type]="64GB",1,Null)),0) AS Count64
FROM Sales RIGHT JOIN AllPhones ON (Sales.[Phone ID] = AllPhones.PhoneID) AND (Sales.[Phone Type] = AllPhones.Type)
GROUP BY AllPhones.PhoneID, AllPhones.Type;

[GetRektByNoob]

wait so the second query should look like this? https://imgur.com/a/mGfoJM2 why is it seperated? and suppost to replace the first query you send me?
also can you explane to me if i changed the phone id in my query to get it [Phone Common Things] table instead of [Sales] sales, shouldnt is show all the phones? instead of removing all the phones

[June7]

Yes, that is result of query 2. Yes, these replace first suggested query because you expanded the requirement.

Why is what separated?

I am not sure what you mean by 'removing all the phones'. The output shows a record for each phone type.

If this is not the output you want, then provide a mockup of what you want.

[GetRektByNoob]

Yes, that is result of query 2. Yes, these replace first suggested query because you expanded the requirement.

Why is what separated?

I am not sure what you mean by 'removing all the phones'. The output shows a record for each phone type.

If this is not the output you want, then provide a mockup of what you want.

sorry for the previews message xD for not being clear

when I said separated in the 2 query there is 2 records per phone Id

when I said removing all phones I didn't complete the sentence and I meant that its removing all the phones from the query result

[June7]

There are 2 records per phone ID because you have 2 tables for phone types. Try removing AllPhones.Type field from second query and see if that gets you desired result. Simply open in DesignView and delete field from the grid. If still not correct, provide mockup. I still don't know what 'removing all the phones' means.

[GetRektByNoob]

There are 2 records per phone ID because you have 2 tables for phone types. Try removing AllPhones.Type field from second query and see if that gets you desired result. Simply open in DesignView and delete field from the grid. If still not correct, provide mockup. I still don't know what 'removing all the phones' means.

lets say i have 10 phones and only 9 sold why is it showing only the 9 and not all the phones with the 10th phone sales being 0,0?
so instead of https://imgur.com/nJ8De4T it will be https://imgur.com/Jc57QHZ.

i tried using the querys you sent me in my Finished table and it look like this http://prntscr.com/o4gq2y and all the value are simillar for all phones even if they dont have sales
but when i create a calc total query it didnt do that after grouping up the results http://prntscr.com/o4gy6b ,any idea what can cuase that to happen in the Finish table?
do you think its possible to solve?

[June7]

I am using db you posted. Queries I suggested result in a dataset like first image with additional row for PhoneID 43 showing 0 in both columns. I don't know why you get variation from what I provided because you are not providing your queries for analysis.

[GetRektByNoob]

Phone Shop - Copy.zip here is my project

[June7]

Sorry, wrong thread.

[GetRektByNoob]

can you send me the access project that you did which shows phoneID 43 as 0,0? please so see what is the difference between them

[GetRektByNoob]

nvm fixed the problem by changing the relashan ships in the query tnx for helping!

[June7]

All you have to do is modify query [June7 - Second SQL] as suggested in post 22 and you will get same output I get.