Results 1 to 4 of 4

Thread: Count Problems

  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Unhappy Unanswered: Count Problems

    Hi all

    I've created a query to dislpay multiple results in one query using UNION:

    SELECT [tbl_stock/equipment].EquipID, [tbl_stock/equipment].InternalID, [tbl_stock/equipment].Sold
    FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0));
    UNION
    SELECT [tbl_stock/equipment].EquipID, [tbl_stock/equipment].InternalID, [tbl_stock/equipment].Sold
    FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "DM***") AND (([tbl_stock/equipment].Sold)=0));
    UNION
    SELECT [tbl_stock/equipment].[EquipID], [tbl_stock/equipment].[InternalID], [tbl_stock/equipment].[Sold]
    FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].[InternalID]) Like "PR***") And (([tbl_stock/equipment].[Sold])=0));


    Now i want to create a report / query to do a count of the results ie i want it to tell me there are 4 results for "D" and 6 results for "DM" and 12 results for "PR"

    How can i do this

    Thanks
    Mike

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Hi

    I normally do things like this in hidden forms. Create a form and then put the field in and pass the recordsource with the query and then just do

    msgbox forms!test.recordsetclone.recordcount, vbinformation, "Count"

    Open the form and keep it hidden and when finished just close the form

  3. #3
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    Not sure how to do it this way, not done it this way before, what i usually do is build multiple queries to get the results, then I use something like:
    SELECT
    (SELECT Count(*) FROM [qur_15_unsold_soundcards]) AS Unsold_Soundcards,
    Count(*) AS Unsold_Network_Cards FROM qur_16_unsold_network_cards;

    Can you bulid a count into a query so that instead of it displaying the results it just displays the count?

    Thanks
    Mike

  4. #4
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    hi
    Got something like this to work once (Got numbers out of it) but thought it was so i deleted it, doh. Now i cant seem to get it back, i keep getting:

    undefined function WHERE in EXpression.

    Im not very good with SQL so any help would be appreciated, I used:

    SELECT
    (SELECT Count(*) FROM [tbl_stock/equipment]) AS Bases,
    WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0)),

    (SELECT Count(*)FROM [tbl_stock/equipment]) As Monitors,
    WHERE ((([tbl_stock/equipment].InternalID) Like "DM**") AND (([tbl_stock/equipment].Sold)=0)),

    Count(*) As Printers FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "PR***") AND (([tbl_stock/equipment].Sold)=0));

    Now im stuck
    thanks
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •