How to add the same primary key to all tables

[Patricia]

Hi everyone,

I'm learning as I go with Access so please bear with me!

I have a database that has about 30 tables in it. I want to have a primary
key in one table; it will be auto-numbered. I want that value to push out to
all the other tables and be the primary key for all the other tables. Any
way to do this? Or am I making my database more complicated than it needs to
be?

Thanks!

 

[Douglas J. Steele]

It's not possible, nor does it really make sense. What exactly are you
trying to model?

 

[Patricia]

I've exported a SQL database to Access and want to use it to generate some
reports. I may not be explaining it right--- I'm a platforms admin so I'm
kind of out of my area of expertise here.

The ultimate goal is to build a tool that will generate reports based on the
contents of the exported database and some additional tables and/or
individual rows/records. I was looking into using data access pages but I
can't get the data from multiple tables into one data access page because
there are no relationships defined between the tables. I have a pretty large
number of tables and am not sure how to set up the relationships-- it will
be more like sections than anything. So I was hoping to add this primary key
to sort of tie it all together and use that as the basis for the
relationships.

I've got practically no experience with Access. I play with mail servers and
storage all day! So I am probably taking the entirely wrong approach here.
Any suggestions/advice would make you my hero!

 

[Douglas J. Steele]

I think you'll need to explain what kind of information is contained in the
exported databases.

 

[Patricia]

Application configuration information and related, so it's pretty much
static. When you install this particular application it creates a SQL
database that contains a lot of tables, for example one table contains
relevant info about all of the servers that have that application installed
(servername, software version, etc).

 

[John Vinson]

Hi everyone,

I'm learning as I go with Access so please bear with me!

I have a database that has about 30 tables in it. I want to have a primary
key in one table; it will be auto-numbered. I want that value to push out to
all the other tables and be the primary key for all the other tables. Any
way to do this? Or am I making my database more complicated than it needs to
be?

Thanks!

Well, you certainly appear to be on the wrong track, though it's hard
to tell for sure. One to one relationships are VERY rare (useful for
Subclassing and a few other esoteric things); and creating empty
placeholder records with nothing but a primary key value is
essentially *never* a good idea.

What are these tables, in terms of the real-life information that they
will contain?

John W. Vinson[MVP]

 

[Patricia]

Application configuration information and related, so it's pretty much
static. When you install this particular application it creates a SQL
database that contains a lot of tables, for example one table contains
relevant info about all of the servers that have that application installed
(servername, software version, etc).

Where I want to end up is a tool/template/report shell. I want to export out
the SQL database with all the config info in it as well as some additional
info imported from text or manual input. I want to then tag certain items
and pull them into a formal report that says basically, here's a list of
your servers, and for each one here's a list of the OS and patch levels,
etc. I want to be able to recycle the tool/whatever the right name for it
is, so that I can take a new database, pull it in and get a new report back
out.

I don't really know if this is the right tool for the job. I can capture the
information easily but need to get all the captured info back out as
automatically as possible.

Thanks for whatever input you can provide!

 

[John Vinson]

Application configuration information and related, so it's pretty much
static. When you install this particular application it creates a SQL
database that contains a lot of tables, for example one table contains
relevant info about all of the servers that have that application installed
(servername, software version, etc).

Sounds like a many to many relationship: a table of Servers, a table
of Applications, and a table of InstalledApplications with fields for
ServerID and SoftwareID. I fear you're "committing spreadsheet" and
having lots of fields in each table!

Where I want to end up is a tool/template/report shell. I want to export out
the SQL database with all the config info in it as well as some additional
info imported from text or manual input. I want to then tag certain items
and pull them into a formal report that says basically, here's a list of
your servers, and for each one here's a list of the OS and patch levels,
etc. I want to be able to recycle the tool/whatever the right name for it
is, so that I can take a new database, pull it in and get a new report back
out.

Starting with properly normalized tables (in both SQL and Access) is
your best bet. It's easy to do crosstab queries and reports to format
the data the way you like.


John W. Vinson[MVP]

 

[Larry Linson]

Jamie / aaron,

In properly normalized relational databases, one-to-one relationships are,
if not "rare," certainly "not common."

From the titles/subjects/names of the Tables you cite, it's clear those are
unlikely to be properly normalized relational databases. They seem more like
"legacy COBOL files" that have been handed down over the years.

Larry Linson