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.

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

ASKER

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

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?

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

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#

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

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

i assumed WE is datatype is Date,

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

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

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

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

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?

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

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?

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

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

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

this field has no data
tblAssetsWE

ASKER

You right working on getting the expiration date for the equipment

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

ok so thats mean so all your requirements done :)

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

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!

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

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

ASKER

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

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

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

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

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

sorry mistake,ignore the excel sheet

what is your datatype for "tblAssetsWE"

if its text can u changed it to Date/time

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

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?

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

hope sql is working fine

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?

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

3 years Report you need to use SQL where cluase with

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

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!

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

Database4.accdb

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 :)

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?

:)

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

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

can come with another question

ASKER

Best Support EVER!!!!