Two entries in printed Address Book

Topic

New Reply

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)

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote