Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query - or report? (XP, 2000)

    I am trying, unsuccessfully, to develop a query (or maybe I need a report) to count records with unique barcodes. The attached DB shows what I am trying to do. I have two queries there. One is the
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query - or report? (XP, 2000)

    You need two queries to do this (it can be done in one rather exotic query, but that is not easy to maintain.)

    1. Create a query to return all unique combinations of ProspectID, Line # and Barcode/File #:

    SELECT DISTINCT PROSPECTS_Table.ProspectID, LINE_Table.[Line #], TAPES_Table.[Barcode/File #]
    FROM PROSPECTS_Table INNER JOIN (LINE_Table INNER JOIN TAPES_Table ON LINE_Table.LineID = TAPES_Table.LineID) ON PROSPECTS_Table.ProspectID = LINE_Table.ProspectID;

    Save this query as qryUniqueCombinations.

    2. Create a totals query based on qryUniqueCombinations to count the number of Barcode/File # for each ProspectId and Line #:

    SELECT qryUniqueCombinations.ProspectID, qryUniqueCombinations.[Line #], Count(*) AS CountOfTapes
    FROM qryUniqueCombinations
    GROUP BY qryUniqueCombinations.ProspectID, qryUniqueCombinations.[Line #];

    If you like, you can use this query as record source for a report.

    Note: if possible, avoid the use of spaces and of special characters such as # and / in field and object names. They complicate things and may lead to errors.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query - or report? (XP, 2000)

    Thanks Hans. It certainly works but why didn't mine work? Your first query looked just like mine except that you used ProspectID and I used Prospect Name. Ideally, I would like to have the name ultimately show instead of the ProspectID.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query - or report? (XP, 2000)

    Hi Don,

    You can replace ProspectID with [Prospect/Area Name] in both queries I described. The essential point is that I don't try to do it all in one query: retrieve the unique combinations (without counting anything) in the first query, then use another query to count.

Posting Permissions

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