Count fields Query - Access 2010

Tanus Sacin
Tanus Sacin used Ask the Experts™
on
Hello there,

I am working on a database with Access 2010, and I need to create a report of the amount of computers, laptops monitors etc, etc that we have.

The DB have control of everything that we have, but each monitor or laptop or pc its assigned to a user, so we can what belongs to what, so , so far so good, but now that I need to create a report of the total equipment that we have I am facing a wall....

so on my table I got this field:

Asset <- ( Here I can choose for, Workstation, Monitor)
AssetType <- ( Here I chose for laptop, PC,ThinClient ,etc)

so basically I need to pick something from the Asset Field then I can pick from the assettype,

So how can run a report where I can have the total of "Laptops" or "PC"??

also, I have another filed where I keep the date of purchase and when the warranty expire, the field name are:

DoP ( for Date of Purchase)
WE ( for Warranty Expire)

So, how can I accomplish the following:

1.- Have a report with all the total "AssetType"
2.- Have a report with All the Total "AssetType" base on the WE? let say I want to see everyitng that is going to expire in 2012, so I can run the report and see it.


thanks in Advanced.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It is very difficult to help you without real table structure.
May be you can upload your DB with sample data?

Author

Commented:
upload the whole mdb file?
No, we need only sample data (some records for sample report), tables and forms, where you are filtering report data
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I am sorry for asking too many questions but, if i export the tables that i am using to an excel file, will that work for you?
It is better to export to other mdb (in this case we will see real fields type)

Commented:
Do you know how to use Query in MS ACCESS

if you know you can try this codes
1.- Have a report with all the total "AssetType"
select Asset,AssetType,Count([AssetType]) From Asset Group By Asset,AssetType


After From you need to enter your Table name, i just give a example name Asset,and i belive Asset and AssetType is correct field you given for me.

02.Have a report with All the Total "AssetType" base on the WE? let say I want to see everyitng that is going to expire in 2012, so I can run the report and see it.

select Asset,AssetType,WE From Asset Where WE >=#01/01/2012#

Commented:
sorry
02 second question answer should be
select Asset,AssetType,WE From Asset Where WE <=#01/01/2012#

Commented:
better this, its not taking 01/01/2012 data
select Asset,AssetType,WE From Asset Where WE <#01/01/2012#

Commented:
i assumed WE is datatype is Date,

Author

Commented:
ukerandi:
Thank you for your help.

The WE is a date datatype field.

The AssetType field is a text data type, so how can I count all the vuales that are equals to "Laptop" ?

Commented:
select Asset,Count([AssetType]) From Asset Where AssetType="Laptop" Group by AssetType, Asset

Commented:
or
select Count([AssetType]) From Asset Where AssetType="Laptop" Group BY AssetType

Commented:
or
select AssetType,Count([AssetType]) From Asset Where AssetType="Laptop" Group BY AssetType

Author

Commented:
Works perfeclty:

select AssetType,Count([AssetType]) From Asset Where AssetType="Laptop" Group BY AssetType

Here is the code that I am using:

SELECT Count([HOU_tblAssets.tblAssetsType]) AS Expr1
FROM HOU_tblAssets
WHERE ((([HOU_tblAssets.tblAssetsType])="Laptop"))
GROUP BY tblAssetsType;


HOU_TblAssets is the table the have:

tblAssets
tblAssetsType

How can I the WE and / or DoP? to it?

Commented:
select AssetType,Count([AssetType]) AS Assettype,WE From Asset Where AssetType="Laptop"  and WE <#01/01/2012#  Group BY AssetType ,WE

select AssetType,Count([AssetType]) AS Assettype,WE From Asset Where AssetType="Laptop"  and DoP <#01/01/2012#  Group BY AssetType ,WE





Commented:
i'm not sure what u need but if you can explain little bit more i can publish the solution
i didn't understand
How can I the WE and / or DoP? to it?

Commented:
select tblAssets,Count([tblAssetType]) AS Assettype,WE From HOU_TblAssets Where tblAssetType="Laptop"  and WE <#01/01/2012#  Group BY tblAssetType ,WE

select tblAssetType,Count([tblAssetType]) AS tblAsettype,WE From HOU_TblAssets Where tblAssetType="Laptop"  and DoP <#01/01/2012#  Group BY tblAssetType ,WE


or
you need to connect both 2 tables

Asset
HOU_TblAssets

Author

Commented:
Ok,

I have exported my 2 tables

