Combining data from two tables.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I feel that I am so close and yet so far .. ... ... .. .... ... ... ..
I have two tables one contains information about jobs done, where, when what
etc. Primary Key is job number as this has to be unique as it is generated by
our clent.

The second table contains a list of codes, their descriptions and price.
This is a fixed list, It is always the same but because it is three columns
wide and each job can have up to thirteen codes I want to be able to refer to
the second table by a link or whatever. I have read the messages about look
up not being the best way and want to learn a new way.

The information has to be sent back to our client in a form that they have
also generated. I have recreated in Word and have worked with mail merges
before and find them great. There is a tight deadline on this. I did post
another question in another strain but could not find it. I just need some
step by step guidence.

Thanks in anticipation.
 
okay, you have a tblJobs and a tblCodes. one job may have many codes
assigned to it, and presumably each code may be assigned to many jobs. so
you have a many-to-many relationship between jobs and codes. in Access, this
is resolved with a third table - called a "join" or "linking" table. see the
example below, as

tblJobs
JobNumber (primary key)
(other fields that describe a job)

tblCodes
Code (primary key)
Description
Price

tblJobCodes
JobNumber (foreign key from tblJobs)
Code (foreign key form tblCodes)
the two above fields can be used together as a combination primary key for
the table, or you can add a third field with Autonumber data type, to serve
as the table's primary key.

each record in tblJobs lists one code assigned to one job, so if a job has
13 codes, there will be thirteen records in the table for that job.

the relationships are
tblJobs.JobNumber 1:n tblJobCodes.JobNumber
tblCodes.Code 1:n tblJobCodes.Code

hth
 
Great thanks. How do I now get the information from the third table into the
word document. Do I use a query as in the past? and other than creating a
link between the two tables because many to many relationships are not cool
according to my Access 2002 for Dummies book, what is the role of that third
table in queries etc?

Cheers
 
Hello V6Lady,
How do I now get the information from the third table into the
word document. Do I use a query as in the past?
Click to expand...

Yes. A query can be used to present a denormalized view of the data. Out of
curiousity, do you really have a need to export the results to Word? Would a
report in Access not serve just as well?
...and other than creating a link between the two tables because many to many
relationships are not cool according to my Access 2002 for Dummies book...
Click to expand...

What? I don't have this book, so I cannot read what context they might have
wrote that in. However, it is very common to find many-to-many (M:N)
relationships in a properly normalized database.

Tina wrote:
* each record in tblJobs lists one code assigned to one job, so if a job has
* 13 codes, there will be thirteen records in the table for that job.

I'm not sure, but I think she meant to say that there would be 13 records in
the tblJobCodes table (ie. the join or linking table that includes the two
foreign keys).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Great thanks. How do I now get the information from the third table into the
word document. Do I use a query as in the past? and other than creating a
link between the two tables because many to many relationships are not cool
according to my Access 2002 for Dummies book, what is the role of that third
table in queries etc?
__________________________________________

:

okay, you have a tblJobs and a tblCodes. one job may have many codes
assigned to it, and presumably each code may be assigned to many jobs. so
you have a many-to-many relationship between jobs and codes. in Access, this
is resolved with a third table - called a "join" or "linking" table. see the
example below, as

tblJobs
JobNumber (primary key)
(other fields that describe a job)

tblCodes
Code (primary key)
Description
Price

tblJobCodes
JobNumber (foreign key from tblJobs)
Code (foreign key form tblCodes)
the two above fields can be used together as a combination primary key for
the table, or you can add a third field with Autonumber data type, to serve
as the table's primary key.

each record in tblJobs lists one code assigned to one job, so if a job has
13 codes, there will be thirteen records in the table for that job.

the relationships are
tblJobs.JobNumber 1:n tblJobCodes.JobNumber
tblCodes.Code 1:n tblJobCodes.Code

hth
__________________________________________

:

I feel that I am so close and yet so far .. ... ... .. .... ... ... ..
I have two tables one contains information about jobs done, where, when what
etc. Primary Key is job number as this has to be unique as it is generated by
our clent.

The second table contains a list of codes, their descriptions and price.
This is a fixed list, It is always the same but because it is three columns
wide and each job can have up to thirteen codes I want to be able to refer to
the second table by a link or whatever. I have read the messages about look
up not being the best way and want to learn a new way.

The information has to be sent back to our client in a form that they have
also generated. I have recreated in Word and have worked with mail merges
before and find them great. There is a tight deadline on this. I did post
another question in another strain but could not find it. I just need some
step by step guidence.

Thanks in anticipation.
 
