Results 1 to 7 of 7
  1. #1
    danprtr
    Guest

    Simple frustrating question

    Hoping someone can clear the fog!

    Trying to query 2 small tables with a 1 to many relationship between records in a database. Like to show (via file export) the data relationships in the tables so that the table with the many (up to 4)related records are displayed as fields along with the second tables fields.

    Hope that makes sense and thanks in advance for any help!

    Dan

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple frustrating question

    I would suggest you use the Report Wizard and try as many types as you need to in order to get the result.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple frustrating question

    Are you trying to display a single record for each record in the one side of the relation with the fields in the many side shown in the same record? Or do you want to show repeated records from the one side for each matching record in the many side? The latter is a simple two-table query. The former is not relational, it's spreadsheet thinking.

    You need to give more information about the structure of the two tables and maybe an idea of the kind of a record you hope to display in order to get more specific help.
    Charlotte

  4. #4
    danprtr
    Guest

    Re: Simple frustrating question

    Spreadsheet thinking is what I'm after. These two files were created by a trade show barcode scanner and I want to combine them in a spreadsheet format.

    Thought was Access could do the job with a query faster than sorting and manually combining the two tables in Excel.
    Table 1 has 1015 unique records (related to 1 or more records in table 2).
    Table 2 has 1457 records (related to only record in table 1).

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple frustrating question

    Related how? Do your table1 have a key field that's a foreign key in table2? You need to include that information. If each of the tables has only one field, or one field of barcode info and an autonumber, there isn't any way to relate them unless the same info is contained in both tables.
    Charlotte

  6. #6
    danprtr
    Guest

    Re: Simple frustrating question

    The tables have a common field and I've already linked them using a query. Here is layout:
    Table 1 (1015 records)- BadgeID FName LName ...
    Table 2 (1457 records)- BadgeID ActionCode

    I just can't get the Query to show the ActionCodes (12 unique and up to 4 per record in table 1) in columns "added" to Table 1. Like this:
    Query 1- BadgeId FName LName ... ActionCode1 ActionCode2 ActionCode3 ActionCode4
    1 Frank Smith Send1Catalo Send2Catalo Send2Catalo

    The above files come that way from the scanner and the sample data says to send a total of 5 catalogs to the guy.

    Thanks for your questions. I'm getting the feeling this sort of task is not really what Access is designed to do but still think there should be a way to display records from table 2 in columns along with the records from table 1.

    Thanks again!

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple frustrating question

    Try playing with a crosstab query based on both tables.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •