Avatar of Tanus Sacin
Tanus Sacin
Flag for United States of America 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..
Microsoft Access

Avatar of undefined
Last Comment
Tanus Sacin

8/22/2022 - Mon

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

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


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.

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

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

Perfect!! as always......

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