determine all possible combinations

I need to determine all combinatons of data to build a an array that will display the combinations so that calculations can be done to determine all possible combinations per city.

datasample.bmp
Columns A:G orginal data
Columnes I:J - possible combinations calclations
Col(L:R)  results of combinations for the city of Paris.

the approach I was thinking was to build on the fly a query using for loops or an array to determine all possible calculations for each row of data.  All suggestions are welcomed/

so I need to programically determine all possible combinations, see example.  I was hoping you could help me to write the code to return necessary results.

Thanks,

k
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

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

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

chaauCommented:
to get all possible combinations in SQL you need to use a query for the Cartesian product. The ANSI standard query is a CROSS JOIN. However, Access does not support it. Therefore, you need to use a comma-list of tables in the FROM clause without any joining conditions. Eg. take for example this table (Table1):
Row
A
B
C
D 
E
F
G
H

Open in new window


And this table (Table2):
Column
1
2
3
4
5
6
7
8

Open in new window


To get a list of all cells on a chess board you will use this query:

Select  Table1.Row, Table2.Column FROM Table1, Table2

Open in new window

.
I hope this will help you with your query. If not, please provide the query syntax that you already have with the sample data in the text form and we will be able to help you build it
0
Karen SchaeferBI ANALYSTAuthor Commented:
ok these  original  values will be stored in 1 table not multiples.

Thanks for you quick input.

K
0
chaauCommented:
You can join one table several times using different aliases. You want a better answer please provide sample data with the desired result
0
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!

Karen SchaeferBI ANALYSTAuthor Commented:
I did see the attachment the left side is the sample of data that I need to determine all 16 combination possibilities (right side)

I was thinking of using vba to loop thru to build query on the fly, what do you think?
0
chaauCommented:
After looking thoroughly at your screenshot I think now I understand what you are after. You need a CROSSTAB query. There is a very good Wizard in Access that does these type of queries. Try the wizard first and see if you can make it, otherwise send us a sample database with a table containing dummy data (but similar to your real data)
0
Karen SchaeferBI ANALYSTAuthor Commented:
a cross tab will not capture all combinations for each grouping..  What I need to to determine  all possible Vat opitons

If there is a Vat of 10% and also Vat = 5% and then there is a Hotel at 1% and Hotel @ 0%, also a City with a range 1% to 3% then the results would be:

Vat 10%, Hotel 1% City 3%  along with a total of the Maximun values
Vat 10%, Hotel 0%, City 3%
Vat 10%, Hotel 0%, City 0%
Vat 5%, Hotel 1%, City 3%
Vat 5%, Hotel 0%, City 3%
Vat 5%, Hotel 0%, City 0%

K
0
chaauCommented:
You can create the Crosstab query to get the Min/Max values. Then, just create another query to create the Cartesian product
0
Karen SchaeferBI ANALYSTAuthor Commented:
See attach from the original meeting notes.  not  familiar with cartesian products.datasample1.bmp
0
Karen SchaeferBI ANALYSTAuthor Commented:
I ran a crosstab an these are the results

datasample1.bmp
How do I get the correct output to display all possibilities?
0
Karen SchaeferBI ANALYSTAuthor Commented:
Here is a sample database can you demo what you are talking about please.

Keep in mind that if Min = Max use Max value, but if there is a range betwen Min/Max then display as (1-3) in the string.  see original data.
however, use the Max for calculation purposes.
Thanks,

K
Database2.accdb
0
Karen SchaeferBI ANALYSTAuthor Commented:
Also there is an issue building crosstab - and another query based on the crosstab - field names will be constantly changing so how do you build the 2nd query when you don't know the original field names?

K
0
chaauCommented:
Thanks for the sample database. I have already started creating one. Yours arrived just in time.
To get the cartesian product run this query:
SELECT Table1.CityName, Table1.[Component Name], Table1.Value, Table1.Max, Table1.Min
FROM Table1 INNER JOIN Table1 AS Table1_1 ON (Table1.[Component Name] = Table1_1.[Component Name]) AND (Table1.CityName = Table1_1.CityName);

Open in new window

I will send you the database shortly with the crosstab
0
Gustav BrockCIOCommented:
This is quite easy using subqueries in a Cartesian query:
SELECT 
    IIf(Cvat.Min=Cvat.Max, Cvat.Min & '%', '(' & Cvat.Min & '-' & Cvat.Max & ')') AS vat,
    IIf(Chotel.Min=Chotel.Max, Chotel.Min & '%', '(' & Chotel.Min & '-' & Chotel.Max & ')') AS hotel,
    IIf(Ccity.Min=Ccity.Max, Ccity.Min & '%', '(' & Ccity.Min & '-' & Ccity.Max & ')') AS city,
    IIf(Cservicefee.Min=Cservicefee.Max, Cservicefee.Min & '%', '(' & Cservicefee.Min & '-' & Cservicefee.Max & ')') AS [service fee],
    Cvat.Min + Chotel.Min + Ccity.Min + Cservicefee.Min AS [min value],
    Cvat.Max + Chotel.Max + Ccity.Max + Cservicefee.Max AS [max value]
FROM
    (SELECT * FROM Table1 WHERE [Component Name]='VAT') AS Cvat,
    (SELECT * FROM Table1 WHERE [Component Name]='HOTEL') AS Chotel,
    (SELECT * FROM Table1 WHERE [Component Name]='CITY') AS Ccity,
    (SELECT * FROM Table1 WHERE [Component Name]='Service Fee') AS Cservicefee
ORDER BY
    Cvat.Min,
    Chotel.Min,
    Ccity.Min,
    Cservicefee.Min

Open in new window

This will return (one VAT changed to 5% for the fun):
ExampleWhat your original Min, Max, and Total columns really showed, I'm not sure. The query returns Min Total and Max Total.

/gustav
0
PortletPaulEE Topic AdvisorCommented:
:( beat me to it...

Cartesian products ARE possible in Access even though the literal syntax of "CROSS JOIN" isn't supported.
0
Karen SchaeferBI ANALYSTAuthor Commented:
ok thanks for all your efforts, however the crosstab datasource does return same results, the probelm with using a crosstab as a datasource is the fix field name.  Since the field name is a variable depending on the data, how do you create a query when the original datasource field name are constantly changing?

Also the IIf(Cvat.Min=Cvat.Max, Cvat.Min & '%', '(' & Cvat.Min & '-' & Cvat.Max & ')') AS vat,
Has an error - should be if min = Max use the Max value only, if min<>Mas then use a range.

do not combine rows of data to create the range, each Vat will need all posibliities for each.  ie.

Vat       Hotel       Cityt       ServiceFee
10%         1%         1%       1%
5%           1%         1%       1%

Not Vat 5-10%

Ranges only should be used with the Min<> Max on the same row of data.

K
K
0
Gustav BrockCIOCommented:
> ..  the field name is a variable depending on the data,

How? Your example data doesn't show this.

> how do you create a query when the original datasource field name are constantly changing?

You will create an SQL string for a simple select query where you alias the varying field names to fixed field names - or you will use an SQL template where you replace default field names with the current field names.

> IIf(Cvat.Min=Cvat.Max, Cvat.Min & '%', '(' & Cvat.Min & '-' & Cvat.Max & ')') AS vat,
> Has an error - should be if min = Max use the Max value only,

How would that make any difference as Min and Max are equal?

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
Not getting difference, those are just text to describe the value - in all cases I will always use the Max Value for calculation purposes.  Please review my original post and look at the attachment.  I need to have  16 rows of data - showing 16 possbile results based on my original data.  

But, still not sure how to create a crosstab, when the Column headings will be constantly changing, hence the data source for the query, will be changing.  Even if you use the will card.

IIf(Cvat.Min=Cvat.Max, Cvat.Min & '%', '(' & Cvat.Min & '-' & Cvat.Max & ')') AS vat,
> Has an error - should be if min = Max use the Max value only,

So how would ue do the calculation if field names are in flux?

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
OK - The crosstab and the 2 additional queries based on the results of its predicessor, returns the correct results, however, still unsure how to create a crosstab and additional queries when the field names can and will change based on the data.

is there code to return the field name on the fly, maybe using the system tables to  retrieve applicable table and fieldnames?

K
0
Gustav BrockCIOCommented:
> Not getting difference, those are just text to describe the value -
> in all cases I will always use the Max Value for calculation purposes.

So what was the error, please?

> So how would ue do the calculation if field names are in flux?

I don't know. I guess field names don't "come from the wild" or just "happen".

You must be more specific what is changing and how.
We have only what you provide here to work with.

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
What I am trying to say is I the results I was looking for when using the suggested crosstab query as the basis for Query 1
 andi n Query 1 the field names are based on the field names from Table1,  since the field names have been established the actual field names are now used in the calculation for Query 2, and the final Query also relies on the original field names.  How do I built the necessary queries if the field names f rom Query will need to vary?

I have been reading up on Dynamic Crosstabs,  any input on those?

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
I was able to get the results, however see attached.

datasample.png
0
Gustav BrockCIOCommented:
Using VBA and DAO, you can read the field names and rewrite/adjust the SQL from using a set of default, generic field names (like F1, F2, ..., Fn) to the field names currently in use.

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
again the problem is the unknown factor of # of fieldnames and column count to be used in the next layer of queries.

How would that be accomplished - sample would be appreciated.

Thanks,

K
0
Gustav BrockCIOCommented:
I would write the SQL template with "enough" fields - you know the maximum count - then modify the SQL as described above and if you have, say, six fields, cut the superfluous fields.

Alternatively, if your code writes the SQL from scratch, you would just include the relevant fields.

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
I have seen some samples of code that create sql on the fly, with the approach you are suggesting, however, they are for printing reports and not displaying data.  I was hoping there was a simplier way to handle this issue without writing a bunch of code.  Note: this will not be a one off situation, I need to build the sql on the fly, so code is my only choice.

Thanks,

for all your time and input.

K
0
Gustav BrockCIOCommented:
Yes, it will take some code and handwork but - as you have the working SQL - it will be trivial.

For example, a simple method for replacing field names is like this:

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

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

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 coloumn names.

/gustav
0

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:
Thanks of all your input and great suggestions.  I wlll try this out probably in the next few weeks.  I am still in the requirement gathering phase of my project.  I was just looking into my options before writing the necessary requirement to cover this issue.

Thanks again been a great help.

Karen
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.