Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22

    Count with if

    hey guys,
    i have a sales table and in there is Phone ID and Phone Type (32GB or 64GB) and i have a query that in there i want to count how many phones have been sold from each type of phone so for example


    http://prntscr.com/nyvixq i want it to show in the Phone Stats query that phone ID 1 sold 1 32GB and 1 64GB (in different colums)
    i tried
    Code:
    Count(IIf([Sales]![Phone Type]="32" And [Phone Stats]![Phone ID]=[Sales]![Phone ID],"1",Null))
    can someone help me achive this?
    Thanks for the helps

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    You can make a saved query e.g. qPhoneSales, like
    Code:
    qPhoneSales = SELECT  s.PhoneType, Count(s.PhoneType) As PhonesSold FROM tblSales s GROUP BY s.PhoneType
    Now whenever you need sales number, you ask it from this query.

  3. #3
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    Quote Originally Posted by ArviLaanemets View Post
    You can make a saved query e.g. qPhoneSales, like
    Code:
    qPhoneSales = SELECT  s.PhoneType, Count(s.PhoneType) As PhonesSold FROM tblSales s GROUP BY s.PhoneType
    Now whenever you need sales number, you ask it from this query.
    im not good with access and i dont understand alot of stuff and this is one of the things, what do i need to search to be able to make this?

  4. #4
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    i just relised that what im trying to is Count([Phone ID] = [Sales]![Phone ID] And [Sales]![PhoneType] = "32GB") but i dont know how to do in properly. and i didnt understand what Arvil said can someone explane to me how do i do this?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use the query builder and make an aggregate query.

    Or build a report and use its Sorting & Grouping features with aggregate calcs.

    Here is a video on aggregate query https://www.youtube.com/watch?v=YBwGcslqP00

    In your case, something like:

    SELECT PhoneID, Sum(IIf([PhoneType]="32GB",1,Null)) AS Count32, Sum(IIf([PhoneType]="64GB",1,Null)) AS Count64 FROM tablename GROUP BY PhoneID;

    Alternative is a CROSSTAB query.

    This is basic Access functionality.

    Suggest you study an introductory tutorial book.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    im sorry for not knowing this... i have a school project and we didnt really use this program at all all year so im trying to study it alone...
    and can u explane where do i put the SELECT thing? i didnt understand how to use it

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It is a query SQL statement. Do this exercise:

    1. use query designer to build a simple query that pulls some fields from a table

    2. switch to SQLView to view the SQL statement

    You can edit this SQL statement directly in SQLView or go back to DesignView.

    Get an introductory book (try used book store) and spend a couple days with it. It should cover these basics.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    Code:
    SELECT [Phone Common Things].[Phone ID], [Phone Common Things].[Phone Name]
    FROM ([Phone Types - 64GB] INNER JOIN ([Phone Types - 32GB] INNER JOIN (Companys INNER JOIN [Phone Common Things] ON Companys.[Company ID] = [Phone Common Things].[Company ID]) ON [Phone Types - 32GB].[Phone ID 32GB] = [Phone Common Things].[Phone ID]) ON [Phone Types - 64GB].[Phone ID 64GB] = [Phone Common Things].[Phone ID]) INNER JOIN (Customers INNER JOIN Sales ON Customers.[Customer ID] = Sales.[Customer ID]) ON [Phone Common Things].[Phone ID] = Sales.[Phone ID]
    GROUP BY [Phone Common Things].[Phone ID], [Phone Common Things].[Phone Name]
    HAVING ((([Phone Common Things].[Phone ID])=[Forms]![A_Main]![Stats_Input]))
    SELECT PhoneID, Sum(IIf([PhoneType]="32GB",1,Null)) AS Count32, Sum(IIf([PhoneType]="64GB",1,Null)) AS Count64 FROM Sales GROUP BY PhoneID;
    im getting error from this, did i past it correctly?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, you did not. Those are two complete queries. Can have only 1. Remove the one that was originally in the window or start with a new blank query. Did you practice using query builder as I suggested?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    yes i did, i tried making a new query i added the SQL statment and all the fields i want in the there but when i activate the query it asks me what type of Phone.Type im looking for instead of counting 32 and 64 any idea how to make so it counts it without asking for params?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It asks for input because it can't find field with that name. Your original post shows field name of [Phone Type] not Phone.Type

    Advise not to use spaces nor punctuation/special characters in naming convention. If used, must enclose in [ ].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    i also tried this and it didnt work
    Click image for larger version. 

Name:	aaa.png 
Views:	12 
Size:	2.4 KB 
ID:	38766
    im really confused on why its asking for params

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    For one thing, applying a criteria of "32GB" to a Count() result makes no sense. Would have to apply filter under the [Phone Type] field, not the calculated field.

    Essentially, your query is attempting WHERE [Sells in 32GB] = [Sales]![Phone Type]="32G" which makes no sense.

    SELECT [Phone ID], Count(*) AS [Sells in 32GB] FROM Sales WHERE [Phone Type] = "32GB" GROUP BY [Phone ID];

    However, the original suggestion using IIf() expressions should work. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    GetRektByNoob is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    22
    did noy manage to make it work here is my project can you try? please Phone Shop.zip

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This query works:

    SELECT [Phone ID], Sum(IIf([Phone Type]="32GB",1,Null)) AS Count32, Sum(IIf([Phone Type]="64GB",1,Null)) AS Count64 FROM Sales GROUP BY [Phone ID];

    This is essentially my original suggestion but now with spaces in field names and the actual table name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  2. Replies: 6
    Last Post: 06-20-2017, 08:56 AM
  3. Replies: 2
    Last Post: 06-30-2016, 06:38 PM
  4. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums