Howdy.

[Andy]

I have a database with 3 tables

One table just has an ID

the other table has people's information which references the ID table and
the other one has other information and also references the id table.

How can I create a form in access 2007 which allows me to make one page
where my users can fill out a form with all of the information and it will
create an autonumber ID and automatically enter the new auto-number ID into
the two tables which reference that autonumber ID?

hope this makes sense.

thanks,
-Drew

 

[Keith Wilby]

I have a database with 3 tables

One table just has an ID

the other table has people's information which references the ID table and
the other one has other information and also references the id table.

How can I create a form in access 2007 which allows me to make one page
where my users can fill out a form with all of the information and it will
create an autonumber ID and automatically enter the new auto-number ID
into the two tables which reference that autonumber ID?

hope this makes sense.

Makes no sense at all to me Andy. "One table just has an ID"? What purpose
would that serve? What are the relationships between your tables?

Keith.
www.keithwilby.com

 

[Andy]

Makes no sense at all to me Andy. "One table just has an ID"? What
purpose would that serve? What are the relationships between your tables?

Keith.
www.keithwilby.com

Yes, the uniqueID is stored in its own table, and then the other tables
reference that ID

the design is fairly common in normalized SQL databases.

i.e.

tableID -- ID - AutoNumber

tableInfo -- ID - Number (tied to ID.ID) Name - Text | Email - Text

tableOther -- ID - Number (tied to ID.ID) Other - Text | Other2 - Text

that way you can build queries and applications where you simply select
information for the ID in "tableID" and then seek information in the other
tables based on that ID...

Andy

 

[Steve Schapel]

Andy,

the design is fairly common in normalized SQL databases.

Sorry to be contradictory. But I don't think this is correct. I don't
think it is common at all. It is so rare that I have never come upon
the idea before.

 

[John W. Vinson]

that way you can build queries and applications where you simply select
information for the ID in "tableID" and then seek information in the other
tables based on that ID...

I don't see that.

If TableID just contains a series of meaningless numeric ID's, there's *no
information to select* - you need some external source of information (another
table using the same ID as a primary key for example) to determine that you're
interested in ID's 3127, 3395 and 4126. And if you have to look in another
table anyway, you can just join *that* table directly - you don't get any
benefit from having a one-field table with no data in it!

John W. Vinson [MVP]

 

[Keith Wilby]

Yes, the uniqueID is stored in its own table, and then the other tables
reference that ID

the design is fairly common in normalized SQL databases.

Is it?

i.e.

tableID -- ID - AutoNumber

tableInfo -- ID - Number (tied to ID.ID) Name - Text | Email - Text

tableOther -- ID - Number (tied to ID.ID) Other - Text | Other2 - Text

that way you can build queries and applications where you simply select
information for the ID in "tableID" and then seek information in the other
tables based on that ID...

Still makes no sense at all, sorry. Even in a M:M you'd have two IDs (not
autonumbers) in the join table.