Count Multiple Fields - Crosstab -Access 2010
Hello There,
I have a Access DB that I use to control my assets.
Right now, I have a crosstab query that I can see what items are going to expire and the total of them and I can see them on the following formats:
Item Type Total Year
Laptop 50 2012
This is telling me that I have 50 laptop that I need to buy for 2012, so how can I add a price to my report? for example, I need to create a report that I show to my Financial Manager, so this is what I am looking for:
Item Type Total Year Unit Price Total
Laptop 50 2012 1000 50000
I was able to get that working ( with a great help of ukerandi ) but now, is that I need to have multiple items with multiple prices on the same report...., look at the example:
Item Type Total Year Unit Price Total
Laptop 50 2012 1000 50000
Computer 5 2012 50 2500
I have a form, so I want to add those values from the form, since those values are just to have an IDEA on how much the replacement of the equipment will cost, we dont need to keep the value store on a table or somewhere else....
Thank you!!
I have attached of copy of the working DB as a reference..
Database4-2.accdb
I have a Access DB that I use to control my assets.
Right now, I have a crosstab query that I can see what items are going to expire and the total of them and I can see them on the following formats:
Item Type Total Year
Laptop 50 2012
This is telling me that I have 50 laptop that I need to buy for 2012, so how can I add a price to my report? for example, I need to create a report that I show to my Financial Manager, so this is what I am looking for:
Item Type Total Year Unit Price Total
Laptop 50 2012 1000 50000
I was able to get that working ( with a great help of ukerandi ) but now, is that I need to have multiple items with multiple prices on the same report...., look at the example:
Item Type Total Year Unit Price Total
Laptop 50 2012 1000 50000
Computer 5 2012 50 2500
I have a form, so I want to add those values from the form, since those values are just to have an IDEA on how much the replacement of the equipment will cost, we dont need to keep the value store on a table or somewhere else....
Thank you!!
I have attached of copy of the working DB as a reference..
Database4-2.accdb
Last Comment
ASKER
well, the thing is I should be able to put the price on the form, and the query will ready it from the form, that way I dont need to keep the price on a table, ( we dont nee it).
the price for the laptop will always be the same ( budget purposes).
The challenge is have the computer and laptop on the same report so that I can have something like this:
Item Type Total Year Unit Price GrandTotal
Laptop 50 2012 1000 50000
Computer 5 2012 50 2500
The Unit Price field will be read from the Form and the report or query will multiply Total*unitPrice = GrandTotal
the price for the laptop will always be the same ( budget purposes).
The challenge is have the computer and laptop on the same report so that I can have something like this:
Item Type Total Year Unit Price GrandTotal
Laptop 50 2012 1000 50000
Computer 5 2012 50 2500
The Unit Price field will be read from the Form and the report or query will multiply Total*unitPrice = GrandTotal
So, you've got a theoretically unlimited number of ItemTypes in your table. Do you expect a field on your form for each?
Whether it's an Access table or some other mechansim, it will take the form of ItemType, Price pairs. I think you are swimming against the current when it is so easy to maintain that information in a table and generate your report from that.
However, if you want to "hardcode" as much on your form, I'll pave the way to hell for you. Checkout Form1 in the attached db.
I just can't stand it! So, I also tossed together somthing quick to show how a table can help. Check out Form2.
Database4-2.accdb
Whether it's an Access table or some other mechansim, it will take the form of ItemType, Price pairs. I think you are swimming against the current when it is so easy to maintain that information in a table and generate your report from that.
However, if you want to "hardcode" as much on your form, I'll pave the way to hell for you. Checkout Form1 in the attached db.
I just can't stand it! So, I also tossed together somthing quick to show how a table can help. Check out Form2.
Database4-2.accdb
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
Perfect!! as always......
would be a good idea to increase the points, sometimes 500 is not enough.
would be a good idea to increase the points, sometimes 500 is not enough.
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
Try this query:
SELECT CrossTab.[tblAssetsType], CrossTab.[2012], Avg(HOU_tblAssets.Price) AS AvgOfPrice, Avg(HOU_tblAssets.price)*[
FROM CrossTab INNER JOIN HOU_tblAssets ON CrossTab.tblAssetsType = HOU_tblAssets.tblAssetsTyp
GROUP BY CrossTab.[tblAssetsType], CrossTab.[2010], CrossTab.[2012];