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.
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.
Last Comment
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([Ass
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#
if you know you can try this codes
1.- Have a report with all the total "AssetType"
select Asset,AssetType,Count([Ass
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#
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#
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" ?
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
select Count([AssetType]) From Asset Where AssetType="Laptop" Group BY AssetType
or
select AssetType,Count([AssetType
select AssetType,Count([AssetType
ASKER
Works perfeclty:
select AssetType,Count([AssetType
Here is the code that I am using:
SELECT Count([HOU_tblAssets.tblAs
FROM HOU_tblAssets
WHERE ((([HOU_tblAssets.tblAsset
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
Here is the code that I am using:
SELECT Count([HOU_tblAssets.tblAs
FROM HOU_tblAssets
WHERE ((([HOU_tblAssets.tblAsset
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
select AssetType,Count([AssetType
select AssetType,Count([AssetType
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?
i didn't understand
How can I the WE and / or DoP? to it?
select tblAssets,Count([tblAssetT
select tblAssetType,Count([tblAss
or
you need to connect both 2 tables
Asset
HOU_TblAssets
select tblAssetType,Count([tblAss
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
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)="Lapto
i will check your excel code and come back
SELECT Asset.AssetType, Asset.Asset, Asset.WE, Asset.Dop
FROM Asset INNER JOIN HOU_TblAssets ON Asset.AssetType = HOU_TblAssets.tblAssetType
WHERE (((Asset.AssetType)="Lapto
i will check your excel code and come back
this field has no data
tblAssetsWE
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,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
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,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
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.tblAss
Thanks!
Data Type Mismatch In Criteria Expresion
Also, I see that Count(HOU_tblAssets.tblAss
Thanks!
you need to convert tblAssetsWE to Datatype as Date/time then it will work
if not use this code
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
ASKER
on the table? its already as a Date/Time, I just double check.
this will work
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
or try this
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
SELECT HOU_tblAssets.tblAssetsWE,
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsWE,
HAVING (((HOU_tblAssets.tblAssets
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
<"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
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
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?
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.tblAssetsTyp
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
HAVING (((HOU_tblAssets.tblAssets
Database4.accdb
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.tblAssetsTyp
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
HAVING (((HOU_tblAssets.tblAssets
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?
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],
SELECT HOU_tblAssets.tblAssetsTyp
FROM HOU_tblAssets
WHERE (((HOU_tblAssets.tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
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
TRANSFORM Count(Right([tblAssetsWE],
SELECT HOU_tblAssets.tblAssetsTyp
FROM HOU_tblAssets
WHERE (((HOU_tblAssets.tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
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
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!
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
Database4.accdb
this is SQL View
SELECT HOU_tblAssets.tblAssetsTyp
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
HAVING (((HOU_tblAssets.tblAssets
Hope u can give marks for me :)
SELECT HOU_tblAssets.tblAssetsTyp
FROM HOU_tblAssets
GROUP BY HOU_tblAssets.tblAssetsTyp
HAVING (((HOU_tblAssets.tblAssets
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?
Question, can I give a 1000 points instead of 500?
:)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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....
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!!!!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
May be you can upload your DB with sample data?