Multiple Tables Forces Man to Rend Computers 1

[tradermaceo]

I'm a novice in the extreme, using Approach for the first time.

I have several .txt files that I wan to compare against each other.

I've joined the databases into a blank DB, but cannot access the information.

I've learned to write very simple SQL statements, however the assistant doesn't like them.

I've tried to write a script (I'm familiar with that type of programming) but to no avail.

Can anyone help me to access multiple tables, and do searches across multiple fields?

 

[paulbent]

When you open a text file in Approach it creates a dBase table (.dbf file) from it and an apr file with a form and worksheet views. You need to save one of these aprs (select apr only on the File Save dialog) and discard the rest.

Then in the saved apr, from the menu, Create - Join to open the join dialog. Click Open to browse to a dbf to join. If the dbfs have common primary keys then join on these and you will be able to compare records. If there are no matching keys you can still join on two arbitrary fields and will be able to create views based on the dbfs to find on.

To compare records, create a worksheet with main table dbf A containing the primary keys from A & B. Those records with a blank B field exist in A but not in B. A similar worksheet based on table B will show which records don't exist in A.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[tradermaceo]

Paul,

Thank you.

I'd created a join prior, however I'm running into trouble creating a search like:

SELECT lo_prices.base,lo_prices.time,hi_prices.base,hi_prices.time FROM lo_prices,hi_prices WHERE lo_prices.frame = hi_prices.frame;

Although, I may have just inadvertently discovered why. My initial SQL statment contained table names with 'dots' in them ie. lo.prices hi.prices, etc. Could that be the reason it wasn't working?

 

[paulbent]

Approach has very limited SQL support in its native dBase connection. Joins aren't supported in SQL statements.

You need to open one dbf and join the other on &quot;frame&quot;, assuming this will be a one-to-one relationship. Create a worksheet based on lo_prices and add the three fields from each table. In Find mode, enter <> as the condition in hi_prices.frame. This will find all the lo_prices records that have a match on frame in hi_prices. You can select Find Again to enter further conditions if you want to compare the other fields.

There are plenty of Approach resources at

http://www.xpertss.com

which may also help you get started.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk