Saving Multiple Tables

Databases
1

Hi how would I go about combining these 2 tables to save them in one go, I have shortened the tables just to save space.

[quote] dim dr as New DatabaseRecord
dim rs as RecordSet

if golfer <> nil then
rs = golfer.SQLSelect("select * from course where PK = "+ str(coursepk))
end if

dr.column(“club”) = trim(club.text)
dr.column(“clubaddress”) = trim(clubaddress.text)

app.golfer.InsertRecord “course”, dr
app.golfer.commit
[/quote]
and the second table is

[code] dim dr as New DatabaseRecord
dim rs as RecordSet

if golfer <> nil then
rs = golfer.SQLSelect("select * from mens where PK = "+ str(menspk))
end if

dr.column(“mens1”) = trim(mens1.text)
dr.column(“mens2”) = trim(mens2.text)

app.golfer.InsertRecord “mens”, dr
app.golfer.commit
[/code]

Thanks Shane

2

You can only use a recordset to save to one table. If you want to update multiple tables you’ll need to start getting into SQL statements.

3

Don’t understand SQL Statements.

4

So I guess what I am asking is it ok to save a table that has 160 entries in it rather than trying to save it in multiple tables.

Cheers Shane.

5

Your tables should organize your data.

If your table is purchases and you have 160 entries all purchases that’s fine. If your table is myBigFatTable and you’ve got every entry under the sun on that table that’s poor practice (even if it works.)

SQL statements aren’t that difficult, you should try to learn them.

6

I don’t quite understand what your example is doing, but here’s what I’d do if you wanted to record a list of scores from several golfers. I’d have one table called golfers, one table called courses, and one table called scores.

Golfers would have columns id (integer, primary key, auto increment), firstname (text), lastname (text), and any further personal info.
Courses would have columns id (integer, primary key, auto increment), coursename (text), and any further course info.
Scores would have columns id (integer, primary key, auto increment), golfer, course, score date, and any other info (e.g. ‘handicap’).

So, golfers would look like this:

id firstname lastname 1 John Smith 2 Jenny Jones 3 Ed Fish

Courses:

id coursename 1 St Andrew's 2 Royal West Norfolk 3 Gog Magog

Scores:

id golfer course score date 1 1 1 80 2014-06-30 2 1 1 78 2014-07-04 3 2 3 77 2014-07-05

So, I’m not making extra columns in the golfer table for the scores.

There are literally millions of SQL tutorials out there, so I’d suggest having a look at them - at the very least, you’ll need to understand SQL statements to pull information out of your database!