INSERT Query on two dependent joined tables ??

Hi,

I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.

Here's the basic specs and database schema:
-------------------------------------------

Database: Basic MS Access 2000 MDB
Table Relationships: Both tables are joined one-to-one on their ID fields.

TableMembers
ID: Long Integer; Required; Indexed(Unique); PrimaryKey;
Firstname: Text 50; Required;
Lastname: Text 50; Required;
Telephone: Text 50; Required;

TableAccounts
ID: AutoNumber; Required; Indexed(Unique); PrimaryKey;
Username: Text 50; Required;
Password: Text 50; Required;


The problem:
------------
In an ideal world I would like to use a single SQL INSERT on both tables to
insert
the required data. But I know this isn't possible as INSERT works on single
tables only, right ?

I cannot use an SQL INSERT on TableMembers as the ID field is created by the
AutoNumber
in TableAccounts.ID.

I could INSERT into TableAccounts first, which would auto-create the ID, but
how do I use the
ID in the second SQL INSERT on TableMembers ???

When I open the TableAccounts Table in Access, I can manually input all data
for both Tables
without any problems. TableMembers Fields are displayed as a dropdown record
from TableAccounts
and the ID field is automatically taken care of.

Can Anyone here can offer some advice or solutions, all replies greatly
appreciated.

Carl.

re: INSERT Query on two dependent joined tables ??

"Carl" wrote

First, unless you have beaucoup data fields that you didn't list, why would
you want to have these two tables with a one-to-one relationship? Obviously
the simplest, easiest solution is to include all the information in a single
table and you don't have to worry about matching keys.
[color=blue]
> I could INSERT into TableAccounts first,
> which would auto-create the ID, but
> how do I use the ID in the second SQL
> INSERT on TableMembers ???[/color]

Retrieve the record you just entered*, and create the INSERT SQL with code,
using that value for the key field just as you use values for any other
field.

* this may not be as easy as it seems, which
may be a good argument, in some cases, for
using a "natural key" rather than using AutoNumber
as a surrogate key.

Larry Linson
Microsoft Access MVP

re: INSERT Query on two dependent joined tables ??

Do you have any experience with visual basic? If so, you could do at least a
couple different things:
1) you could create your own routine to generate the ID value so that you
don't have to use an autonumber field, and then you could insert into each
table with your ID value.
2) you could use vb to insert the fields into the Accounts table, then read
the ID of the newly inserted ID value, then insert that ID into the Members
table.

"Carl" <mail2carl@_remove_yahoo.com> wrote in message
news:MtZwe.13254$11.3650@newsfe2-win.ntli.net...[color=blue]
> Hi,
>
> I hope someone can share some of their professional advice and help me out
> with my embarissing problem concerning an Access INSERT query. I have
> never
> attempted to create a table with one-to-one relationship but on this
> occasion
> I must keep username/password details within a seperate table.
>
> Here's the basic specs and database schema:
> -------------------------------------------
>
> Database: Basic MS Access 2000 MDB
> Table Relationships: Both tables are joined one-to-one on their ID fields.
>
> TableMembers
> ID: Long Integer; Required; Indexed(Unique); PrimaryKey;
> Firstname: Text 50; Required;
> Lastname: Text 50; Required;
> Telephone: Text 50; Required;
>
> TableAccounts
> ID: AutoNumber; Required; Indexed(Unique); PrimaryKey;
> Username: Text 50; Required;
> Password: Text 50; Required;
>
>
> The problem:
> ------------
> In an ideal world I would like to use a single SQL INSERT on both tables
> to insert
> the required data. But I know this isn't possible as INSERT works on
> single tables only, right ?
>
> I cannot use an SQL INSERT on TableMembers as the ID field is created by
> the AutoNumber
> in TableAccounts.ID.
>
> I could INSERT into TableAccounts first, which would auto-create the ID,
> but how do I use the
> ID in the second SQL INSERT on TableMembers ???
>
> When I open the TableAccounts Table in Access, I can manually input all
> data for both Tables
> without any problems. TableMembers Fields are displayed as a dropdown
> record from TableAccounts
> and the ID field is automatically taken care of.
>
> Can Anyone here can offer some advice or solutions, all replies greatly
> appreciated.
>
> Carl.
>
>
>
>[/color]

