Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Sheffield, South Yorkshire, England
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Two entries in printed Address Book

    My first computer was one of the first Amstrad 8256 computers here in the UK in 1985. When Locofile & Locomerge were introduced with its basic like language one of my first projects was to put our extensive address book onto the database and develop an easily printed out loose leaf address book. For ease of finding commercial items in the printed version some items were printed twice under (say)

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Two entries in printed Address Book

    [img]/w3timages/icons/cool.gif[/img]A long winded method would be to make a new table using a make table query and include a calculated field called RealIndex which would contain Lastname Firstname.

    Then run an append query selecting only those items with a flag. In the second query the RealIndex field would be set to Occupation.

    Then use the resulting table for the report.

    You might make a macro to pull all this together, in which case although clumsy it would run at a single click.

    You can probably do the whole thing in one massive SQL query but they are much more difficult to maintain.
    David Grugeon
    Brisbane Australia

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

    Re: Two entries in printed Address Book

    You need a category table that holds all the categories you want to use, like electrician. That table would have two fields, a numeric autonumber key and the name of the category, i.e., electrician.

    Then you would have another table called something like AddressCategory. That table would hold the primary key from the address table and the primary key from the category table. Those two fields would jointly create a unique key, so that you could only have one record in a particular category for each address. Then if an address were for both an electrician and a heating contractor, you would have an entry for the same address ID for each category ID. This is called a join table because it gives you a way of joining many address to many categories. In other words a single address may have multiple categories and any single category may be attached to multiple addresses.

    You would create your multiple listings by building a query like this:

    SELECT tblAddress.*, tblCategory.Category
    FROM tblCategory
    RIGHT JOIN
    (tblAddress
    LEFT JOIN tblAddressCategory
    ON tblAddress.AddressID = tblAddressCategory.AddressID)
    ON Category.CategoryID = tblAddressCategory.CategoryID;

    That would give you all your addresses, and any that had multiple categories would turn up multiple times. Build a report based on the query and set it to group by Category.
    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
  •