Display and calculated vary data types in same field

My customer has given me a real challenge - how to store either a currency or percentage data type in same data field, plus distinguish between the difference when calculating totals.  I have racked my brain and it keeps coming back this is not the best approach to store different data types in the same field.  see attach for example of final document.  I do have a separate field that identifies the field type (currency, pct)

To top it off the data will be based on several crosstab query results - see ee:

I am looking for the best approach to handling mix data types.


Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'll agree with you, that storing different data types in the same field is not at all a happy approach.
Displaying different data in the same location on a form or report can be done with a bunch of approaches.
The simplest is to have two controls directly overtop of one another.
The separate field that identifies the field type toggles the unused field's Visible property to false.
It LOOKS like everything is in the same field in the table, but that evil is avoided.
Karen SchaeferBI ANALYSTAuthor Commented:
I forgot to mention the calculation will need to be exported to an Excel sheet - which will be very complex for all the variants in the columns and what calculates what.
Karen SchaeferBI ANALYSTAuthor Commented:
plus there is the crosstab issue with compiling data based on multiple crosstabs and the field names constantly varying.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
It is always funny what a customer will "Dream Up", ...without knowing how the technology works...

What is puzzling is the *need* for his type of presentation...
Is the customer stating that they have gotten this type of design in other applications? (what are they basing this design on?)
What is the ultimate need?
Perhaps another approach would be better...

I cannot see how a person viewing this data would interpret the results?
Why is VAT a percentage for some values and a number for others?
Is this output just for this one person, ...or will other people be viewing this output?

Why not just drop the field in twice, ..once formatted as a number, and the other formatted as a percentage, ...then display both?
Ultimately the values in this filed *must* have the same dataype,...so the only thing I can suggest is to display the field twice (with the differing formats)

Karen SchaeferBI ANALYSTAuthor Commented:
what if I allow for the storage of a single numeric value and based on the field type selection ($ or %) use a Dlookup to determine which calculation is used where.  Just running the idea by you.


Karen SchaeferBI ANALYSTAuthor Commented:
VAT is a tax and depending on the city and or country how they calculate the tax.

Karen SchaeferBI ANALYSTAuthor Commented:
This is only a small portion of the bigger picture and yes this will be for multiple users/reviewers.

Karen SchaeferBI ANALYSTAuthor Commented:
if you wish I could include a copy of the various calculation needed to get the correct output.  I am creating a research database for tax revenue from around the world.  Need to calculation and display all possible combinations of tax revenue per city/Country.

Like I state before I need to combine a list of taxes & values, determine all possible combinations, then calculate a total % and Total $.  Then export to excel a complete list of cities and all related taxes with tax name in separate columns.  Creating crosstabs for the basis of other queries (hence the need for generic field Names until the final query).  This is getting more complicated as I start to understand the requirements of my client.
Jeffrey CoachmanMIS LiasonCommented:
what if I allow for the storage of a single numeric value and based on the field type selection ($ or %) use a Dlookup to determine which calculation is used where.
That is certainly an option...
I would still go back to the customer and ask where they got the idea for this unique output.
Then I would suggest the alternatives...

To me, designing an interface (as they have requested) would create a president where this customer will ask you for more increasingly "unique" outputs...

Tell them that this is not possible and let them know the alternatives that we suggested.

Certainly some requests can be treated as "challenges", (and as you know, ...everything is do-able, ...the amount of trouble you have to go through o achieve it is the deciding factor)
...but I like to draw a line between what is reasonable and what is not.

Again, to me, seeing two different formats in the same field would be confusing.
for example, how would you know if a value was invalid? (a number or percent being too high or too low)

Hamed NasrRetired IT ProfessionalCommented:
To use one field to store more than one type, you need another field to store the desired type.
fieldType  fieldValue
1                     10                   'means a fixed value 10, where 1 is a code for fixed value
2                      5                     ' means a percentage value 5, where 2 is a code for a percentage value.

Depending on fieldType, you may process your respective calculations.
Karen SchaeferBI ANALYSTAuthor Commented:
Thats the plan as of now to  use a field to identify if it is currency or percent.,  Probably use a Dlookup to validated type prior to calculation of the numeric field type.  

Thanks of all the input.  Now for the other possible issue with Cross tab queries as basis of additional queries does any one already have code that will build the crosstab on the fly.  I know I have used it in the past, but unable to located the code.  See previous post that Gustav assisted my with.

does any one already have code that will build the crosstab on the fly.
The theory for that remains the same as for any other query

Dim qdf as QueryDef
Dim NewSQL as string
Set qdf = Currentdb.QueryDefs("SomePlaceholderQueryNameThatllGetRebulitRightNow")
qdf.SQL = "NewSQL"

It's building NewSQL that's the fun part
Looking at one of my own, I could probably transmogrify it in VBA
NewSQL = "Transform " & Something
NewSQL = NewSQL & "Transform " & Something
NewSQL = NewSQL & "SELECT " & Something
NewSQL = NewSQL & "FROM " & Something
NewSQL = NewSQL & "WHERE " & Something
NewSQL = NewSQL & "GROUP BY " & Something
NewSQL = NewSQL & "ORDER BY " & Something
NewSQL = NewSQL & "PIVOT " & Something

The somethings are the hard bit, but if you have a working crosstab, you should be able to suss out what they need to be.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Also looking for code that will build a query from the crosstab results - since the column names will vary need to use generic field names this again will have to be on the fly.  what is "transmogrify "

something like gustav suggested:

SQL = "Select {0}, {1}, {2}, {3}, {4}, {5}, ... etc"

For Index = 0 To 5
    SQL = Replace(SQL, "{" & Index & "}", rst.Fields(Index).Name)

Same qoes for the crosstab where you can have:

PIVOT YourValueField In ("{10}","{11}","{12}","{13}","{14}","{15}","{16}", .. etc.);

the replace these with your column names.
what is "transmogrify "
verb (used with object), transmogrified, transmogrifying.  
1. to change in appearance or form, especially strangely or grotesquely; transform.

That's a lot of my developing :)
A clean sheet is hard to work with.
Transforming something so completely that it barely resembles the original is a lot easier.
Any which way you can to build a valid SQL string that does the job will do it.

The guts of a crosstab are those keywords -- pretty much everything else is up for grabs.
The mechanics of it would be up to you, and how your data stacks together.
It's all text.
Replace() is fine
I'd be more likely to Dim an array, and build  comma de-limited string that way
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks guys for your input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.