re: INSERT Query on two dependent joined tables ??

Larry....the tables need to be seperate because they will be accesed by
another third-party with strict rules on the fields. Maybe I should of
mentioned
that I would be using a Perl script to run the SQL INSERT.

Retrieving the record after the INSERT may work if I could somehow have the
SQL return
the ID after INSERT as completed.

I like the idea of using an AutoNumber as it functions as you expect it to
without
the need to create possible problems with your own AutoNumbering routines.

Just seems such a much more simple solution would be to INSERT into both
tables
using the same SQL query....I dont understand why this isn't possible !

thanx...




"Larry Linson" <bouncer@localhost.not> wrote in message
news:ZB_we.24799$Ff6.8496@trnddc09...[color=blue]
> "Carl" wrote
>
> First, unless you have beaucoup data fields that you didn't list, why
> would
> you want to have these two tables with a one-to-one relationship?
> Obviously
> the simplest, easiest solution is to include all the information in a
> single
> table and you don't have to worry about matching keys.
>[color=green]
> > I could INSERT into TableAccounts first,
> > which would auto-create the ID, but
> > how do I use the ID in the second SQL
> > INSERT on TableMembers ???[/color]
>
> Retrieve the record you just entered*, and create the INSERT SQL with
> code,
> using that value for the key field just as you use values for any other
> field.
>
> * this may not be as easy as it seems, which
> may be a good argument, in some cases, for
> using a "natural key" rather than using AutoNumber
> as a surrogate key.
>
> Larry Linson
> Microsoft Access MVP
>
>[/color]

re: INSERT Query on two dependent joined tables ??

Hi John.....I do but I prefer Perl as it will be a internet based
database running on Unix and Win32.

It looks like the only solution is to create my own ID generation routines.
I cannot rely on a INSERT then a SELECT to get the last ID entered.
There may be a situation where after the INSERT to the AccountsTable,
another client may create a new Account which would trigger another
INSERT.

How do you "read" the ID of the newly inserted ID ??? What do you mean by
this ??
What happens if another INSERT is triggered before I've "read" the previous
ID ??

I was under the impression that INSERT querys don't return anything.

thanx for reply.


"John Welch" <jwelch@fred.com> wrote in message
news:da1t6401riv@enews4.newsguy.com...[color=blue]
> Do you have any experience with visual basic? If so, you could do at least
> a couple different things:
> 1) you could create your own routine to generate the ID value so that you
> don't have to use an autonumber field, and then you could insert into each
> table with your ID value.
> 2) you could use vb to insert the fields into the Accounts table, then
> read the ID of the newly inserted ID value, then insert that ID into the
> Members table.
>
> "Carl" <mail2carl@_remove_yahoo.com> wrote in message
> news:MtZwe.13254$11.3650@newsfe2-win.ntli.net...[color=green]
>> Hi,
>>
>> I hope someone can share some of their professional advice and help me
>> out
>> with my embarissing problem concerning an Access INSERT query. I have
>> never
>> attempted to create a table with one-to-one relationship but on this
>> occasion
>> I must keep username/password details within a seperate table.
>>
>> Here's the basic specs and database schema:
>> -------------------------------------------
>>
>> Database: Basic MS Access 2000 MDB
>> Table Relationships: Both tables are joined one-to-one on their ID
>> fields.
>>
>> TableMembers
>> ID: Long Integer; Required; Indexed(Unique); PrimaryKey;
>> Firstname: Text 50; Required;
>> Lastname: Text 50; Required;
>> Telephone: Text 50; Required;
>>
>> TableAccounts
>> ID: AutoNumber; Required; Indexed(Unique); PrimaryKey;
>> Username: Text 50; Required;
>> Password: Text 50; Required;
>>
>>
>> The problem:
>> ------------
>> In an ideal world I would like to use a single SQL INSERT on both tables
>> to insert
>> the required data. But I know this isn't possible as INSERT works on
>> single tables only, right ?
>>
>> I cannot use an SQL INSERT on TableMembers as the ID field is created by
>> the AutoNumber
>> in TableAccounts.ID.
>>
>> I could INSERT into TableAccounts first, which would auto-create the ID,
>> but how do I use the
>> ID in the second SQL INSERT on TableMembers ???
>>
>> When I open the TableAccounts Table in Access, I can manually input all
>> data for both Tables
>> without any problems. TableMembers Fields are displayed as a dropdown
>> record from TableAccounts
>> and the ID field is automatically taken care of.
>>
>> Can Anyone here can offer some advice or solutions, all replies greatly
>> appreciated.
>>
>> Carl.
>>
>>
>>
>>[/color]
>
>[/color]

