Query and Counting

[johnberman]

Hi




I have an access dbase with a single table

Im interested in reporting so need totals, I can write a query that will count the number of machines in a dept like so

SELECT [Computer Inventory].Department, Count([Computer Inventory].ID) AS CountOfID
FROM [Computer Inventory]
GROUP BY [Computer Inventory].Department;

This works fine, however I now want to add more so are two fields
To_be_migrated
Is_migrated

These are just checkboxes so I assume criteria is true or false

I need a query which will give me

The total number of machines by dept
The total marked for migration
The total migrated

Just not sure where to start

Regards

John b

[JoeM]

Yes/No fields are really boolean fields, where Yes/True = -1 and No/False = 0.

So you could just add them to your Aggregate Query, i.e.

SELECT [Computer Inventory].Department, Count([Computer Inventory].ID) AS CountOfID, Sum(To_Be_Migrated), Sum(Is_Migrated)
FROM [Computer Inventory]
GROUP BY [Computer Inventory].Department;

If you don't link the fact that they may return as negative numbers, just multiply by -1.

[johnberman]

Thanks for that, one last question - how do I just multiply by -1 ?

Regards

John B

[JoeM]

Its really as easy as you think it might be:

SELECT [Computer Inventory].Department, Count([Computer Inventory].ID) AS CountOfID, Sum([To_Be_Migrated]*-1) as CountOfToBeMigrated, Sum([Is_Migrated]*-1) as CountOfIsMigrated
FROM [Computer Inventory]
GROUP BY [Computer Inventory].Department;

[johnberman]

Appreciated

John B