umm, if you mean that it's common to find many-to-many relationships *that
are expressed as two one-to-many relationships with a linking table*, then
i'd agree. in the real world, many entities have a many-to-many
relationship, of course. but AFAIK, the only way to properly support such a
relationship in Access is with the linking table solution.

hth
 
the third table holds the data of "which specific codes are assigned to
which specific tables". whenever you need to see or edit that data, you'll
do it in the third table, so that is its' "role", to store that data.

to match up job codes with the rest of the job data (that's stored in
tblJobs), you put both tables into a query. if you want to include data
about the codes, that's stored in tblCodes, you include that table in the
query also. for instance, if you want a query to return the job number,
code, price, and job name (i made that one up) fields, your query SQL
statement would be

SELECT tblJobCodes.JobNumber, tblJobCodes.Code, tblJobs.JobName,
tblCodes.Price
FROM (tblJobCodes LEFT JOIN tblJobs ON tblJobCodes.JobNumber =
tblJobs.JobNumber) LEFT JOIN tblCodes ON tblJobCodes.Code = tblCodes.Code;

exactly how you write the query depends on how you need the data presented,
and what data fields need to be included. for instance, you may want to use
a cross-tab query to present your data a certain way. AFAIK, you can use a
query to mail merge the data into a Word doc. or, as Tom suggests, you might
be able to write a report in Access to use the data directly.

hth
 
Hi Tina,
umm, if you mean that it's common to find many-to-many relationships *that
are expressed as two one-to-many relationships with a linking table*, then
i'd agree.
Click to expand...

It is common to find many-to-many relationships in relational databases, as
I'm sure you are well aware. The Northwind sample database includes several
M:N relationships. I did not talk about *how* one implements a M:N
relationship, since you had already covered that quite well with your
discussion of join (aka linking, junction, or intersection) tables. So, of
course the implemention requires the join table, using two 1:M relationships,
with the foreign key (many side) of both relationships at the join or linking
table.

I'm sure we are in agreement on this point. Sometimes the written word does
not convey our intentions correctly to another person.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

umm, if you mean that it's common to find many-to-many relationships *that
are expressed as two one-to-many relationships with a linking table*, then
i'd agree. in the real world, many entities have a many-to-many
relationship, of course. but AFAIK, the only way to properly support such a
relationship in Access is with the linking table solution.

hth
 
Hi Tina,

Thank you for your time on this. I am still slightly confused, like I am
missing a piece of the puzzle.

A report does not really work for my client because of the nature of the
document. I have not been able to produce a replica of their page in Access.
It is a fussy little blighter, the form that is not the client, I also found
the mail merge option solved other problems I was having in terms of the
client reading the pages at their end. They do not run Access and I have no
idea on how I can get around that in terms of sending the data to them.

In terms of my immediate problem of understanding this whole linking
business, I have managed a work around for now that is working even though a
little message comes up with the mail merge saying that there are locked
records - all the info is there.

I have never used SQL to create my queries. and what does AFAIK mean?
 
I have never used SQL to create my queries. and what does AFAIK mean?

all Access queries are SQL statements. when you build a query in query
design view, the system writes the actual SQL; to see it, click View | SQL
on the menu bar. AFAIK = as far as i know.

hth
 
Attention: This (e-mail address removed) email address is on the BLACK LIST with
thousands of others. Do not believe the sob story. More information
regarding this can be found at
http://www.joewein.de/sw/blacklist/from-bl.txt. The report for
http://search.netzero.net/search?action=search&source=startpage&[email protected]
read as followed.

From: (e-mail address removed)

Web Results
--------------------------------------------------------------------------------

1.. "ojo5."@k.ro;419 "(e-mail address removed)"@jumpy.it;419 "walker ... ...
walla.com;419 "charles ... .com;419 (e-mail address removed);419
(e-mail address removed);419 (e-mail address removed);419 (e-mail address removed);419
(e-mail address removed);419 (e-mail address removed) ...
www.joewein.de


2.. "419" sender addresses (blacklisted by jwSpamSpy) "419" sender
addresses (blacklisted by jwSpamSpy) If a deal sounds too good to be true,
it probably is.
www.joewein.de
----- Original Message -----
From: "abah usman" <[email protected]>
To: <undisclosed-recipients:>
Sent: Wednesday, January 18, 2006 3:49 AM
Subject: INVESTMENT PROJECT
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining two tables 3
Comparing Tables 3
Two tables with identical data 5
VB and set of Data bases 2
Can a table be created from a template table 1
Update Two Tables Using One Form Contents 0
COMBINE TABLES IN ACCESS 3
Lookup data and create a Report 3

Back
Top