Solved Multiple Columns That Need Totalling and Need to Display Potentially Multiple Currencies (1 Viewer)

LGDGlen

Member
Local time
Today, 19:10
Joined
Jun 29, 2021
Messages
229
Hi All

I have a query that needs to display:

  • Price of bought product
  • Total value of bought product
  • Price of transport product per kg
  • Total value of transport of product
  • Price product will be sold at
  • Total value of sold product
  • Value of customer claim
All of these values and prices have an associated currency that can be potentially be different and is selected from a list of currencies we deal with, GBP, USD and EUR.

I am creating a query for reporting that needs to show these values and their associated currencies and be able to total the value columns. So as an example, the data stored would be:

1635150012910.png


But displayed in the query i would like to see:

1635150064836.png

Now for different suppliers, transport and customers depending on where they are and what currency they do business in and the such like these currencies will change so its not just a matter of using 1 particular currency for a specific column it is literally based on the preceding field

hope this makes sense, i just need to understand how i format the columns based on the preceding field and make sure that they are still able to be totalled, and that total use the currency.

PLEASE NOTE: as i am aware in writing this that it might seem potentially nonsensical to have multiple currencies possible in the same column and try to total them, the query will return results based on user interaction and only show data that makes sense to total, so bought price will never have GBP and EUR displayed at the same time so the totals will make sense its just that i can't force specific currencies as each time the user queries they may choose a different product/customer/supplier so i need to make it use the currencies associated with the data

kind regards

Glen
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:10
Joined
May 7, 2009
Messages
19,169
you can show it in Datasheet view with Total on the bottom (Ribbon->records->total).
 

LGDGlen

Member
Local time
Today, 19:10
Joined
Jun 29, 2021
Messages
229
@arnelgp thats what i am trying to do but i want to understand how i can format the columns of data so that they display the correct currency on each of the columns based on the currency field that relates to them and then make sure the totals column has the same formatting

and if its not possible to do, the next best thing would be to have the currency field next to the value, but the issue i then run into is, as an example, the last 2 columns use the same currency field so how can display the same field without a header in the column multiple times?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:10
Joined
Feb 19, 2013
Messages
16,553
your second image (what you want) as far as I can see all are in the same currency for the previous column, yet you say this is dependant on the preceding column with the implication being that this can change from row to row. Similarly the totals in your example are all based on everything in the same column being the same currency. So a pretty simple solution to format the column.

But does not seem to fit with the description of what you require. Suggest provide a more relevant example to illustrate your requirement - and I my understanding is correct, what currency the totals need to be in
 

LGDGlen

Member
Local time
Today, 19:10
Joined
Jun 29, 2021
Messages
229
ok so what the user will end up with as an example will be:
1635151597592.png


Where the currency 1 uses the same currency field, currency 2 uses the same currency field and currency 3 the same currency field to display.

The data is stored as:

1635152282882.png

This is a very simplified example but different customer will potentially have different currencies for bought, transport and sold prices depending on the product, location, transport used etc

The user will supply a filter which will return values and those values will always be in the same currencies but i can't hard code or pre determine what those currencies will be until the filter is run, so the currency of the price/total field will always be determined by the related field, bought currency, transport currency or sold currency

The issue i'm having is that i don't know how to display a field that can be used multiple times without a name/header in the column. So bought currency is used for bought price and bought total value, transport currency is used for transport price and total transport value, sold currency is used for sold price, sold total value and value of any customer claim. I don't want have any text in the column header for the currency. This query is to display data on a form not a report as user may want to view multiple different filtered queries.

I might have to export the data to excel if this is not the best way to do things to allow the user to manipulate it there but was hoping i could keep things inside the front end as much as possible as i'm trying to move the users away from the excel sheet they use to run the business on at the moment and as such promoting the database as the future

hoping that gives a bit more context to the issue
 

Attachments

  • 1635151746598.png
    1635151746598.png
    20.2 KB · Views: 415

CJ_London

Super Moderator
Staff member
Local time
Today, 19:10
Joined
Feb 19, 2013
Messages
16,553
I won't get into the figures, they don't make sense to me - looks like you made a loss on customer 1

And you can't have multiple columns called Total in a database, so I don't think what you are showing as stored is correct.

You say this is a report, so in the report detail on format event, use code something like this for each column you want formatting

Code:
Select Case BoughtCurrency
    Case "USD"
         me.BoughtTotal.format="$#,##0.00"
    CASE "GBP"
         me.BoughtTotal.format="£#,##0.00"
  etc
End Select

SELECT Case TransportCurrency
    Case "USD"
         me.TransportTotal.format="$#,##0.00"
    etc
End Select
 

LGDGlen

Member
Local time
Today, 19:10
Joined
Jun 29, 2021
Messages
229
@CJ_London thanks for the update. i actually had an idea in the shower (as where all good ideas start):

User has a form to filter deliveries and select which ones they want to report on, the selection is an IN clause for the query on the delivery IDs, and the query is exported to an excel file and opened for the user to view. I'm trying to hard to do all of this in Access and whilst it might be ok to display some data, looking at the amount they want in this particular report its too many columns to reasonably fit on a page of a report or in a form

so i'll just have column headings for the currencies called C1, C2, C3 etc etc etc and then populate with the correct data from the relevant field and go from there, they shouldn't have too many issues with that as the output format would be what they are used to anyway just the way it is generated and the speed at which is generated will be better
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:10
Joined
Feb 19, 2013
Messages
16,553
whatever works for you. With regards columns to fit, consider changing font size, using abbreviations, making headers vertical and of course, using landscape.
 

LGDGlen

Member
Local time
Today, 19:10
Joined
Jun 29, 2021
Messages
229
@CJ_London thank you, yeah i do try to reduce the width of things to fit on a page, and most of the time it works, but i'm finding more and more the reports that are required to be generated are just too wide to be displayed on a form or a landscape sheet of paper unfortunately, but i have been directed as you've stated before and try to keep it in mind whilst i'm creating things
 

Users who are viewing this thread

Top Bottom