asked on

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

dqmq

Unfortunately, that report cannot be produced in a logically correct way because you don't really know the price of a "laptop". You know the price of a specific laptop, but not all laptops may have the same price. If you would prefer avg(price) or max(price) perhaps we can help.

Try this query:

SELECT CrossTab.[tblAssetsType], CrossTab.[2012], Avg(HOU_tblAssets.Price) AS AvgOfPrice, Avg(HOU_tblAssets.price)*[2012] AS ExtendedPrice
FROM CrossTab INNER JOIN HOU_tblAssets ON CrossTab.tblAssetsType = HOU_tblAssets.tblAssetsType
GROUP BY CrossTab.[tblAssetsType], CrossTab.[2010], CrossTab.[2012];

Tanus Sacin

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

dqmq

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

ASKER CERTIFIED SOLUTION

ukerandi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tanus Sacin

ASKER

Perfect!! as always......

would be a good idea to increase the points, sometimes 500 is not enough.