I have a 2 tables. They are linked together by the primary key of the (one) table and one of the primary keys of the (many) table. When I bring the two tables in the query I get separate entries for each of the lines in the many table, but I would like for the fields of the "many" table to be added horizontally to all of the cases in the "one" table. Does anyone know of a way to do this?
Query Question
Collapse
X
-
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 blahhhhComment
-
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)Comment
-
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.Comment
-
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.Comment
Comment