Avatar of Tanus Sacin
Tanus Sacin
Flag for United States of America asked on

Count fields Query - Access 2010

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.
Microsoft AccessExchangeSQL

Avatar of undefined
Last Comment
Tanus Sacin

8/22/2022 - Mon
als315

It is very difficult to help you without real table structure.
May be you can upload your DB with sample data?
Tanus Sacin

ASKER
upload the whole mdb file?
als315

No, we need only sample data (some records for sample report), tables and forms, where you are filtering report data
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Tanus Sacin

ASKER
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?
als315

It is better to export to other mdb (in this case we will see real fields type)
ukerandi

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#
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ukerandi

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

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

i assumed WE is datatype is Date,
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Tanus Sacin

ASKER
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" ?
ukerandi

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

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ukerandi

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

ASKER
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?
ukerandi

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





Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ukerandi

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?

ukerandi

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
Tanus Sacin

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ukerandi

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
ukerandi

this field has no data
tblAssetsWE
Tanus Sacin

ASKER
You right working on getting the expiration date for the equipment
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ukerandi

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#"));
ukerandi

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

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#"));
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tanus Sacin

ASKER
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!
ukerandi

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

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"));
Your help has saved me hundreds of hours of internet surfing.
fblack61
Tanus Sacin

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

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"));
ukerandi

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"));
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tanus Sacin

ASKER
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


ukerandi

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
ukerandi

sorry mistake,ignore the excel sheet
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ukerandi

what is your datatype for "tblAssetsWE"
ukerandi

if its text can u changed it to Date/time
ukerandi

i alrdeay add my MS Access database, Run the Query and find the answer
Database4.accdb
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tanus Sacin

ASKER
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?
ukerandi

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
ukerandi

hope sql is working fine
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Tanus Sacin

ASKER
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?
ukerandi

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
ukerandi

3 years Report you need to use SQL where cluase with

WEP => Year(#01/01/2011#) <= Year(#01/01/2011#) +3
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tanus Sacin

ASKER
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!
ukerandi

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


Database4.accdb
ukerandi

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 :)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Tanus Sacin

ASKER
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?
ukerandi

:)
ASKER CERTIFIED SOLUTION
ukerandi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tanus Sacin

ASKER
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....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ukerandi

can come with another question
Tanus Sacin

ASKER
Best Support EVER!!!!