Add a table

[Matt]

What is the best way to add a new table to my existing database? I want to
add several fields, and thought it would be easiest if I could just create a
new table and somehow link the information together. I would like the info
in the new table to coincide with the existing info on the other table via a
Job#. I have tried everything I know (which obviously isn't much) and cannot
seem to get these two tables linked. I want to create a query that includes
fields from both tables, so I created a query for each table seperately, then
tried to create a new query with fields from both of the queries I just made.
Doesn't work. I get an error message saying "You have chosen fields from
record sources which the wizard can't connect. You may have chosen fields
from a table and from a query based on that table. If so, try choosing
fields from only the table or only the query." I didn't. Thanks in advance,
Matt.

 

[Golfinray]

Your tables have to be related or you will get that error. Read Access help
about primary and foreign keys. Go to tools/relationships and set a
relationship between the two tables on job#. Then when you query simply add
both tables to the query, drag job# from one to the other. Then bring in the
fields you want.

 

[Matt]

No unique index found for the referenced field of the primary table. The
only relation I seem to be able to make is between the autonumber fields in
each. I do not have the Job# set as a primary key because I imported a
couple thousand entries in an excel spreadsheet, where about 1/4 of the Job#
was blank.

 

[John W. Vinson]

No unique index found for the referenced field of the primary table. The
only relation I seem to be able to make is between the autonumber fields in
each. I do not have the Job# set as a primary key because I imported a
couple thousand entries in an excel spreadsheet, where about 1/4 of the Job#
was blank.

Well, you certainly CANNOT relate an autonumber to another autonumber.
Autonumber values are meaningless and arbitrary; even if you could relate the
tables that way, you would be connecting a record in the primary table to an
unpredictable, almost certainly unrelated record in the other.

What is the LOGICAL relationship between the tables? Does the spreadsheet have
only one row for any given Job#, or more than one? What (if anything) do you
want to do with spreadsheet rows which have no Job#? Do they "belong" to some
job, and if so how can you determine which?

 

[Matt]

The spreadsheet had only one row for each Job#, and about 30 columns. No
biggie. I want all of the information in the second table (I'm really just
adding a bunch more columns) to be related to the first via the Job#. The
entries with blank Job#'s could just be filled in with a random number now,
because they are reference only at this point. I normally do all searches
based on an address anyways, but the Job# is the logical (to me) reference,
because we never repeat them, but sometimes have add ons to it. Should I
just add all the info I want to the first table? Is there a point where the
size of a single table will effect the speed of the database? That's all I'm
trying to avoid, so if I just need to add everything in one huge table, I
guess I could. Thanks for the help!

 

[John W. Vinson]

The spreadsheet had only one row for each Job#, and about 30 columns. No
biggie. I want all of the information in the second table (I'm really just
adding a bunch more columns) to be related to the first via the Job#. The
entries with blank Job#'s could just be filled in with a random number now,
because they are reference only at this point. I normally do all searches
based on an address anyways, but the Job# is the logical (to me) reference,
because we never repeat them, but sometimes have add ons to it. Should I
just add all the info I want to the first table? Is there a point where the
size of a single table will effect the speed of the database? That's all I'm
trying to avoid, so if I just need to add everything in one huge table, I
guess I could. Thanks for the help!

If you're just "adding everything into one huge table" you're misusing Access.

The information in your spreadsheet has (I hope!!) a *logical, meaningful*
order. In a spreadsheet it's routine to just add 30 more columns. That's
almost certainly WRONG in a relational database.

Read some of the tutorials in

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

if you want to make productive use of Access. The first step is to identify
the "Entities" - real-life people, things, events, Jobs - of importance to
your application. You'll find that each Job might have (say) zero, one, two or
more People working on it - tadaaah, a many to many relationship, with tables
for Jobs (containing nothing about people), People (containing nothing about
jobs), and JobAssignments, related one to many to each of the above.

You can certainly move data from your spreadsheets *into* a normalized set of
tables... but you do need to set up your normalized set of tables first, and
what makes a good spreadsheet will in general NOT make a good table.

 

[Matt]

Alrighty, I've read through several of the tutorials/info that you provided
links for. Here's a little better background for what I'm trying to
accomplish because it obviously makes all the difference in the world in the
advice you give. What the database is intended to contain is information
relating to a particular "Job #". The info that I'm tracking really is kind
of hard (for me at least) to determine what should go in what table, and the
best way to link them. About 40% of the columns contain dates under various
headings, all manually filled out, all the time. I am trying to track
several stages of a production job, from approvals to production to install,
so if I break down the tables into just the ones that are directly related I
get several tables with just a couple of fields in them. This probably isn't
an issue, assuming you're not confused when trying to set the relationships.
The only information on any given job that could even possibly be used to
link these tables is the Job #, because nothing else is unique. So if I'm
not mistaken, I need to break my table down into smaller tables. What is the
best way to then link all of these tables together?

 

[Matt]

Alrighty, I've read through several of the tutorials/info that you provided

 

[John W. Vinson]

Alrighty, I've read through several of the tutorials/info that you provided
links for. Here's a little better background for what I'm trying to
accomplish because it obviously makes all the difference in the world in the
advice you give. What the database is intended to contain is information
relating to a particular "Job #". The info that I'm tracking really is kind
of hard (for me at least) to determine what should go in what table, and the
best way to link them. About 40% of the columns contain dates under various
headings, all manually filled out, all the time. I am trying to track
several stages of a production job, from approvals to production to install,
so if I break down the tables into just the ones that are directly related I
get several tables with just a couple of fields in them. This probably isn't
an issue, assuming you're not confused when trying to set the relationships.
The only information on any given job that could even possibly be used to
link these tables is the Job #, because nothing else is unique. So if I'm
not mistaken, I need to break my table down into smaller tables. What is the
best way to then link all of these tables together?

One way to handle this is a JobStages table. You would have a table of Stages
with values like "Proposal", "Design Initiation", "Customer Approval", etc.
Your JobStages table would have three (maybe a few more) fields - Stage, JobNo
(don't use # in fieldnames, it's a date delimiter), and StageDate. Rather than
40 fields for forty stages, you could add new records to the JobStages table
as the various milestones are reached.

The JobNo would of course be the link to the Jobs table which would have
non-repeating fields pertaining to the job.

Not knowing the nature of the job or the other fields in your table it's hard
to be specific, but it should be possible to do at least some normalization.

 

[Matt]

Is there a better way to link both tables by JobNo than to Paste Append to
the new table? So far I've only got about 1700 unique JobNo, so I did a
Paste Append to my new table (with no other information enetered yet) of all
the JobNo. It works fine now (thanks to you) I'm just curious if there's a
better way to do it.

 

[John W. Vinson]

Is there a better way to link both tables by JobNo than to Paste Append to
the new table? So far I've only got about 1700 unique JobNo, so I did a
Paste Append to my new table (with no other information enetered yet) of all
the JobNo. It works fine now (thanks to you) I'm just curious if there's a
better way to do it.

Yes. An Append Query.

Copy and paste is routine for spreadsheets but it's rarely the best approach
in a database.

 

[Matt]

Thanks, I appreciate all the help!

Yes. An Append Query.

Copy and paste is routine for spreadsheets but it's rarely the best approach
in a database.