table join

  • Thread starter Thread starter Kara
  • Start date Start date
K

Kara

words in caps are the column headings

The equipment table is holding the fields
--EQUIPMENTNAME
--TIME that the equipment was checked
--PROBLEM with the equipment, if there was one
--who was CALLED FOR REPAIR
--person that was NOTIFIED of the problem
--DATE, SHIFT, COMPUTERNUMBER(this is a unique number
given to each employee)
The primary keys are date, shift, computer number, and
equipment.

The workstation table is holding the fields
--POSITION where the workstation is located(location 1,
location 2) etc
--who REPORTED the problem
--PROBLEM with the wrkstation
--who the problem was REPORTED TO
--who was NOTIFIED of the problem
--DATE, SHIFT, COMPUTER NUMBER
Primary Keys date, shift,computer number, and position.

I have that many primary keys because there are three
shifts and they will each enter data into the tables.
Here is the SQL statement for the query....

SELECT equipment.first_check, equipment.second_check,
equipment.problem, equipment.call_rpr, equipment.notified,
tbl_workstation.position, tbl_workstation.rptd_by,
tbl_workstation.time, tbl_workstation.problem,
tbl_workstation.rptd_to, tbl_workstation.notified,
tbl_workstation.date, tbl_workstation.computernumber,
tbl_workstation.shift
FROM equipment LEFT JOIN tbl_workstation ON
(equipment.shift = tbl_workstation.shift) AND
(equipment.date = tbl_workstation.date) AND
(equipment.computernumber =
tbl_workstation.computernumber);

Kara
 
Does anyone have any suggestions???
-----Original Message-----
words in caps are the column headings

The equipment table is holding the fields
--EQUIPMENTNAME
--TIME that the equipment was checked
--PROBLEM with the equipment, if there was one
--who was CALLED FOR REPAIR
--person that was NOTIFIED of the problem
--DATE, SHIFT, COMPUTERNUMBER(this is a unique number
given to each employee)
The primary keys are date, shift, computer number, and
equipment.

The workstation table is holding the fields
--POSITION where the workstation is located(location 1,
location 2) etc
--who REPORTED the problem
--PROBLEM with the wrkstation
--who the problem was REPORTED TO
--who was NOTIFIED of the problem
--DATE, SHIFT, COMPUTER NUMBER
Primary Keys date, shift,computer number, and position.

I have that many primary keys because there are three
shifts and they will each enter data into the tables.
Here is the SQL statement for the query....

SELECT equipment.first_check, equipment.second_check,
equipment.problem, equipment.call_rpr, equipment.notified,
tbl_workstation.position, tbl_workstation.rptd_by,
tbl_workstation.time, tbl_workstation.problem,
tbl_workstation.rptd_to, tbl_workstation.notified,
tbl_workstation.date, tbl_workstation.computernumber,
tbl_workstation.shift
FROM equipment LEFT JOIN tbl_workstation ON
(equipment.shift = tbl_workstation.shift) AND
(equipment.date = tbl_workstation.date) AND
(equipment.computernumber =
tbl_workstation.computernumber);

Kara
.
Click to expand...
 
Because that's the way relational queries are designed to work, if I
understand the problem!

If you join two tables in a one-to-many relationship in a Query, and
there are eleven rows in the "many" side which match a given row in
the "one" side table, you will see eleven composite records: the
values in the "one" side table will be repeated eleven times, each one
joined to a different set of values from the "many" side table.
 
Because that's the way relational queries are designed to
work, if I understand the problem!
If you join two tables in a one-to-many relationship in a
Query, and...

I have posted a similar problem. How do we make it a 1-to-
1 relationship? Thanks, -Bill.
 
I have posted a similar problem. How do we make it a 1-to-
1 relationship? Thanks, -Bill.
Click to expand...

One to one relationships are QUITE rare: if each record in TableA
corresponds to one and only one record in TableB, you might as well
put all the fields in one table.

If you want a one to one relationship anyway, open the "many" side
table in design view and select the joining field. On the Index line
in the field properties, specify a unique index.
 
John, You're right, 1-to-1 isn't what I really wanted. I
took out the join/relationship & my append query works
fine now. Thnaks for the help. You folks in here are SO
MUCH better at responding than some of the other places
I've posted. Keep it up. Maybe someday I'LL be able to
return the favors. EDX...COBOL...TCAL, anybody? ; )
 
John, You're right, 1-to-1 isn't what I really wanted. I
took out the join/relationship & my append query works
fine now.
Click to expand...

It should NOT be necessary to remove a relationship to get an append
query to work!!!! Care to post some more details? I think there is a
less drastic solution!
Thnaks for the help. You folks in here are SO
MUCH better at responding than some of the other places
I've posted. Keep it up.
Click to expand...

Thank you very much!
 
Back
Top