Total Price after Sum in Query

[Mashknight]

Hello. I'm new to Access so this might be an easy answer. I created a Query that joins to tables of "Pipes" and "Pipes Types", what this query does is to sum the lengths of all the pipes depending on their type. Now I have another table called "Pipes Prices" that indicates the unit price of each pipe type. What I want to do now is join this table to the query that I already have so it also shows the prices of each pipe types and their total price depending on the sum of length per type.


Thank you.

[orange]

Please post a picture of your table designs.
Need more info on Pipe lengths and Prices.

Welcome to the forum.

[Mashknight]

Hello. Thank you for your reply and welcome. Here are the images of the query and the table I want to incorporate.

[orange]

Your prices seem extremely high--are these per so many feet??? What/How many units of 4" would cost ~ $240K??

Seems you need

Item, Cost per unit and Number of units purchased to get TotalPrice

Consider
4" Pipe(PVC Sanitario bajante), at a cost of $XX per 10 ft. , 250 ft purchased would have total Price

($xx/10)*250 = $25XX

[Mashknight]

The units are in meters and the prices in colombian pesos. The exchange currency at the moment is 3000 pesos = 1 US Dolar.

How do I add that calculation? My goal is to do this directly in Access everytime I change something from the original model on Revit and to avoid exporting the Access Database to Excel.

[orange]

For clarity,

You post shows
4" Pipe(PVC Sanitario bajante) with price $239,182.00.
What exactly is the Price per meter?
How many meters were purchased?

(PricePreMeter*NumberOfMeters)/(columbianPesos/US Dollar)

[Mashknight]

For clarity,

You post shows
4" Pipe(PVC Sanitario bajante) with price $239,182.00.
What exactly is the Price per meter?
How many meters were purchased?

(PricePreMeter*NumberOfMeters)/(columbianPesos/US Dollar)

Thank you for your response. To clarify that statement means: the 4" PVC Sanitario Bajante has a price of $239,182 per meter. In this case, as shown in the query, were bought a total 39.93ml. The column of Total Price that I want to add would say that this type of pipe will be $239,182/ml x 39.93 ml =$9,550,537.26 pesos (around 3000 US Dollars).

How can I get this information base on the query and the pipe price table I have? Or is it not possible?

[orange]

It sounds like you may have an inventory of Pipe (different types).
And you sell different lengths of different Pipes.
Also, the exchange rate may change from tie to time, so you may need to keep track.

If this isn't correct, then give an overview of how the pipe, Prices and exchange rate fits in your set up.
If you post a copy of the database(only need a few records), I'll see if I can get a query to do the calculation.

[Mashknight]

Here is a screenshot of the Design View. I don't need the exchange rate because I need this information in colombian pesos, I mentioned it because you were inquiring about the currency.

I have pipes types, different lengths in meter, now I need the total price of those lengths according to their respective price.



Thank you. You have been very patient with my lack of knowledge about this.

[orange]

Do Pipe Prices vary depending on Length? If not, why do you have overall Size in the PipePrices table?

Your table and field names should not have embedded spaces --(will cause syntax errors).

How often do you Prices change? You will need to relate PipePrices to your other tables.

I still don't understand the business --do you sell pipe to customers?

[Mashknight]

Hello. I'm a Construction Manager, specifically BIM Manager. The reason I started using Access was to export a database ODBC from exporting the quantities from Revit. The traditional way is through Excel but every time I update the model, I had to export all the data, organize it and get the information that I have it automatically with Access. My idea is to have this information immediately which I already do with Access, now I just wanted to have the total price as I continue to model from Revit. Now, I can export the data from Access to Excel to get this total price column but I just wanted to see if there was a way to avoid that.

The prices comes from a quota so they will not change but the sum of the lengths do. The information of the query is the total sum of the lengths of different types of pipes. From what I have seen from Youtube videos is to use the Builder from the Design View but I am having difficulties with creating the formula.

[orange]

I am not familiar with Revit.
What field in your table(s) identify the Pipe for which this is the PricePerMeter?
Perhaps the info I'm looking for is hidden in the graphic you posted in post #9.

[Mashknight]

It would be the Keynote. In the Table of Pipe prices appears the type of pipe which is under the Keynote Column and the price per meter depends on the overall diameter.

[orange]

Can you post a copy of your database with only a few records in each table?

[Mashknight]