For user info:
HOU_tblUsers

for Asset info:
HOU_tblAssets

I have relationship between the tables, so each asset is tide to an specific user, and I used the Primary Key to keep a record of what belongs to whom.

so, right now I can see how many laptops, monitors do I have, (thanks for the code), but I need to create a report, where I can show everything that will need to be replaced on 2012, ( The WE and DoP, have a 01/01/2011 format) so I would like to group everything by a year, for example

I have 200 laptops that were purchased on 2011, but when we captured that info we added the full date of purchase, like 04/05/2011 or 01/03/2011, so when I ran the report I see all the laptops that were purchased on 2011, but no in a big group, so I would like to take the year out the "full date format" and Group them by Year and tblAssetsType

that make sense?

I have uplodaded the tables that I am using.

thanks for all the help.


HOU-tblAssets.xlsx

Commented:
you can mean time below code
SELECT Asset.AssetType, Asset.Asset, Asset.WE, Asset.Dop
FROM Asset INNER JOIN HOU_TblAssets ON Asset.AssetType = HOU_TblAssets.tblAssetType
WHERE (((Asset.AssetType)="Laptop") AND ((Asset.WE)<#1/1/2012#));

i will check your excel code and come back

Commented:
this field has no data
tblAssetsWE

Author

Commented:
You right working on getting the expiration date for the equipment

Commented:
so, right now I can see how many laptops, monitors do I have, (thanks for the code), but I need to create a report, where I can show everything that will need to be replaced on 2012, ( The WE and DoP, have a 01/01/2011 format) so I would like to group everything by a year, for example



SELECT HOU_tblAssets.tblAssetsWE, Count(HOU_tblAssets.tblAssets) AS CountOftblAssets, HOU_tblAssets.tblAssetsType
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssetsType
HAVING (((HOU_tblAssets.tblAssetsWE)<"#1/1/2012#"));

Commented:
ok so thats mean so all your requirements done :)

Commented:
I have 200 laptops that were purchased on 2011, but when we captured that info we added the full date of purchase, like 04/05/2011 or 01/03/2011, so when I ran the report I see all the laptops that were purchased on 2011, but no in a big group, so I would like to take the year out the "full date format" and Group them by Year and tblAssetsType


SELECT HOU_tblAssets.tblAssetsWE, Count(HOU_tblAssets.tblAssets) AS CountOftblAssets, HOU_tblAssets.tblAssetsType, HOU_tblAssets.tblAssetsDoP
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssetsType, HOU_tblAssets.tblAssetsDoP
HAVING (((HOU_tblAssets.tblAssetsWE)<"#1/1/2012#"));

Author

Commented:
I copy / past the code and I got a mismatch error

Data Type Mismatch In Criteria Expresion

Also, I see that Count(HOU_tblAssets.tblAssets), is not tblassetsType (here is where the laptop, monitor, field is saved) ?

Thanks!

Commented:
you need to convert tblAssetsWE to Datatype as Date/time then it will work

Commented:
if not use this code
SELECT HOU_tblAssets.tblAssetsWE, Count(HOU_tblAssets.tblAssets) AS CountOftblAssets, HOU_tblAssets.tblAssetsType, HOU_tblAssets.tblAssetsDoP
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssetsType, HOU_tblAssets.tblAssetsDoP
HAVING (((HOU_tblAssets.tblAssetsWE)<"01/10/2012"));

Author

Commented:
on the table? its already as a Date/Time, I just double check.

Commented:
this will work

SELECT HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssets, Count(HOU_tblAssets.tblAssetsType) AS CountOftblAssetsType, HOU_tblAssets.tblAssetsDoP
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssets, HOU_tblAssets.tblAssetsDoP
HAVING (((HOU_tblAssets.tblAssetsWE)<"01/10/2012"));

Commented:
or try this
SELECT HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssetsType, Count(HOU_tblAssets.tblAssetsType) AS CountOftblAssetsType, HOU_tblAssets.tblAssetsDoP
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE, HOU_tblAssets.tblAssetsType, HOU_tblAssets.tblAssetsDoP
HAVING (((HOU_tblAssets.tblAssetsWE)<"01/10/2012"));

Author

Commented:
This expression was the one that was giving me problems

<"01/10/2012"

I am still seeing all the assettyps by year, if I have 5 laptops that I purchased on 2011 the query returns all 5, I want to group them all have the total per year

instead of

1 laptop 2011
1 laptop 2011
1 laptop 2011
1 laptop 2011
1 laptop 2011

have a

5 Laptop 2011


Commented:
Did you mean like this,

if you can show me out put example how ure are expecting ,may be i can do better way.
Sample-Expansion-of-Model-Horizo.xlsm

Commented:
sorry mistake,ignore the excel sheet

Commented:
what is your datatype for "tblAssetsWE"

Commented:
if its text can u changed it to Date/time

Commented:
i alrdeay add my MS Access database, Run the Query and find the answer
Database4.accdb

Author

Commented:
ok,

Thank you for everyting, you are a rock star, but still we are missing something....

I want to group everyting, so I dont want to see how many laptop expired on specific month, I want to see how many laptops on an specific year, so when I run you query, I can see how groups only the itmen where WE match, so i think I need to take the year from the WE so that instead of looking for 01/01/2011 it will look for 2011 so it will group all the laptops for 2011, correct?

Commented:
in this database 2 queries
one for only laptop query called - Count OnlyLaptop 2011
otherone for all equipment count for only 2011 called -Count every thing 2011

count for laptop only SQL is
SELECT HOU_tblAssets.tblAssetsType, Count(HOU_tblAssets.tblAssetsType) AS CountOftblAssetsType, Right([tblAssetsWE],4) AS WEdate
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsType, Right([tblAssetsWE],4)
HAVING (((HOU_tblAssets.tblAssetsType)="Laptop") AND ((Right([tblAssetsWE],4))=2011));
Database4.accdb

Commented:
hope sql is working fine

Author

Commented:
thanks! is working now!!

The problem was with one of the fields, but now is working,

last quesiton, how can i group the information by years on a "horizontal" format?

For example

Type      |     2012      |    2013   |   2014
Laptop             5                 10          30


so far, I can create the reports by total by type and group them on the same year, but when i tried to create the report for the next 3 years, .. any idea?

Commented:
This SQL for Horizontal

TRANSFORM Count(Right([tblAssetsWE],4)) AS Expr2
SELECT HOU_tblAssets.tblAssetsType
FROM HOU_tblAssets
WHERE (((HOU_tblAssets.tblAssetsType)="Laptop"))
GROUP BY HOU_tblAssets.tblAssetsType
PIVOT Right([tblAssetsWE],4);


I attached the MS Access File, Run the Query called "Crosstab" Query. you can changed it go to design view
Database4.accdb

Commented:
3 years Report you need to use SQL where cluase with

WEP => Year(#01/01/2011#) <= Year(#01/01/2011#) +3

Author

Commented:
Thanks!!!

Everything is working perfecly, one more question, and this is the last one i promise, ( tell me if you want me to create another question for this)

I have the assetype  as you know, with Latop, Computer , etc, how can I multiply the total values of the assettype for a value put it on a form?

for example,

I got 5 laptops that needs to be replaced on 2012, so that part is working....so on my form I want to add something like, Laptop value, so when I run the report, I can see:

AssetType        Total           WE         UnitPrice        Total
Laptop                  5            2012        $500              $2500

Thanks!

Commented:
hi, I atatched DB file, Run the Total Query, Anyway I add field called Price in the "HOU_tblAssets" Table


Database4.accdb

Commented:
this is SQL View

SELECT HOU_tblAssets.tblAssetsType, Count(HOU_tblAssets.tblAssetsType) AS Numberof, Right([tblAssetsWE],4) AS WEP, HOU_tblAssets.Price, CInt(Count([tblAssetsType]))*[Price] AS TotalPrice
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsType, Right([tblAssetsWE],4), HOU_tblAssets.Price
HAVING (((HOU_tblAssets.tblAssetsType)="Laptop") AND ((Right([tblAssetsWE],4))="2012"));


Hope u can give marks for me :)

Author

Commented:
that works great if I all my assets type are the same, can I mix them? for example Laptop and Computer?? the challenge is laptop and computer have a different price?

Question, can I give a 1000 points instead of 500?

Commented:
:)

Author

Commented:
that is if I have the values defined on the table...what if I want to enter the values manually? you probably ask why, for example,

we need to renew 20 laptops on 2012, so we need to ask for the money? but how much? so we a quote from a local vendor and we multiply the value by the number of laptop that need to be replaced... but that is just for reporting purposes, since we dont need to keep a record of those values on the database....

Commented:
can come with another question

Author

Commented:
Best Support EVER!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial