Alphabetizing a column in a report, made of multiple controls. 2

[MSWhat]

I have sort of a tricky question, that I'm guessing will require a clever work-around that I can't think of. The actual substance is confusing enough, so I'll use an anologous type of example.

Suppose I am keeping track of the different colors of popsicles offered by different brands. The brand name (ie Good Humor, Rising Star, Klondike, Dreamsicle, etc) is serving as the primary key. Each brand may have up to 3 colors, but as few as zero. I know the table isn't normalized, but here's what the table basically looks like:

Brand Name Color 1 Color 2 Color 3

Good Humor red blue orange
Rising Star green red yellow
Klondike
Dreamsicle green

Notice that they may have a different number of popsicles offered, and also that while Rising star and Good Humor may both offer red popsicles, it was arbitrarily assigned that "red" was entered into Good Humor as Popsicle 1 and was entered as Popsicle 2 for Rising Star.

Now, I'm trying to make a report that is either sorted or grouped (it doesn't really matter, though grouped would be better) by Popsicle Color. What I've done is to put the controls of Popsicle Color 1, 2, and 3 all in a column on my report, so when you view it in report-view, it looks as if they're all part of the same control. The only problem, it alphabetizes by color within each control (Color 1, Color 2, and Color 3) so it would go Green, Red, [all of the colors of "Color 1"], blue, red, [then alphabetized by all of the colors within "Color 2"], orange, yellow [those of "Color 3"].

Is there a way to put Color 1, 2, and 3 all into one column, and have that column be ordered aplphabetically? Or, is there a way to alphabetize by 3 columns? Similrarly, if I wanted to group by color, I can't group by Color 1, 2, and 3--all as part of the same grouping level--I must only group by Color 1, Color 2, or Color 3.

I'm not sure if this makes ANY sense, but please don't hesitate to ask questions and I appreciate any help you might be able to offer.

I'm very lost.

 

[MSWhat]

I tried, though can't seem to make work, the following code:

=IIF([COLOR=1] & [COLOR=2], [COLOR=1] & ", " & [COLOR=2])

It didn't work, but it might be along the right path?

 

[dhookom]

I'm not sure if this is what you are looking for but I would first normalize this "spreadsheet" with a union query:

SELECT [Brand Name], [COLOR=1] as Color
FROM tblNoNameGiven
WHERE [COLOR=1] Is Not Null
UNION ALL
SELECT [Brand Name], [COLOR=2]
FROM tblNoNameGiven
WHERE [COLOR=2] Is Not Null
UNION ALL
SELECT [Brand Name], [COLOR=3]
FROM tblNoNameGiven
WHERE [COLOR=3] Is Not Null;

Once your table is normalized, most report formats are possible.

If you can't figure this out, come back with how you would want your above data to be displayed in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MSWhat]

Hey, Duane--

It looks like you're exactly dead on! The only problem is that I'm not able to add more categories beyond the "Color" and "Brand Name." I'm sure this is only because I'm not very knowledgable at MS Access, not because it can't be done. Below is the code which you gave me, adapted to my needs, which has worked perfectly to form the union query that I need.

SELECT [Transaction Name], [Allocatee 1] as Allocatee
FROM [Master List]
WHERE [Allocatee 1] Is Not Null
UNION ALL
SELECT [Transaction Name], [Allocatee 2]
FROM [Master List]
WHERE [Allocatee 2] Is Not Null
UNION ALL SELECT [Transaction Name], [Allocatee 3]
FROM [Master List]
WHERE [Allocatee 3] Is Not Null;

However, I also need to add the following fields. Unfortunately, my union query can't be opened in design view, only SQL view, and so I'm not sure how to add the following controls. What is below is the SQL statement of those fields which I'd like to add, copy>pasted from the SQL statement of another query. How do I add them? I tried just putting the two together, with a "UNION ALL SELECT" to bridge them, but an error message popped up telling me that "The number of columns in the two selected tables or queries of a union query do not match."

As a workaround I tried to just make the report based off of two queries, but apparently you can't do that. Any thoughts?

UNION ALL
SELECT [Master List].[Tax Credit Type], [Master List].[QEI 1],[Master List].[QEI 2],[Master List].[QEI 3], [Master List].[NMTCs], [Master List].[Fed HTCS], [Master List].[NMTC Equity], [Master List].[NMTC Price/Credit], [Master List].[Fed HTC Equity], [Master List].[Fed HTC Price/Credit], [Master List].[Total Equity], [Master List].[Closing Date], [Master List].City, [Master List].State, [Master List].BDO, [Master List].PM, [Master List].AM, [Master List].[Total QEI], [Master List].[Legal Firm], [Master List].[USB Legal's Lawyer], [Master List].[Accounting Firm], [Master List].[Leverage Lender 1], [Master List].Developer, [Master List].[Project Type], [Master List].[Leverage Lender 2], [Master List].[CDC Debtquity], [Master List].Investor, [Master List].BDA, [Master List].[Project Description]
FROM [Master List]
WHERE [Master List].[Status]="AM: Closed & Active

 

[MSWhat]

IGNORE THAT LAST POST! I'm hot on the trail, but still have one outstanding question. Here's the SQL statement, which should include every Allocatee from each Transaction, as well as all of the other information about the transaction. (Is that correct?!)

My only other concern is that I want to filter NOT ONLY by WHERE [Allocatee X] is not Null, but also by WHERE [Status]="AM: Closed & Active". How do I make it so that the union query filters by both WHERE statements? I tried putting each in paranthesese and putting a comma and/or an ampersand between them, but that didn't work, i.e.

WHERE ([Allcoatee 1] Is not Null) & ([Status]="AM: Closed & Active")

Thanks to all!

 

[VRoscioli]

WHERE ([Allcoatee 1] Is not Null) AND ([Status]="AM: Closed & Active")

-V

 

[MSWhat]

Brilliant! Thank you, thank you, thank you.