Query Question

Do you mean a GROUP BY?
Or are you talking about a record which is as wide as there are records?

There is no grouping involved.
I have two tables:
Ex)
Main Table
Case # Other Fields
112-112 blah blah blah

Other Table
Case # Other Fields(not the same as the main table)
112-112 blah
112-112 blahh
112-112 blahhh
112-112 blahhhh


I want to then want to create a query that looks like this:
Case # Other Fields Other Fields(not the same as the main table)
112-112 blah blah blah blah blahh blahhh blahhhh

This is not really supported by an RDBMS like Access (as it doesn't make database sense).
However, other people have requested similar things so I designed a bodgy way around this. Check it out at (Combining Multiple Rows of one Field into One Result)

I do not want them combined to one field, sorry.
Here is what the query looks like now:
Case Number 5 Fields from the Many Table 20 Fields from the One Table
507-111
507-111
507-111
etc.

The 5 Fields from the many table are different for each case number, but the 20 Fields from the one table are the same for all case numbers. I want it to look like this:

(Case Number) (Fields from first(507-111) entry) (5 Fields from Second (507-111) entry) (5 Fields from third entry) 20 fields from the One Table
So, now there is only one 507-111 entry


The problem is, I want to be able to rename the fields from the many table based on the information in one of the fields. Or else, there would be 3 sets of 5 Fields with the same field name.

Your problem is much, much bigger than that.
There is no way that I know of (short of some fairly convoluted bespoke code) to produce output as you describe. Again, that is quite foreign to the concepts of an RDBMS. I'm assuming (as you haven't said otherwise) that the numbers of records vary from case to case. Even if it were static at three, that would not be straightforward.
Fundamentally, a query is defined before it is run. Varying the number of fields at run-time is not easily supported. Cross-tab queries do something along those lines but are highly specific.