Query Question

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jl2886
    New Member
    • Mar 2007
    • 83

    #1

    Query Question

    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?
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32669

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

    Comment

    • jl2886
      New Member
      • Mar 2007
      • 83

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

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32669

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

        • jl2886
          New Member
          • Mar 2007
          • 83

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

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32669

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

            Working...