converting a relational database to a flat one

  ernieernie 22:34 01 Mar 2007

Is there a (fairly) simple way to combine the two dbf files that make up a relational database into a flat one?
I don't now need all the fields that that are in the database and I want to leave it in a simpler form for my successor.
The only common field is one called IDNO

  Woolwell 22:37 01 Mar 2007

Run a Query and export to Excel? I assume you are talking about Access database.

  ernieernie 12:52 02 Mar 2007

Thanks for respnse Woolwell. Actually the database is Lotus Approach. I'll have a look at the manual (gasp!) and see what it says about queries.

  ernieernie 20:29 02 Mar 2007

The APPROACH manual information on queries just baffled me. I'm a self-taught octogenarian who's a big fish in a little pond (perhaps puddle would be more accurate).
Unless there's another way I shall either have to leave the problem or face doing an awful lot of re-entering as there are over 4,000 entries in the database.
Perhaps that would be a good idea as I would get the chance to delete a lot of rubbish! One advantage about being retired is that you can spare the time to take on a big task like that.
Thanks again Woolwell, you were the only one to respond.

  Woolwell 21:12 02 Mar 2007

Sorry I cannot help you with Lotus Approach. Haven't even seen it. You should be able to export to a spreadsheet which is a version of a flat database.

  fly2hi 02:01 03 Mar 2007

You could try this site for links to user groups;
click here

There must still be a way to what you want. In the old days of dos you could use something like Foxbase and with the 'update on' command you would;
add a new field to dbf1 (the one you want to keep)
lets say you call it 'custname'
these names i assume are stored in dbf2 along with the 'idno'
you would then 'replace' all the 'custname' fields in dbf1 with the names from dbf2 using the 'idno' key in both files as the link.
Hope that makes sense.
As the files are in DBF format you could still achieve this under windows using a command prompt to run something like Foxbase (if you can get hold of a copy. I'm glad i kept all this stuff!). There must surely be a free utility to do the same thing. Or you can import DBF files into Access and manipulate them. Or quite possibly a command within Lotus approach which i've never used. One thing i'm certain of. You shouldn't need to re-enter all the data.

  ernieernie 11:09 03 Mar 2007

fly2hi. You've got me thinking.
It is nice to know that solving problems is still something that gives me a lift so there must be life in the old dog yet.

  vinnyT 12:28 03 Mar 2007

This may help, click here. This tool will support you in your database management activities for dBase-compatible tables...

Plus it's free, so no loss if it doesn't (just make sure you nhave a verified backup of your database.

  fly2hi 20:36 03 Mar 2007

Thanks for the link Vinny. Looks like a useful tool. I had a little play with it but can't see that it goes beyond the on-screen commands, unless something is hidden away. The docs seem to suggest it has more features than can be found in a hurry. Noticed there are a lot of links related to dbf files and would have thought there would be a comprehensive tool amongst them, even if it was the price of a decent curry.
Ernie...please let us know how you get on with this particular one.

  ernieernie 23:02 04 Mar 2007

I followed your link and found Lotus Approach User Support which led me to its forum where I was told I could just EXPORT in Approach.
This does work, but what I get is an XLS file with as many copies of each individual record found by IDNO as there are persons in dbf2 - which I ought to have expected if I had thought enough about it.
Which of course brings me back to why I made it relational in the first case.
It looks to me as though it will be nearly as much work to do to get it how I intended as to have entered it all again from scratch!
I might decide to let my successors do the worrying!

  vinnyT 12:00 05 Mar 2007

You're retiring, take it easy, go traveling, fishing (or pref both), you've done your bit, enjoy yourself.

Basically, forget about spreadsheets.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Adobe shows still-in-development tools, including automatically colourising black-and-white photos

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?