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

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
###### Who is Participating?

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

Commented:
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
``````

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

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

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

BI ANALYSTAuthor Commented:
ok these  original  values will be stored in 1 table not multiples.

Thanks for you quick input.

K
0

Commented:
You can join one table several times using different aliases. You want a better answer please provide sample data with the desired result
0

BI 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

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

BI 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

Commented:
You can create the Crosstab query to get the Min/Max values. Then, just create another query to create the Cartesian product
0

BI ANALYSTAuthor Commented:
See attach from the original meeting notes.  not  familiar with cartesian products.
0

BI ANALYSTAuthor Commented:
I ran a crosstab an these are the results

How do I get the correct output to display all possibilities?
0

BI 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

BI 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

Commented:
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);
``````
I will send you the database shortly with the crosstab
0

CIOCommented:
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
``````
This will return (one VAT changed to 5% for the fun):
What your original Min, Max, and Total columns really showed, I'm not sure. The query returns Min Total and Max Total.

/gustav
0

freelancerCommented:
:( beat me to it...

Cartesian products ARE possible in Access even though the literal syntax of "CROSS JOIN" isn't supported.
0

BI 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

CIOCommented:
> ..  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

BI 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

BI 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

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

BI 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

BI ANALYSTAuthor Commented:
I was able to get the results, however see attached.

0

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

BI 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

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

BI 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

BI 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

CIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.