Solved Count distinct values (1 Viewer)

[trax1337]

I have 2 queries `qry_tbl_crt_bookings_by_container` which prompts the user for a container number and a second query which I want to count some values from the first query:

SELECT qry_tbl_crt_bookings_by_container.Container,
(SELECT Count(*) FROM (SELECT DISTINCT PO FROM qry_tbl_crt_bookings_by_container AS SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOfPO,
(SELECT Count(*) FROM (SELECT DISTINCT [800 Split] FROM qry_tbl_crt_bookings_by_container AS SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOf800Split,
(SELECT Count(*) FROM (SELECT DISTINCT EAN FROM qry_tbl_crt_bookings_by_container AS SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOfEAN, 
Sum(qry_tbl_crt_bookings_by_container.Pallets) AS SumOfPallets
FROM qry_tbl_crt_bookings_by_container GROUP BY qry_tbl_crt_bookings_by_container.Container;

The query above is the best I could actually make that actually returns the the correct count. However, it prompts the user for a container number two times. Any ideas on how I can fix this? I can make changes to the previous query and actual table if needed.

 

[The_Doc_Man]

You use SubQ as the alias name for three sub-queries, but they probably should be three different names, a different one for each sub-query. So SubQ1, SubQ2, SubQ3 for three subqueries, and be careful to keep them straight. Also, save yourself some typing by making MainQ the alias for the non-sub-query use of qry_tbl_crt_bookings_by_container.

You need the many different names for the different sub-queries because the way Access would execute them, all of them would have to be open as long as the main query was also open, so you have THREE "channels" (3 subs) pointing to the same record source under the same alias but for different summations with different WHERE clauses. That HAS to confuse Access because you use the main query value as a WHERE argument but you ALSO use a sub-query argument as the other comparand of the WHERE clauses.

To be honest, I'm a little surprised that it only asked twice, but maybe the first use of SubQ was accepted and the 2nd and 3rd were the triggers. But it is hard to tell by looking. The fact that it only asked twice makes me think it exists but is just difficult to find because of the confusion of FROM element names.

 

[trax1337]

Same issue, but then again ..I have no idea what I'm doing

SELECT qry_tbl_crt_bookings_by_container.Container,
    (SELECT Count(*) FROM (SELECT DISTINCT PO FROM qry_tbl_crt_bookings_by_container AS SubQ1 WHERE SubQ1.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOfPO,
    (SELECT Count(*) FROM (SELECT DISTINCT [800 Split] FROM qry_tbl_crt_bookings_by_container AS SubQ2 WHERE SubQ2.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOf800Split,
    (SELECT Count(*) FROM (SELECT DISTINCT EAN FROM qry_tbl_crt_bookings_by_container AS SubQ3 WHERE SubQ3.Container = qry_tbl_crt_bookings_by_container.Container)) AS CountOfEAN,
    Sum(qry_tbl_crt_bookings_by_container.Pallets) AS SumOfPallets
FROM qry_tbl_crt_bookings_by_container GROUP BY qry_tbl_crt_bookings_by_container.Container;

 

[Josef P.]

Access will probably not be able to pass a data field from the main source through 2 levels of subqueries.
=>

SELECT
   Q.Container,
   (SELECT Count(*) FROM (SELECT DISTINCT Container, PO FROM qry_tbl_crt_bookings_by_container) as SubQ WHERE SubQ.Container = Q.Container) AS CountOfPO,
   (SELECT Count(*) FROM (SELECT DISTINCT Container, [800 Split] FROM qry_tbl_crt_bookings_by_container) as SubQ WHERE SubQ.Container = Q.Container) AS CountOf800Split,
   (SELECT Count(*) FROM (SELECT DISTINCT Container, EAN FROM qry_tbl_crt_bookings_by_container) As SubQ WHERE SubQ.Container = Q.Container) AS CountOfEAN,
   Sum(Q.Pallets) AS SumOfPallets
FROM
   qry_tbl_crt_bookings_by_container AS Q
GROUP BY
   Q.Container;

Note: The alias names in the sub-queries can be the same, as no access from one subquery to the other is possible.


To test:

SELECT
   Q.Container, PO.CntPo as CountOfPO, [800Split].Cnt800Split as CountOf800Split, EAN.CntEAN as CountOfEAN, Q.SumOfPallets
FROM
(((select Container, Sum(Pallets) as SumOfPallets from qry_tbl_crt_bookings_by_container group by Container)  AS Q
   LEFT JOIN
   (
      SELECT Container, Count(*) as CntPo
      FROM (SELECT DISTINCT Container, PO FROM qry_tbl_crt_bookings_by_container) SubQ
      group by Container
   )  AS PO ON Q.Container = PO.Container)
   LEFT JOIN
   (
       SELECT Container, Count(*) as Cnt800Split
       FROM (SELECT DISTINCT Container, [800 Split] FROM qry_tbl_crt_bookings_by_container) SubQ
       group by Container
   )  AS 800Split ON Q.Container = [800Split].Container)
   LEFT JOIN
   (
       SELECT Container, Count(*) as CntEAN
       FROM (SELECT DISTINCT Container, EAN FROM qry_tbl_crt_bookings_by_container) SubQ
       group by Container
   )  AS EAN ON Q.Container = EAN.Container;

To think about it: is it "wise" to hide the filter by container number in a query, which is then used as a data source again?

 

[trax1337]

So the trick is to create an alias for the table and in the subquery passing the alias as the source?

 

[Josef P.]

Alias for the main source is just an abbreviation for writing, this would also work with the full name.

Compare:

(SELECT Count(*) FROM (
    SELECT DISTINCT PO FROM qry_tbl_crt_bookings_by_container AS SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container)
) AS CountOfPO

=> Query levels: ( ... ( ... = qry_tbl_crt_bookings_by_container.Container ))
vs.

(SELECT Count(*) FROM (
    SELECT DISTINCT Container, PO FROM qry_tbl_crt_bookings_by_container) as SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container
) AS CountOfPO

=> Query levels: ( ... ( ... ) ... = qry_tbl_crt_bookings_by_container.Container )

 

[trax1337]

Alias for the main source is just an abbreviation for writing, this would also work with the full name.

Compare:

(SELECT Count(*) FROM (
    SELECT DISTINCT PO FROM qry_tbl_crt_bookings_by_container AS SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container)
) AS CountOfPO

=> Query levels: ( ... ( ... = qry_tbl_crt_bookings_by_container.Container ))
vs.

(SELECT Count(*) FROM (
    SELECT DISTINCT Container, PO FROM qry_tbl_crt_bookings_by_container) as SubQ WHERE SubQ.Container = qry_tbl_crt_bookings_by_container.Container
) AS CountOfPO

=> Query levels: ( ... ( ... ) ... = qry_tbl_crt_bookings_by_container.Container )

I see, thank you for the help.