Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Combine 2 Tables in Report without Duplicates

    Hello,
    I have been told what I want to do cannot be done with Tables. Now I am wondering if it can be done in a report. I am trying to combine 2 Tables along a common field ID, but there are several issues. Each ID represents a city block and on each block there are many traffic signs (many rows). Table_OLD lists an ID (identifying the block) with a row for each sign at, say, a year ago. Table_NEW lists an ID (identifying the block) with a row for each sign as they are currently. I want to see them side-by-side, but sometimes a particular ID (block) will have more signs in Table_OLD. Other times it will have more signs in Table_NEW. So with some IDs (blocks) there will have to be blanks in the Sign Description field on the Table_OLD side and at other IDs, there will have be blanks in the Sign Description field on the Table_NEW side.
    I tried a UNION (full) join of the Tables on the ID field, but it creates many duplicate rows because each particular ID in each Table has many entries. When I tried to put both Tables in a report side-by-side, Access insisted I link them and then created the same duplicate fields.
    So in a perfect world my report would look like the attached screenshot (Tables.png).


    The idea being that the list of signs align (are grouped by?) along the same ID number and leave blanks where there are fewer signs in one table or the other.
    I can physically put the two Tables next to each other so it seesm logical that Access could so it in a much neater way.
    Thanks for looking at this.
    Attached Thumbnails Attached Thumbnails Tables.png  

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Your tables don't sound like they're completely normalised (no repeats) on them.

    You probably want to have 2 (or 3) tables setup for this, and your life will be soooo much easier (once you get your head around joins anyway).

    For example:

    Table_NEW
    - Block ID
    - Sign

    Table_REF_sign
    - Sign
    - Sign Description

    You'd then setup a relationship between the two 'Sign' fields. If a Block ID can have any number of signs associated with it, not just one, and depending on what kind of data you have linked with Table_NEW, you might want to use the setup:

    Table_NEW
    - Block ID
    - Address?
    - Postcode?
    - Council region?
    - etc.

    Table_NEW_signs
    - Block ID
    - Sign

    Table_REF_sign
    - Sign
    - Sign Description

    In this way, you'd have the bulk of your data in 'Table_NEW'. NOTHING to do with signs appearing in that table.

    In the second table, 'Table_NEW_signs' you'd have the means to have unlimited references to the 'Block ID' held in 'Table_NEW', but without having to use fields like, 'Sign 1', 'Sign 2', which won't work if the number of signs you are trying to associate are dynamic (of unknown amount). This way you can reference the 'Block ID' that is being used, and knowing that, can find ALL the 'Sign'(s) associated with that block.

    The third table can then be used to find the 'Sign Description' for all the 'Sign'(s) found to be associated with that 'Block ID'.


    This modification isn't necessary to get what you're after working. It is however, HIGHLY recommended that one of these modifications is made. Either my first suggestion if each 'Block ID', only needs associating with a single 'Sign', or my second suggestion if each 'Block ID' can be associated with ANY NUMBER of DIFFERENT 'Sign'(s).
    Looking for the perfect beer...

  3. #3
    Join Date
    Oct 2011
    Posts
    3

    Combine 2 Tables.

    You know the make 3 Tables and join on the ID# sounds too obvious to have missed it. I will give it a try.
    Thanks

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I'd only join on the ID# for the Signs contained on each block.

    NOT for the sign descriptions to the sign name. I'd simply use the sign name as the foreign key, and use no primary key at all on the second table (unless you want to have ID#s on that table, which is fine. It's often nice to have a way to reference a singular record in a table). Using the Sign name as a foreign key has the benefit of troubleshooting the third table containing the descriptions, because rather than having to look up meaningless numbers, you have a very short description already that just needs pairing with a more verbose one.

    Either way, hope you get it working.

    Just drop back in if you get stuck again.

    Good luck!
    Looking for the perfect beer...

Tags for this Thread

Posting Permissions

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