Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Sheffield, South Yorkshire, England
    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
    Brisbane, Queensland, Australia
    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
    Sacramento, California, USA
    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
    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.

Posting Permissions

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