re: INSERT Query on two dependent joined tables ??

"Larry Linson" <bouncer@localhost.not> wrote in
news:ZB_we.24799$Ff6.8496@trnddc09:
[color=blue]
> "Carl" wrote
>
> First, unless you have beaucoup data fields that you didn't list,
> why would you want to have these two tables with a one-to-one
> relationship? Obviously the simplest, easiest solution is to
> include all the information in a single table and you don't have
> to worry about matching keys.
>[color=green]
> > I could INSERT into TableAccounts first,
> > which would auto-create the ID, but
> > how do I use the ID in the second SQL
> > INSERT on TableMembers ???[/color]
>
> Retrieve the record you just entered*, and create the INSERT SQL
> with code, using that value for the key field just as you use
> values for any other field.
>
> * this may not be as easy as it seems, which
> may be a good argument, in some cases, for
> using a "natural key" rather than using AutoNumber
> as a surrogate key.[/color]

Basically, I agree with Larry's reservations about the data
structure. However, I've had applications where I needed to break
down single tables into 1:1 sub-tables because the application
needed what amounted to record subtypes.

There are two basic ways to do this.

1. have a field in main side of the 1:1 join that has only one
purpose, to hold a value that tells you "this record is being
inserted right now." You'd put something like -10000000 in it during
the insert, then use that value to identify the single record you've
just added so you can select its PK value to insert into the second
table. After you populate the second table, you set this special
field in the first table to Null. The problem with this approach is
that it doesn't work well in multi-user applications, unless you
make the user name part of the ID field you're using to find the
record that was just inserted.

2. write VBA code to insert the record in an AppendOnly recordset,
so you can grab the PK value, then use that to write INSERT SQL to
insert the record in the second table. You'd probably want to wrap
this in a transaction to insure that the whole process completes.
That is, if part of it fails you probably want the whole thing to
fail.

The 2nd choice is much more complex, but far more robust.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: INSERT Query on two dependent joined tables ??

Hi David.....

Your Solution 1 seems a good idea, it's a bit of a "hack" but you got
me thinking that this could work well. Maybe I could use the time
as another identifer.......I'll give it a try on the test server.

Your other solution I will look into.....not quite sure of a recordset,
will look into this though, if it's more preferred robust method.

