query outputs mismatched data

[bluefoxoctober]

When running queries on three or more tables, I often get the join conditions to work on the first two tables, but not on the third or fourth tables. This causes the output to have multiple selected records for the first table, and the number of multiples is equal to the number of selected records from the third or fourth tables. I can fix it by running two separate queries, but I need to be able to turn this process over to users, and their skills are somewhat limited.

 

[rgbean]

If you are using the Query Designer - don't! As you can see, this isn't reliable for more than two tables. The best thing to do is just write the code by hand.

If you need to make something available for end users, consider using a third pary tool like FoxFire! (

http://www.micromegasystems.com/),

there may also be some free tools at the UT (

http://www.universalthread.com/Live/Download5.asp)

- haven't checked recently.

Rick

 

[bluefoxoctober]

Thanks, rgbean. Do you mean I need to do a scatter and gather memvar to load the tables? And thanks for the links. They look good, and I was unaware of their existence.

BlueFoxOctober

 

[rgbean]

No I'm not suggesting resorting to dBase-like code, it's just that the query designer is limited (even in VFP 7.0!), and was never meant to be used by end users.

Writing proper SQL statements is a skill unto itself and it's even harder in FP 2.x without the JOINs (INNER and OUTER) that VFP offers. I'd suggest getting a good book on SQL (there is very little help in the FoxPro help files) and studying it. If nothing else, it may convince to consider upgrading to VFP where the SQL has been extended much beyond what FP 2.x offers.

Rick

 

[bluefoxoctober]

Thanks again. I will find a book on SQL as you suggest. I do have VFP 6.0, and do plan to convert, just a matter of time right now.