count unique SKUs from each warehouse

[ajm]

I am roughing up a despatch profile and need to figure out how many different products (SKUs) we send from each warehouse to each particular country. my raw data is set up in a single table. I have so far set up a query that groups my data table (AllData2) by Warehouse and then Delivery Country but count merely counts the number of lines for each country. How do I get a unique count of SKUs associated with each Delivery Country?

 

[Micron]

I presume your query is a Totals query, and it will never work with 2 or more groupings. Think of it this way:
If there are 3 warehouses, each with 1 sku and each sends to 3 countries, you can, for example, group by warehouse and show that each has a count of 1 sku. As soon as you throw country into the mix, warehouse 1 sends 1 sku to each of USA, Canada and Mexico. Now you'll split up the sku count into countries, because there is a count of 1 for each sku for each country. That is the grouping you're asking for.

In a case like this, you'll need a crosstab query. If you can't follow the wizard and create one (they confuse the h-e double hockey sticks out of me), create a table using the fields you see in my posted sql and paste the sql into sql view of query design and run it. It will give you a layout that may not be what you're after. Also try the second sql. Closer to what you need, I think, but perhaps farther away from what I think you expect it to look like.

TRANSFORM Count(tblCountSku.[SKU]) AS CountOfSKU
SELECT tblCountSku.[Whse], Count(tblCountSku.[SKU]) AS [Total Of SKU]
FROM tblCountSku
GROUP BY tblCountSku.[Whse]
PIVOT tblCountSku.[Country];

TRANSFORM Count(tblCountSku.[SKU]) AS [Total Of SKU]
SELECT tblCountSku.[Whse], tblCountSku.[SKU]
FROM tblCountSku
GROUP BY tblCountSku.[Whse], tblCountSku.[SKU]
PIVOT tblCountSku.[Country];

 

[xenou]

Distinct Sku's by country also could try something like this:

SELECT T.Country, Count(T.SKU) As CountOfDistinctSku
FROM 

(
	SELECT DISTINCT SKU, Country
	FROM
	MyTable 
) 
AS T

GROUP BY T.Country

Think of the inner query as a one query and the outer query as another - if you're not comfortable with raw SQL then in design view you can write the inner query by itself (for the distinct sku's by country) and write a second query to query the first one (for the grouping/totals).

 

[Micron]

Distinct Sku's by country also could try something like this:

SELECT T.Country, Count(T.SKU) As CountOfDistinctSku
FROM 

(
    SELECT DISTINCT SKU, Country
    FROM
    MyTable 
) 
AS T

GROUP BY T.Country

Think of the inner query as a one query and the outer query as another - if you're not comfortable with raw SQL then in design view you can write the inner query by itself (for the distinct sku's by country) and write a second query to query the first one (for the grouping/totals).

I tried to think of a subquery that would work too. However, when I run your example, it gives what you can get with a totals query and just one level of grouping. If I understand the issue, the OP wanted a count of sku grouped by country and warehouse: how many different products (SKUs) we send from each warehouse to each particular country
I tried to work with your example and add the warehouse, but I get the usual Access messages or prompts.

 

[xenou]

Let's wait for the OP to clarify then. I read his last sentence literally perhaps.

 

[ajm]

micron and xenou, thanks for stepping into the breach for my question. I am, indeed, after a count of skus per country and warehouse. I will give micron's routine a work through this morning and post back.

 

[easy2understandexcel]

You can also just use a pivot table...

 

[xenou]

You can add warehouse to grouped query also:

SELECT T.Country, T.Warehouse, Count(T.SKU) As CountOfDistinctSku
FROM 

(
	SELECT DISTINCT SKU, Warehouse, Country
	FROM
	MyTable 
) 
AS T

GROUP BY T.Country, T.Warehouse

 

[ajm]

You can also just use a pivot table...

thanks easy2understandexcel. i am working with data (500k + line items) that had to be cleansed with access first due to the way it came out of the ERP system. Its back in excel now to finish it up. )

 

[ajm]

guys, i ended up with a variation of your suggestions closely resembling xenou's last post:

SELECT suppliergroup, warehousename, located, correctedcustomertype, deliverycountry, keycustomer, customername, skuean, count(*), sum(itemsqty)
FROM alldata4correctedcustomertype
WHERE correctedcustomertype="retail"
GROUP BY suppliergroup, warehousename, located, correctedcustomertype, deliverycountry, keycustomer, customername, skuean;

With 90 different deliver to countries, the transform became quite difficult to read once I added the count of items also. thanks very much once again for offering your assistance. Its this type of help with no promise of reward that has kept me active on mrexcel for 13 years now.

cheers,

ajm