thanx again..Carl.

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns9685CFE1DFE92dfentonbwaynetinvali@24.168.1 28.74...[color=blue]
> "Larry Linson" <bouncer@localhost.not> wrote in
> news:ZB_we.24799$Ff6.8496@trnddc09:
>[color=green]
>> "Carl" wrote
>>
>> First, unless you have beaucoup data fields that you didn't list,
>> why would you want to have these two tables with a one-to-one
>> relationship? Obviously the simplest, easiest solution is to
>> include all the information in a single table and you don't have
>> to worry about matching keys.
>>[color=darkred]
>> > I could INSERT into TableAccounts first,
>> > which would auto-create the ID, but
>> > how do I use the ID in the second SQL
>> > INSERT on TableMembers ???[/color]
>>
>> Retrieve the record you just entered*, and create the INSERT SQL
>> with code, using that value for the key field just as you use
>> values for any other field.
>>
>> * this may not be as easy as it seems, which
>> may be a good argument, in some cases, for
>> using a "natural key" rather than using AutoNumber
>> as a surrogate key.[/color]
>
> Basically, I agree with Larry's reservations about the data
> structure. However, I've had applications where I needed to break
> down single tables into 1:1 sub-tables because the application
> needed what amounted to record subtypes.
>
> There are two basic ways to do this.
>
> 1. have a field in main side of the 1:1 join that has only one
> purpose, to hold a value that tells you "this record is being
> inserted right now." You'd put something like -10000000 in it during
> the insert, then use that value to identify the single record you've
> just added so you can select its PK value to insert into the second
> table. After you populate the second table, you set this special
> field in the first table to Null. The problem with this approach is
> that it doesn't work well in multi-user applications, unless you
> make the user name part of the ID field you're using to find the
> record that was just inserted.
>
> 2. write VBA code to insert the record in an AppendOnly recordset,
> so you can grab the PK value, then use that to write INSERT SQL to
> insert the record in the second table. You'd probably want to wrap
> this in a transaction to insure that the whole process completes.
> That is, if part of it fails you probably want the whole thing to
> fail.
>
> The 2nd choice is much more complex, but far more robust.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

re: INSERT Query on two dependent joined tables ??

Hi Carl, what I mean by "reading" the previous ID is similar to David's idea
#2: use a recordset (which is an Active X data Object (ADO)thing - not sure
what you'd do with perl) to append your new record, then get the Id of that
new record (using a method of the recordset object), making sure you account
for multiusers, i.e. either using a transaction or a timestamp or something
to make sure you're getting the ID from the record you just added.

"Carl" <mail2carl@_remove_yahoo.com> wrote in message
news:j10xe.26305$BD2.14816@newsfe1-gui.ntli.net...[color=blue]
> Hi John.....I do but I prefer Perl as it will be a internet based
> database running on Unix and Win32.
>
> It looks like the only solution is to create my own ID generation
> routines.
> I cannot rely on a INSERT then a SELECT to get the last ID entered.
> There may be a situation where after the INSERT to the AccountsTable,
> another client may create a new Account which would trigger another
> INSERT.
>
> How do you "read" the ID of the newly inserted ID ??? What do you mean by
> this ??
> What happens if another INSERT is triggered before I've "read" the
> previous ID ??
>
> I was under the impression that INSERT querys don't return anything.
>
> thanx for reply.
>
>
> "John Welch" <jwelch@fred.com> wrote in message
> news:da1t6401riv@enews4.newsguy.com...[color=green]
>> Do you have any experience with visual basic? If so, you could do at
>> least a couple different things:
>> 1) you could create your own routine to generate the ID value so that you
>> don't have to use an autonumber field, and then you could insert into
>> each table with your ID value.
>> 2) you could use vb to insert the fields into the Accounts table, then
>> read the ID of the newly inserted ID value, then insert that ID into the
>> Members table.
>>
>> "Carl" <mail2carl@_remove_yahoo.com> wrote in message
>> news:MtZwe.13254$11.3650@newsfe2-win.ntli.net...[color=darkred]
>>> Hi,
>>>
>>> I hope someone can share some of their professional advice and help me
>>> out
>>> with my embarissing problem concerning an Access INSERT query. I have
>>> never
>>> attempted to create a table with one-to-one relationship but on this
>>> occasion
>>> I must keep username/password details within a seperate table.
>>>
>>> Here's the basic specs and database schema:
>>> -------------------------------------------
>>>
>>> Database: Basic MS Access 2000 MDB
>>> Table Relationships: Both tables are joined one-to-one on their ID
>>> fields.
>>>
>>> TableMembers
>>> ID: Long Integer; Required; Indexed(Unique); PrimaryKey;
>>> Firstname: Text 50; Required;
>>> Lastname: Text 50; Required;
>>> Telephone: Text 50; Required;
>>>
>>> TableAccounts
>>> ID: AutoNumber; Required; Indexed(Unique); PrimaryKey;
>>> Username: Text 50; Required;
>>> Password: Text 50; Required;
>>>
>>>
>>> The problem:
>>> ------------
>>> In an ideal world I would like to use a single SQL INSERT on both tables
>>> to insert
>>> the required data. But I know this isn't possible as INSERT works on
>>> single tables only, right ?
>>>
>>> I cannot use an SQL INSERT on TableMembers as the ID field is created by
>>> the AutoNumber
>>> in TableAccounts.ID.
>>>
>>> I could INSERT into TableAccounts first, which would auto-create the ID,
>>> but how do I use the
>>> ID in the second SQL INSERT on TableMembers ???
>>>
>>> When I open the TableAccounts Table in Access, I can manually input all
>>> data for both Tables
>>> without any problems. TableMembers Fields are displayed as a dropdown
>>> record from TableAccounts
>>> and the ID field is automatically taken care of.
>>>
>>> Can Anyone here can offer some advice or solutions, all replies greatly
>>> appreciated.
>>>
>>> Carl.
>>>
>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

