I need help.  Oh yea, I'm in over my head and I've been looking for a

solution to this problem for a while and  I surrender.



Here is what I'm trying to do in steps...



1). Create a recordset based on 3 tables unique to the logged on user.  If

the user is new, allow user to create a new record.



2). Populate a Session (am I saying this right?) with the recordset and make

it available on the current form so any previously existing data will appear

like registration information.



3). Allow user to fill out form with new or remaining information.



4). submit the information and update the database



5). email the user the the completed form results



6). email the users form results to myself





My problems are these....



1). I don't know how to create the recordset based on a unique user across 3

tables.



2). I don't know how to take that resordset and populate a Session so

previous info is available to the user on the form (I've done this with a

single table before eg <%=Session("FirstName")%> etc but don't know how to

do this with a multiple tables)



there are other problems but they would probably be solved if I can find a

solution to these problems first.  I'm using Access since SQL Server is not

accessable to me (or Oracle for that matter) so please be aware of this if

sending me SQL statements.



Thanks and I hope I'm explaining what I need clearly.  If not please just

ask!



John

John,



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "John Kinane" <john@k...>

Subject: [access_asp] recordsets, sessions, and access, oh my!!





: 1). I don't know how to create the recordset based on a unique user across

3

: tables.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



You use an SQL statement that JOINs the necesary tables together. This is

used to populate the recordest



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: 2). I don't know how to take that resordset and populate a Session so

: previous info is available to the user on the form (I've done this with a

: single table before eg <%=Session("FirstName")%> etc but don't know how to

: do this with a multiple tables)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



You don't need to do this to populate a form, something like:



<input type="text" name="txtFirstName" value="<%=objRS("FirstName")%>">



is all you need - no session variables used!



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: there are other problems but they would probably be solved if I can find a

: solution to these problems first.  I'm using Access since SQL Server is

not

: accessable to me (or Oracle for that matter) so please be aware of this if

: sending me SQL statements.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



SQL is a database independent query language that is used to manipulate data

in databases. AFAIK, this is the only way you're going to get data joined

from three tables together into the one recordset.

Look in Access online help for INNER JOIN



Cheers

Ken



: Thanks and I hope I'm explaining what I need clearly.  If not please just

: ask!

:

: John

Thanks Ken,

that helps and I'm on my way to solving this problem.....



I've used SQL to a limited extent.  My problem is mainly with not know how

to write proper JOIN statements.



I've tried this and it didn't work....

"SELECT * FROM Person, Project, Client WHERE PersonID.Person = " &

Session("PersonID") & ";"



Am I on the right track with this?



Incidently, I tried cheating and cut and paste the SQL statement generated

from a query  but it spit out an error saying that it was incompatable.



Its becoming more clear that my problems are from my inexperience with SQL.

In the short term is (can't buy any more books right now, I'm up to my

ears!) there a resource that can quickly help me get this going?  Access has

SQL help that I can apply to ASP?



Thanks again!

----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "Access ASP" <access_asp@p...>

Sent: Sunday, October 21, 2001 10:48 PM

Subject: [access_asp] Re: recordsets, sessions, and access, oh my!!





> John,

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "John Kinane" <john@k...>

> Subject: [access_asp] recordsets, sessions, and access, oh my!!

>

>

> : 1). I don't know how to create the recordset based on a unique user

across

> 3

> : tables.

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> You use an SQL statement that JOINs the necesary tables together. This is

> used to populate the recordest

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> : 2). I don't know how to take that resordset and populate a Session so

> : previous info is available to the user on the form (I've done this with

a

> : single table before eg <%=Session("FirstName")%> etc but don't know how

to

> : do this with a multiple tables)

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> You don't need to do this to populate a form, something like:

>

> <input type="text" name="txtFirstName" value="<%=objRS("FirstName")%>">

>

> is all you need - no session variables used!

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> : there are other problems but they would probably be solved if I can find

a

> : solution to these problems first.  I'm using Access since SQL Server is

> not

> : accessable to me (or Oracle for that matter) so please be aware of this

if

> : sending me SQL statements.

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> SQL is a database independent query language that is used to manipulate

data

> in databases. AFAIK, this is the only way you're going to get data joined

> from three tables together into the one recordset.

> Look in Access online help for INNER JOIN

>

> Cheers

> Ken

>

> : Thanks and I hope I'm explaining what I need clearly.  If not please

just

> : ask!

> :

> : John

Hi John,



There's an elementary SQL tutorial here:

http://w3.one.net/~jhoffman/sqltut.htm



Secondly, when you build SQL string in ASP, all you are doing is building a

large string of text. As such, any SQL string you build in ASP will work

fine with Access, provided that it's valid SQL in the first place.



To do JOIN, you usually write something like this:



SELECT field1, field2, field3...fieldn

FROM table1

INNER JOIN table2

ON table1.PK = table2.FK



    -or-



SELECT field1, field2, field3...fieldn

FROM table1, table2

WHERE table1.PK = table2.FK



(PK = Primary Key, FK=Foreign Key - change to be what's in your tables).

The second syntax is the older, less used version - you might have some

purists jump on you for writing that way (even though it does work...)



Access requires you to put some parenthesis in when doing multiple JOINs:



SELECT field1...fieldn

FROM (

    table1

    INNER JOIN table2

    ON table1.PK = table2.FK

    )

INNER JOIN table3

ON table2.PK = table3.FK



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "John Kinane" <john@k...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, October 22, 2001 11:41 PM

Subject: [access_asp] Re: recordsets, sessions, and access, oh my!!





: Thanks Ken,

: that helps and I'm on my way to solving this problem.....

:

: I've used SQL to a limited extent.  My problem is mainly with not know how

: to write proper JOIN statements.

:

: I've tried this and it didn't work....

: "SELECT * FROM Person, Project, Client WHERE PersonID.Person = " &

: Session("PersonID") & ";"

:

: Am I on the right track with this?

:

: Incidently, I tried cheating and cut and paste the SQL statement generated

: from a query  but it spit out an error saying that it was incompatable.

:

: Its becoming more clear that my problems are from my inexperience with

SQL.

: In the short term is (can't buy any more books right now, I'm up to my

: ears!) there a resource that can quickly help me get this going?  Access

has

: SQL help that I can apply to ASP?

:

: Thanks again!