Source language: Translate to:

Sql using 2 tables

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sql using 2 tables

Postby BRobinsonS » Fri Oct 19, 2012 5:20 pm

I have two tables Clients and Duespaid.
I want to report on Dates e.g. Sept 1 to Sept 30 for active members/clients who paid their dues.

I am trying the SQL statement to select records:
dbpExecSQL "AddrBook" "Select * from Contacts,Duespaid |where (Contacts.MemNum = DuesPaid.MemNum) and Contacts.ActiveStatus = True" ""

Common field is MemNum.

Getting 'Syntax error in query 'MemNum = %0%' message.
Also tried other variations and got 'MemNum has no default value'.

Suggestions?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql using 2 tables

Postby Gaev » Fri Oct 19, 2012 6:59 pm

Brian:

a) your posted code ...

- does not have matching quotation marks
- has a | that I don't understand ... what is it supposed to do ?
- includes a comma separated list of tables to be selected ... not sure if this is standard sql
- is it Duespaid or DuesPaid ?

b) I think you need to use an INNER JOIN like ...

Select *
from Contacts
INNER JOIN Duespaid
ON (Contacts.MemNum = Duespaid.MemNum) and (Contacts.ActiveStatus = True)

For an understanding of such advanced SQL options take a look at http://www.w3schools.com/sql/sql_quickref.asp and http://www.w3schools.com/sql/sql_join_inner.asp

Note that the syntax of MS Access SQL may deviate at times from that of Standard SQL.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql using 2 tables

Postby dpayer » Sat Oct 20, 2012 6:36 am

Gaev can probably do these SQL statements from memory.

For people like me it is sometimes helpful (if available) to use a copy of Microsoft Access to create the relationship and query in your database and then right click on that query and copy out the SQL statement. You can use this query in your NB app with little difficulty from my experience.

Mastering the 'join' statement is something I am still working on.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Sql using 2 tables

Postby Gaev » Sat Oct 20, 2012 8:08 am

Gaev can probably do these SQL statements from memory.
At my age, my memory cells are almost all filled with some data ... so I am very selective about what to store in them ... and INNER JOIN syntax is not one of them :)

So, I have a link to websites like w3schools that I go to each time I need the exact syntax (or even the meaning of INNER JOIN).

The second link in my previous post gives a very good example of what it does.

Also, a good idea to have a couple of Tables with related fields ... and a Lab Pub with ...

- a TextEntry Box to type in such commands
- a Button to invoke a dbpExecSQL command that uses the contents of the TextEntry Box
- a Rectangle widget that shows the results

... in other words ... Learn By Trial and Error ... or by error and error and error until you stumble upon 'the right method'.

Also, I don't know what I would have done B.G. / B.B. (before Google / before Bing).
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql using 2 tables

Postby BRobinsonS » Sun Oct 21, 2012 2:57 pm

I have abandoned the SQL idea and gone back to Master/Detail ideas.
See my comments under this Subject.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests

cron