Multi-count query

[JamieRhysEdwards]

Hi all,

I'm wondering if I am able to create a query which houses all of the counts for a certain topic.

For example, I have assets which are broken down into types:

Asset Count - Count of all the assets within the table.
Desktop Count - Count of only the desktop assets within the table
Laptop Count - Count of only the laptop assets within the table

etc...

Is it possible to have this in a single query wherein I have all the counts in their own rows? Or is it a case that I would need to run a query for each type?

Thanks


EDIT :-

However... For the type, I have a lookup table wherein I have an ID field and then the Type field. so the structure would be as follows:

ID	Type	Prefix
1	Desktop	DT
2	Laptop	LT

 

[plog]

As long as you have set up your tables correctly, this is simple.

Count data by using a query - Microsoft Support

Understand ways to count data, count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access.

support.microsoft.com

 

[theDBguy]

I think you can have all the counts in one row but separate columns. If you want them all in one column but separate rows, you will need multiple queries to combine into one.

Edit: Ahh, too slow... Maybe I'm wrong. Cheers!

 

[GPGeorge]

Hi all,

I'm wondering if I am able to create a query which houses all of the counts for a certain topic.

For example, I have assets which are broken down into types:

Asset Count - Count of all the assets within the table.
Desktop Count - Count of only the desktop assets within the table
Laptop Count - Count of only the laptop assets within the table

etc...

Is it possible to have this in a single query wherein I have all the counts in their own rows? Or is it a case that I would need to run a query for each type?

Thanks

It partly depends on table structure, doesn't it?

However, assuming this is a table with an "AssetType" and an "AssetQuantity" field, then a Union query would do the trick.

 

[JamieRhysEdwards]

Sorry all, I've updated OP on how my table is kinda structured

 

[mike60smart]

Sorry all, I've updated OP on how my table is kinda structured

Are you able to upload a copy of the database without any Confidential data?

 

[JamieRhysEdwards]

Are you able to upload a copy of the database without any Confidential data?

I really wish I could, however I've split my database and the linked tables are on a network drive... meaning it would not be usable in any way if I tried to mess with it

 

[mike60smart]

I really wish I could, however I've split my database and the linked tables are on a network drive... meaning it would not be usable in any way if I tried to mess with it

We just need a copy of both the FE and BE. You don't have to change anything.

 

[cheekybuddha]

I've updated OP on how my table is kinda structured

Explicit descriptions of the tables, fields, datatypes and relationships involved would have been better!

Guessing from what you have posted, you can try the following (adapt table/field names as applicable):

To just get counts of different types of assets in the Assets table:

SELECT
  AssetTypeID,
  COUNT(*) AS CountOfAsset
FROM Assets
GROUP BY
  AssetTypeID
;

To get a list of the asset types and their counts:

SELECT
  at.AssetType,
  a.CountOfAsset
FROM (
  SELECT
    AssetTypeID,
    COUNT(*) AS CountOfAsset
  FROM Assets
  GROUP BY
    AssetTypeID
) a
INNER JOIN AssetTypes at
        ON a.AssetTypeID = at.AssetTypeID
;

A list of totals by asset type:

SELECT
  COUNT(*) AS TotalAssets,
  SUM(IIf(t.AssetType = 'Laptop', 1, 0)) AS TotalLaptops,
  SUM(IIf(t.AssetType = 'Desktop', 1, 0)) AS TotalDesktops
FROM (
  SELECT
    a.AssetID,
    at.AssetType
  FROM Assets a
  INNER JOIN AssetTypes at
          ON a.AssetTypeID = at.AssetTypeID
) t
;

Or, since you know the AssetTypeID's, more simply:

SELECT
  COUNT(*) AS TotalAssets,
  SUM(IIf(a.AssetTypeID = 1, 1, 0)) AS TotalDesktops,
  SUM(IIf(a.AssetTypeID = 2, 1, 0)) AS TotalLaptops
FROM Assets a
;

 

[Gasman]

Join the lookup table with the assets table, then group by asset and count of asset?