Finding orphans with an Access query
Finding the start with Access
Printing in Approach with a title
Picking your directory in Approach
Sorting a Paradox tabular report
Sorting a keyed Paradox table
Updating links in Paradox 5.0 for Windows
Q I have some budget information stored in two related tables in an Access 2.0 database. One table, Budget Categories, holds a code and description for each type of expense I record. I enter my transactions into the other table, Expenditures. These tables are linked with a field called Budget Code.
How can I create a report that lists all expenditures and all budget categories, including categories for which there are no expenditures? I tried creating a report based on a query that links the two tables, but the result shows only categories where I've spent money.
- Kathryn Jackson
A You need to change your query's Join Properties. Open your query in Design mode, and right-click the section of the line connecting both tables' Budget Code fields that's marked in red here (it'll be black on your screen).
If the resulting pop-up menu doesn't have a Join Properties option, you clicked in the wrong place.
When you get the right menu, select Join Properties. In the resulting dialogue box, select 2: Include ALL records from 'Budget Categories' and only those records from 'Expenditures' where the joined fields are equal. Click OK, close the query window, and click Yes to save your changes. Your report will now show the empty categories.
Q I often use Access 2.0's Find button to locate data quickly, but I have one problem with it. By default, it finds only records in which the entire field matches the text I've entered. I almost always change the "Where" setting to "Start of Field" - where Access finds any records that start with my text - before I begin my search. Can I make this the default?
- Eric Lemmon
A Sorry; you can't change Find's defaults. But you can get the same result almost as easily with the * wild card. For example, if you want to find the value "Star Gazers" without typing the whole thing, type Star*. Access will look for records in which the value in the field you are searching starts with "Star", even though you didn't change "Where" to "Start of Field".
Q I've spent a lot of time creating an Approach 3.0 worksheet with several calculated columns. I need to print the worksheet with a title and the date, but I don't see a way to add these to a worksheet. Do I have to create a report with the same calculations?
- John Reed
A There is a way to add a title and the date to a worksheet. Click the Change the style & properties button. In the resulting dialogue box, for "Settings for", select Worksheet: Title, where Title is the name of your worksheet. Click the Printing tab. Check the Print title and Print date options, and enter your desired title.
To see how the printed worksheet will look, click Preview. The title will be at the top of each page; the date at the bottom. Unfortunately, you can't change these settings.
Q I keep my Approach 3.0 data in a directory called e:\data. Can I make it my default directory?
- Steve Pardus
A The secret's in Windows' Program Manager. Highlight the app's icon, then select File-Properties or press <Alt>+<Enter>. In the dialogue box's Working Directory field, type the path to your data directory (in your case, e:\data). Click OK. Next time, the Open dialogue box will list the e:\data files.
You can start Approach or any other Windows database by double-clicking a data file, including one whose icon you've dragged from File Manager into Program Manager for easier access.
Q I've designed a tabular report for listing sales amounts in Paradox 5.0 for Windows. The original table is sorted by invoice number, but I want my report sorted by customer name. I've tried adding a group band, but that gives me a separate table for each customer. What I want is one big table sorted by customer. Is this possible?
- Danielle Zale
A Paradox 5.0 makes this easy. Open your report in design mode and click anywhere in the All Records band to select it. Right-click in the band but away from any fields, and choose Sort from the properties menu. In the resulting dialogue box's Fields list, double-click the name of the field to sort by. Click OK to return to your report design. When you run the report, you'll see your records in customer order.
Q I have a Paradox 5.0 for Windows file that's keyed by two fields - Last Name and First Name - so that Paradox automatically sorts the records by these fields. Sometimes I need to view the records by city, but since the table is keyed, the Table-Sort command will only sort it to a new table. I need to work with my original set of records.
- Alicia Kaye
A To view a keyed Paradox table by fields other than the key fields, you first need to set up a secondary index. In the Project Viewer, right-click your table and select Restructure. In the Restructure dialogue box, change Table Properties to Secondary Indexes and click Define. Under Fields, double-click City, click OK, and when Paradox asks you for a name, enter something like By City. Then click Save back in the Restructure dialogue box.
Once you've set up the secondary index, you can easily use it whenever you need to re-sort your table temporarily by city. While viewing your table, click Filter, and for Order By, select By City (or whatever you called your index). Then click OK to view your records in the new order.
To change the viewing order back to the default, click Filter again and select the table's primary index. Also, the next time you open the table, it will be sorted by the key fields again.
Q I use two Paradox 5.0 for Windows data files, names.db and calls.db, to keep a history of client phone calls. Names.db is keyed on two fields, Last Name and First Name; calls.db is keyed on Last Name, First Name and a date field. I've created a form for names.db that shows one name at a time, along with a table of any related records in calls.db.
Everything works fine until I need to change a person's name, which causes the entire history of calls for that person to disappear. When I check the files in table view, I can see that the records in calls.db still have the old name. I thought Paradox would handle this kind of change automatically and keep the links between records, even when the linking values are changed. How can I keep my files in order?
- Eleanor Black
A Paradox can use a feature called referential integrity to protect the values used to link data files. But for it to work, you have to set it up when you create or restructure a table.
To establish referential integrity for names.db and calls.db tables, right-click calls.db in the Project Viewer and select Restructure. In the Restructure dialogue box, under Table Properties, select Referential Integrity and click the Define button.
Double-click names.db under Table, and Last Name and First Name on the Fields list. Last Name and First Name fields should now appear under both Child Fields and Parent's Key. Leave the Update Rule set to Cascade. Click OK, enter a name and click OK again to return to the Restructure dialogue box. Click Save to finish.
Now when you change a name in names.db, Paradox will automatically update any related records in calls.db. If you make such a change in a form that links the tables, calls's records will still disappear, but you can bring them right back by pressing <Ctrl>+<F5>.
Celeste Robinson