re: INSERT Query on two dependent joined tables ??

Thanx all for feedback, I decided use your suggestions by creating a another
field to
identify a new account record....below is the SQL I am using to update both
tables.

I am using a combination of the Date and Time to create the DateTimeStamp
which is processed by the perl script.

TableAccounts Insert Query
--------------------------
INSERT INTO TableAccounts ( DateTimeStamp, Username, [Password] )
SELECT 123456 AS Expr1, 'JoeBloggs' AS Expr2, 'Dumbo' AS Expr2 FROM
TableAccounts
INNER JOIN TableMembers ON TableAccounts.ID = TableMembers.Account_ID;

TableMembers Insert Query
--------------------------
INSERT INTO TableMembers ( AccountID, Firstname, Lastname, Telephone)
SELECT DISTINCT TableAccounts.ID AS Expr1, 'Joe' AS Expr2, 'Bloggs' AS
Expr3, '012233' AS Expr4
FROM TableAccounts WHERE (((TableAccounts.DateTimeStamp)=123456));

Not sure if really need to us the INNER JOIN on INSERTS. Access adds them by
default.
I much prefer to use INSERT INTO tablename (field1,field2,field3) VALUES
(1,2,3)
but Access converts it all into SELECT AS Expr1 etc...no idea why ???

Thanx again for the ideas guys.

"Larry Linson" <bouncer@localhost.not> wrote in message
news:ZB_we.24799$Ff6.8496@trnddc09...[color=blue]
> "Carl" wrote
>
> First, unless you have beaucoup data fields that you didn't list, why
> would
> you want to have these two tables with a one-to-one relationship?
> Obviously
> the simplest, easiest solution is to include all the information in a
> single
> table and you don't have to worry about matching keys.
>[color=green]
> > I could INSERT into TableAccounts first,
> > which would auto-create the ID, but
> > how do I use the ID in the second SQL
> > INSERT on TableMembers ???[/color]
>
> Retrieve the record you just entered*, and create the INSERT SQL with
> code,
> using that value for the key field just as you use values for any other
> field.
>
> * this may not be as easy as it seems, which
> may be a good argument, in some cases, for
> using a "natural key" rather than using AutoNumber
> as a surrogate key.
>
> Larry Linson
> Microsoft Access MVP
>
>[/color]