How Do I Generate a Report Showing All Possible Combinations of Data in Excel or Access
Hi Guys, I'm stumped and need an answer quick - am really hoping you can help??!!
I am not fussed whether I end up working in Excel or Access - but I can't use any other application so...
I have a table containing data values and I need to create a second table, or report, showing all possible permutations or combinations of the data items. For example
Colour Size Packaging
Red Large Bubblewrap
Blue Medium Foil
Yellow Small Shrinkwrap
This table could contain multiple columns and multiple rows.
The automated output I am looking for would show
Red | Large | Bubblewrap
Red | Medium | Bubblewrap
Red | Small | Bubblewrap
Red | Large | Foil
Red | Medium | Foil
Red | Small | Foil
Red | Large | Shrinkwrap
Red | Medium | Shrinkwrap
Red | Small | Shrinkwrap
Blue | Large | Bubblewrap
Blue | Medium | Bubblewrap
Blue | Small | Bubblewrap
Blue | Large | Foil
Blue | Medium | Foil
Blue | Small | Foil
etc, for all possible combinations.
I have used VBA in the past, so wouldn't struggle if I had to use it again.
Any suggestions? Please????
I am not fussed whether I end up working in Excel or Access - but I can't use any other application so...
I have a table containing data values and I need to create a second table, or report, showing all possible permutations or combinations of the data items. For example
Colour Size Packaging
Red Large Bubblewrap
Blue Medium Foil
Yellow Small Shrinkwrap
This table could contain multiple columns and multiple rows.
The automated output I am looking for would show
Red | Large | Bubblewrap
Red | Medium | Bubblewrap
Red | Small | Bubblewrap
Red | Large | Foil
Red | Medium | Foil
Red | Small | Foil
Red | Large | Shrinkwrap
Red | Medium | Shrinkwrap
Red | Small | Shrinkwrap
Blue | Large | Bubblewrap
Blue | Medium | Bubblewrap
Blue | Small | Bubblewrap
Blue | Large | Foil
Blue | Medium | Foil
Blue | Small | Foil
etc, for all possible combinations.
I have used VBA in the past, so wouldn't struggle if I had to use it again.
Any suggestions? Please????
Last Comment
adraughn
you want to use a crossjoin.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
in your case, these items should be three different tables
tblLU_Color
tblLU_Size
tblLU_Packaging
If you don't want to split it out, you can create make table queries for each, breaking ouot the columns. Then you can create your crossjoin query.
tblLU_Color
tblLU_Size
tblLU_Packaging
If you don't want to split it out, you can create make table queries for each, breaking ouot the columns. Then you can create your crossjoin query.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I was under the impression that I had responded to the comments... none of them helped! I have solved the problem by researching at the local library. I am happy for the comment to be closed, but am not prepared to assign points. Thanks guys.
If the asker can show how he got his results using a different approach than the Cartesian or Cross Join suggested, I would be happy to let him keep his points. Using his example, the resulting recordset should have 27 records.
Of course, showing that the suggested approached did not work.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
