473,385 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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.


Nov 13 '05 #1
8 6262
"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.
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 ???


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
Nov 13 '05 #2
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:Mt*****************@newsfe2-win.ntli.net...
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.

Nov 13 '05 #3
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" <bo*****@localhost.not> wrote in message
news:ZB_we.24799$Ff6.8496@trnddc09...
"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.
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 ???


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

Nov 13 '05 #4
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" <jw****@fred.com> wrote in message
news:da*********@enews4.newsguy.com...
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:Mt*****************@newsfe2-win.ntli.net...
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.


Nov 13 '05 #5
"Larry Linson" <bo*****@localhost.not> wrote in
news:ZB_we.24799$Ff6.8496@trnddc09:
"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.
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 ???


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.


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
Nov 13 '05 #6
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" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...
"Larry Linson" <bo*****@localhost.not> wrote in
news:ZB_we.24799$Ff6.8496@trnddc09:
"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.
> 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 ???


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.


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

Nov 13 '05 #7
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:j1*******************@newsfe1-gui.ntli.net...
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" <jw****@fred.com> wrote in message
news:da*********@enews4.newsguy.com...
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:Mt*****************@newsfe2-win.ntli.net...
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.



Nov 13 '05 #8
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" <bo*****@localhost.not> wrote in message
news:ZB_we.24799$Ff6.8496@trnddc09...
"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.
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 ???


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

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
2
by: sqlgoogle | last post by:
Hi I'm having update problem. Here is the senario I have to different db server (SQL Server) linked with each other In DB Server 1 I have 2 tables & In DB Server 2 I have 3 tables. I have...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
2
by: allyn44 | last post by:
Hello--I have 2 tables (illness,event) that a need to query and create a recordset The key fields are personId and description (text field) in each table. I also have other needed fields in the...
1
by: Michael | last post by:
I have a query that uses two joined tables. The query contains data. I would like to pull in a data drom another table but when I add that table to the query design and make the join,the existing...
2
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for...
21
by: Killer42 | last post by:
Hi all. Iím almost embarrassed to ask this one, but in fact most of my dabbling in Access has been at a fairly simple level, via the GUI. Now I need to do something slightly deeper, and donít know...
1
by: racquetballer | last post by:
I need to to an update query that involves three tables: table Dealer needs to be updated with data from table Personnel and table Title. Dealer is joined to Personnel where Dealer.Dealer_Code =...
3
dlite922
by: dlite922 | last post by:
I'm building a dynamic reporting system. The report can of course query multiple tables. The query declares which tables it will access, the fields that it needs for display, and the dependent...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.